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:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: connector, Connector/J, stored procedures, stored programs

[5 Jan 2010 16:03] Bear Giles
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.
[5 Jan 2010 16:25] Mark Matthews
"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?
[6 Feb 2010 0:00] Bugs System
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".