MySQL Bugs: #50091: Bogus syntax error defining stored procedure with connector 5.1.10, svr 5.1.41

Bug #50091	Bogus syntax error defining stored procedure with connector 5.1.10, svr 5.1.41
Submitted:	5 Jan 2010 16:03	Modified:	5 Feb 2010 16:57
Reporter:	Bear Giles	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.1.41-community	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	connector, Connector/J, stored procedures, stored programs

Description:
We are able to create stored procedures with the MySQL Query Browser but the same scripts fail when run programmatically or via eclipse.  We are using the latest version of MySQL connector/J, version 5.1.10.  Server is 5.1.41-community.

Others have reported identical problems in the MySQL Query Browser.  These problems disappeared when they updated to the latest QB.  This hints the problem is a mismatch between the connector and server.

Further investigation shows this problem occuring on the SP scripts in multiple previous versions of our application.

How to repeat:
Sample script:

DELIMITER $$

DROP PROCEDURE IF EXISTS `foo` $$
CREATE PROCEDURE `foo`()
BEGIN
  select 1;
END $$

DELIMITER ;

Error:

"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 'END $$

DELIMITER at line 1"

Suggested fix:
A workaround in the development environment is using the Query Browser to define stored procedures.  This doesn't work in the field since it will require our customers to perform unfamiliar tasks.

We have not investigated using the ODBC connector for this task.

We are investigating reverting to a prior version of the server as a temporary workaround.

"DELIMITER" is a keyword that only certain clients (that are line-based editors) understand, it's not a server keyword, and is not needed for Connector/J. Remove "DELIMITER", and references to any delimiters it declares, and this code should work.

Perhaps earlier versions of the server treated DELIMITER as a no-op, and now they don't?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".