MySQL delimiter syntax error

Question

I'm trying to change the MySQL command delimiter so I can create a procedure with multiple commands in it. However, the delimiter command does not seem to be recognised on MySQL 5.1.47. I tested it on MySQL 5.0.91, and it did work there.

DELIMITER //;
DELIMITER ;//

I'm trying to run this from phpmyadmin, in both situations. Using 5.0.91 instead isn't an option because I need to use events (CREATE EVENT).

Error message:

#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 //' at line 1 

Is there a reason it's not working, or is there an alternative to accomplish the same thing (creating a procedure with multiple queries)?

Answer 1

DELIMITER is not a MySQL command. It's a command that your MySQL client needs to support. I was running PHPMyAdmin 2.8.2.4, which didn't support it. When I upgraded to the newest version, which is currently 3.4.9, it worked just fine. Your MySQL version has nothing to do with DELIMITER and whether it's supported or not.

Answer 2

You don't need to delimit the DELIMIT statements

DELIMITER //

procedure here etc

DELIMITER ;

Exactly as per "Defining Stored Programs" in the MySQL docs.

And if you can control versions, the latest is 5.5.20. Why not use that?

Edit:

The error message indicates an error in the previous statement... if this can't be seem force it thus

; /* <- force previous statement termination */ DELIMITER //

procedure here etc

DELIMITER ;