Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A script with DELIMITER fails with error 1064 near DELIMITER

ErrorERROR 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.

  • Applies to MySQL 8.0, MySQL 8.4, mysql client
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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.sql
  2. 2Match what you see, then jump to its fix

    1. 1The 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. 2ERROR 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. 3The error points into the middle of a word or an escaped string, not at DELIMITER itselfThe chosen delimiter clashes with the SQL textFix

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.sql

2Fix

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;
END

3Verify

Run the statement through your driver, then call the procedure.

CALL dorepeat(1000);

Confirmed by dev.mysql.com

Did this fix it?

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

Did this fix it?

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.sql

2Fix

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.sql

Confirmed by dev.mysql.com

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.