MySQL error 1304 (ER_SP_ALREADY_EXISTS) occurs when you try to create a stored procedure, function, trigger, or event with a name that already exists. The fix is to drop the existing routine first or use CREATE OR REPLACE on MySQL 8.0.13+.
MySQL Error 1304 is raised when you attempt to create a stored routine (procedure, function, trigger, or event) using a name that already exists in the same database schema. Unlike some programming languages, MySQL does not support function overloading by parameter list—each routine name must be unique within its schema. When MySQL encounters a CREATE PROCEDURE or CREATE FUNCTION statement with a duplicate name, it immediately halts execution and returns error 1304. This is a protective measure to ensure that every reference to a routine name unambiguously points to a single definition. If the same name could refer to multiple routines, callers would have unpredictable behavior. This error is particularly common in CI/CD pipelines and automated deployments, where the same migration script may run multiple times and needs to be idempotent (safe to run repeatedly without side effects).
Before making any changes, confirm that the routine exists and which schema it belongs to. Run:
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'your_procedure_name';This shows all routines matching the name across all schemas. Verify you're looking at the correct database.
The most straightforward fix is to drop the old routine before creating a new one. Use DROP PROCEDURE IF EXISTS (for procedures) or DROP FUNCTION IF EXISTS (for functions):
DROP PROCEDURE IF EXISTS your_procedure_name;
DELIMITER //
CREATE PROCEDURE your_procedure_name()
BEGIN
-- Your procedure body here
END //
DELIMITER ;The IF EXISTS clause ensures the statement succeeds even if the procedure doesn't exist, making the script safe to run repeatedly (idempotent).
For functions, use:
DROP FUNCTION IF EXISTS your_function_name;
DELIMITER //
CREATE FUNCTION your_function_name(param INT) RETURNS INT DETERMINISTIC READS SQL DATA
BEGIN
-- Function body
RETURN 0;
END //
DELIMITER ;If you're running MySQL 8.0.13 or later, you can use CREATE OR REPLACE PROCEDURE to replace the routine in a single statement without explicitly dropping it first:
CREATE OR REPLACE PROCEDURE your_procedure_name()
BEGIN
-- Your procedure body here
END //This approach is cleaner and automatically handles the drop-and-recreate logic. Note: CREATE OR REPLACE PROCEDURE requires that parameters and return types of the new procedure are compatible with the existing one. If you're changing the signature significantly, you still need to drop and recreate.
Check your MySQL version:
SELECT VERSION();To prevent this error in the future, always use DROP IF EXISTS before CREATE in deployment scripts. This makes scripts idempotent—they can be run multiple times without failure.
Example migration file:
-- migration_001_create_user_procedures.sql
DROP PROCEDURE IF EXISTS get_user_by_id;
DROP PROCEDURE IF EXISTS create_user;
DROP PROCEDURE IF EXISTS delete_user;
DELIMITER //
CREATE PROCEDURE get_user_by_id(IN user_id INT)
BEGIN
SELECT * FROM users WHERE id = user_id;
END //
CREATE PROCEDURE create_user(IN user_name VARCHAR(255), IN user_email VARCHAR(255))
BEGIN
INSERT INTO users (name, email) VALUES (user_name, user_email);
END //
CREATE PROCEDURE delete_user(IN user_id INT)
BEGIN
DELETE FROM users WHERE id = user_id;
END //
DELIMITER ;Then run it safely in your CI/CD pipeline:
mysql -u root -p database_name < migration_001_create_user_procedures.sqlPrivileges and routine recreation: When you drop a stored routine, any privileges granted specifically on that routine are also dropped. After recreating the routine, you must re-grant those privileges:
GRANT EXECUTE ON PROCEDURE database_name.procedure_name TO 'user'@'host';Case sensitivity: MySQL routines are case-insensitive by default (on case-insensitive filesystems like Windows and macOS). Ensure your CREATE statement uses the exact same case as any existing routine to avoid confusion.
Testing idempotent scripts: To verify your migration script is truly idempotent, run it twice in a test environment and confirm no errors occur on the second run:
mysql -u root -p test_db < migration.sql
mysql -u root -p test_db < migration.sql # Should succeed with no errorsTemporary routines during development: If you're iterating on a procedure during development, you might also use naming conventions to version routines (e.g., get_user_v1, get_user_v2) during the initial development cycle, then settle on a final name once the procedure is stable.
ERROR 1064: You have an error in your SQL syntax
How to fix "ERROR 1064: You have an error in your SQL syntax" in MySQL
ERROR 1054: Unknown column in field list
Unknown column in field list
ER_WINDOW_RANGE_FRAME_NUMERIC_TYPE (3589): RANGE frame requires numeric ORDER BY expression
RANGE frame requires numeric ORDER BY expression in MySQL window functions
CR_ALREADY_CONNECTED (2058): Handle already connected
How to fix "CR_ALREADY_CONNECTED (2058): Handle already connected" in MySQL
ER_WINDOW_DUPLICATE_NAME (3591): Duplicate window name
How to fix ER_WINDOW_DUPLICATE_NAME (3591) in MySQL window functions