SQL Syntax error 1604 in CREATE PROCEDURE statement

Question

I am using this SQL query to generate a procedure:

DELIMITER //

DROP PROCEDURE IF EXISTS GetSessionID //
CREATE PROCEDURE GetSessionID(IN token VARCHAR(64), OUT id INTEGER)
BEGIN
    SELECT s.ID INTO id FROM Sessions AS s WHERE s.Token = token;
END //

DELIMITER ;

I get the following error message:

Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your   MySQL server version for the right syntax to use near 'DELIMITER //

DROP PROCEDURE IF EXISTS GetSessionID //
CREATE PROCEDURE "GetSessi' at line 1

I tried using also $$ as a delimiter, surrounding the procedure's name with double quotes and removing the space before the delimiters in the DROP statement and END statement. If I look at the reference, there is a space after the procedure name - I tried adding that too, and still doesn't work.

I am using Go's db.Exec method to execute queries for creating tables and procedures, one after another.

I don't understand why do I get this error, as the syntax looks correct to me. Worked also in Goland's SQL runner, doesn't work in Go.

Since the error starts at the very beginning - there must be something wrong there. Do you try to execute 2 queries at the same time? — juergen d, Jul 19 '20 at 20:24
I tested the statements executing the `DELIMITER`, then the `DROP`, then the `CREATE`, and then the other `DELIMITER` statement in one go, using Goland's SQL runner. It works there, and the procedure exists on the database. Or do you mean something else by "at the same time"? — Teodor Maxim, Jul 19 '20 at 20:29
On the database you could just execute all that code at once. But using a framework try to execute only the procedure. No delimiter definition, no drop. just the procedure: `CREATE PROCEDURE GetSessionID(IN token VARCHAR(64), OUT id INTEGER) BEGIN SELECT s.ID INTO id FROM Sessions AS s WHERE s.Token = token; END` — juergen d, Jul 19 '20 at 20:34
Tried it and it seems like there is a problem with the `DELIMITER` statement, as it gives me the same error on the next procedure I try to create. — Teodor Maxim, Jul 19 '20 at 20:38

Teodor Maxim · Accepted Answer · 2020-07-20T21:55:20.400

I found out what the problem actually was: The MySQL Go driver for the database/sql library I am using, go-sql-driver does not support multi statements by default. They can be activated as in here, although there are some limitations, documented there.

In conclusion, the problem is framework-related. I hope other people facing the same problem in Go will find this useful, as it isn't so obvious at the first glance. See also this Stack Overflow question

EDIT: the DELIMITER statement isn't supported by the server itself, but by the MySQL command line. It won't work even as a single statement.

score 0 · Answer 2 · answered Jul 19 '20 at 21:12

Please try the below code snippet.

DROP PROCEDURE statement needs to be ended by ';'. One small correction is needed with table alias.

DELIMITER //

DROP PROCEDURE IF EXISTS GetSessionID;

CREATE PROCEDURE GetSessionID(IN token VARCHAR(64), OUT id INTEGER)
BEGIN
  SELECT s.ID INTO id FROM Sessions s WHERE s.Token = token;
END //
    
DELIMITER ;

SQL Syntax error 1604 in CREATE PROCEDURE statement

2 Answers2