MySQL Bugs: #35909: mysqldump and reserved keywords

Bug #35909	mysqldump and reserved keywords
Submitted:	8 Apr 2008 18:09	Modified:	19 Feb 2009 19:14
Reporter:	Diego Medina	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	10.11 Distrib 5.0.51a	OS:	Linux (Fedora 8)
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any
Tags:	mysqldump

Description:
I created a store procedure using the query browser, then I used mysqldump to backup my database and the store procedure I created, so far, so good, but when I try to load the backup, I get a "syntax error"

looking into the sql dump file, I found that I used a reserved keyword as a variable name.

But I do not get an error while creating it (I also tried  creating the same SP using the mysql command line client and I did not get any error.

How to repeat:
I will upload the SP, then:

1- just copy and paste the code into MySQL query Browser and create the procedure
2- on a terminal screen, run:

mysqlump -uroot -p --database db_name_here  -R > bug.sql

3- then run

mysql -uroot -p db_name_here < bug.sql

and you will see the error:

ERROR 1064 (42000) at line 106: 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 '' at line 3

and the problem is with the line that has:

IN delimiter VARCHAR(10)

the delimiter word is the problem, if I use back ticks to escape the word delimiter, the restore process works as expected.

Suggested fix:
Have mysqldump properly escape variable names?

Or have the other clients tell me not so use reserved words (as if happens if you try to name a column "date"

dump to replicate the problem

Attachment: bug.sql (text/x-sql), 6.78 KiB.

Thank you for the report.

Verified as described. As workaround you can quote `delimiter` and other reserved words when create a procedure in MySQL Query Browser.

Cannot reproduce with 5.0.72, upgrade if desired and advise
if problem persists.

In 5.0.72, mysqldump dumps without quoting variable-names,
but mysql command-line client does not seem to break on
replaying the procedure.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html