MySQL Bugs: #58425: Can't create a procedure using a mysql script.

Bug #58425	Can't create a procedure using a mysql script.
Submitted:	23 Nov 2010 19:16	Modified:	23 Nov 2010 19:29
Reporter:	Daniel Alves	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S2 (Serious)
Version:	5.1.47	OS:	Windows (Windows 2003 Server)
Assigned to:		CPU Architecture:	Any
Tags:	delimter, mysql script, problem, PROCEDURE

Description:
Hello everyone, im trying to create a mysql script that creates a mysql procedure. I tried a lot of ways but couldnt find any solutions. I think the problem could be at the 'delimiter' syntax. The same code that i execute on the query browser works but when i execute it by my script just doesnt work. I got MysqlSyntaxError. I looked for some solutions at google but i just found bug reports.
Thanks!

How to repeat:
Just execute this code below at mysql query browser.

-- Code --

DELIMITER //
CREATE PROCEDURE myProcedure ()
BEGIN
 CREATE TABLE myTable;
END ;
//

everything works! but when you type the same code at some script and try do execute you will get : 

 Error executing: DELIMITER // CREATE PROCEDURE myProcedure () BEGIN  CREATE TABLE myTable .  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 // CREATE PROCEDURE myProcedure () BEGIN  CREATE TABLE myTable' at line 1

1)
CREATE TABLE myTable;
.. is not a valid CREATE statement.  There will need to be at least one column like
CREATE TABLE myTable (id int);

Some clients require that 'a new delimiter is delimited with the old delimiter'.  So try:

DELIMITER // ;
CREATE PROCEDURE myProcedure ()
BEGIN
 CREATE TABLE myTable (id int);
END ;
// 
DELIMITER ; //

(and if it works this is not a bug anywhere.  DELIMITER syntax as documented in the MySQL manual applies to command line client only (note what paragraph it appears inside!).  Every client could implement another way (even though I think they should not).  DELIMITER is implemented in the client only and no 'DELIMITER  statement' is ever sent to the server)

DELIMITER originally is mysql command line client command, NOT SQL statement. It is used to define a string that means "SQL statement/sequence of SQL statements is entered entirely and should be sent to server". It is used for parsing the user input.

Some other tools, like Workbench or Query Browser, also parse and use DELIMITER command in a way similar to mysql command line client. If you write your own tool (in Java I assume from error message), you have either to find some other way for user to tell "send it to server" (like "Execute" button) or parse code and send everything entered before defined delimiter (but not delimiter itself).

There is no server bug here, sorry.

correction (missed a number/bullet): 

2)
Some clients require ...

ha .. Valeriy is slow! :-)