A script with DELIMITER fails with error 1064 near DELIMITER
ERROR 1064 (42000)Short answer
DELIMITER is a command of the mysql client, not an SQL statement, so the server cannot parse it. It works in the mysql program and fails when a driver or tool sends the text straight to the server. Remove the DELIMITER lines and send each procedure as one statement, or end the body with the new delimiter and reset it to a semicolon.
Fix it fast
1Find out which cause you have
Find out who reads the file. Run the same script with the mysql program. If it works there and fails in your code or tool, cause 1 applies.
mysql -u root -p mydb < procedures.sql2Match what you see, then jump to its fix
- 1
The error names the text 'DELIMITER' (or the line after it) and the script runs fine with the mysql commandThe script goes through a driver, ORM or tool, not the mysql clientFix - 2
ERROR 1064 near the lines that follow the procedure, or the client keeps waiting at a -> promptThe definition does not end with the new delimiterFix - 3
The error points into the middle of a word or an escaped string, not at DELIMITER itselfThe chosen delimiter clashes with the SQL textFix
- 1
What does the error look like?
ERROR 1064 (42000)%s near '%s' at line %d... near 'DELIMITER //' at line 1
Cause 1 of 3
The script goes through a driver, ORM or tool, not the mysql client
1Check
The manual lists delimiter among the mysql client's own commands, the ones that change how that program splits input. A driver, a migration runner or a database tool that sends the file text to the server hands DELIMITER to the SQL parser, which does not know it.
# read by the mysql client, the file loads; the same text sent by your code fails
mysql -u root -p mydb < procedures.sql2Fix
Delete every DELIMITER line from the text you send to the server, and send each CREATE PROCEDURE, FUNCTION or TRIGGER as one statement with its inner semicolons left in. Execute the statements one call each, because the delimiter only existed to make the client pass the whole body on as a single statement.
-- sent as one statement, no DELIMITER lines
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END3Verify
Run the statement through your driver, then call the procedure.
CALL dorepeat(1000);Confirmed by dev.mysql.com
Cause 2 of 3
The definition does not end with the new delimiter
1Check
After DELIMITER //, the client only treats // as the end of a statement, and a semicolon inside the body is passed on to the server untouched. A body that ends with END; has no terminator the client recognises, so the statement stays unfinished and what follows is read as part of it.
DELIMITER //
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
END;
DELIMITER ;2Fix
End the definition with the new delimiter on its own line, then restore the semicolon before you run anything else.
DELIMITER //
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END
//
DELIMITER ;3Verify
The statement is accepted and the procedure runs.
CALL dorepeat(1000);Confirmed by dev.mysql.com
Cause 3 of 3
The chosen delimiter clashes with the SQL text
1Check
The client treats the delimiter string as a terminator anywhere it occurs outside quoted strings. A letter such as X cuts INDEX into INDE and X. A backslash is the escape character in MySQL. Search the body for the string you picked.
# every hit should be a delimiter line, never part of a body
grep -n -F '$$' procedures.sql2Fix
Pick a short string that cannot occur in the body, such as // or $$, and do not use a backslash.
DELIMITER $$
CREATE TRIGGER trg_orders_bi BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
SET NEW.created_at = NOW();
END$$
DELIMITER ;3Verify
Load the file again; the client no longer stops in the middle of a statement.
mysql -u root -p mydb < procedures.sqlConfirmed by dev.mysql.com
How do I stop it coming back?
- Keep stored programs in files that are loaded with the mysql client, and keep DELIMITER lines only in those files.
- If a framework needs the same code, store one statement per file without DELIMITER lines, and let the framework run each file as one call.
- Always put the line that restores the semicolon right after the last delimiter, so later statements in the same file are not swallowed.
- Use // or $$ as the delimiter and avoid backslash and letters.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Restoring a dump stops with ERROR at line N: Unknown command
ERROR at line 1153: Unknown command
The mysql client treats a backslash outside a quoted string as the start of one of its own short commands, so a stray one in an imported file gives Unknown command.
2 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fix