MySQL Bugs: #33812: mysql client incorrectly parsing DELIMITER

Bug #33812	mysql client incorrectly parsing DELIMITER
Submitted:	10 Jan 2008 23:16	Modified:	28 Jan 2009 22:00
Reporter:	Sean Pringle	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	5.0.52, 5.0.54, 5.1.23-rc	OS:	Any
Assigned to:	Jim Winstead	CPU Architecture:	Any
Tags:	DELIMITER, MySQL

Description:
Somewhere in 5.0.52 the MySQL client started parsing any 'delimiter' text in a query as the DELIMITER command, eg, even when used as part of extended_column_names.  This results in an SQL format error.

Running the same SQL statements using 5.0.50 or earlier version mysql cli shows no problem.

How to repeat:
Start 5.0.52 or 5.0.54 cli.

mysql> create table t1 (f1_delimiter int);
ERROR 1064 (42000): 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 1

At this stage, the cli delimiter has been changed as the following is required to continue normally:

mysql> delimiter ;

Try it again with back-ticks around the column name:

mysql> create table t1 (`f1_delimiter` int);
Query OK, 0 rows affected (0.00 sec)

Correction to initial description:  phrase "any 'delimiter' text" implies any use of the string anywhere in a query, which is wrong.  Further testing shows it is apparently only evident in any unticked column name in:

CREATE TABLE <name> (<column using 'delimiter'> ...);
SELECT <column using 'delimiter'> FROM...;

eg:

mysql> select f1_delimiter from t1;
ERROR 1054 (42S22): Unknown column 'f1_' in 'field list'

Thank you for the bug report.

Client from 5.1.23-rc is also affected.

Bug #34939 was marked as a duplicate of this one.

Remove unnecessary and incorrect code that tried to pull delimiter commands out of the middle of statements

Attachment: bug33812.patch (text/plain), 1.79 KiB.

The patch is against 5.1.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48247

2659 Georgi Kodinov	2008-06-20
      Bug#33812: mysql client incorrectly parsing DELIMITER
      Remove unnecessary and incorrect code that tried to pull delimiter commands out of the
      middle of statements

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48252

2659 Georgi Kodinov	2008-06-20
      Bug#33812: mysql client incorrectly parsing DELIMITER
      Remove unnecessary and incorrect code that tried to pull delimiter commands out of the
      middle of statements

Pushed in 5.1.26

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48263

2661 Tatiana A. Nurnberg	2008-06-20
      Bug#33812: mysql client incorrectly parsing DELIMITER
      
      Remove unnecessary and incorrect code that tried
      to pull delimiter commands out of the middle of
      statements.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48401

2642 Gleb Shchepa	2008-06-24
      back-port from 5.1.
      
      Bug#33812: mysql client incorrectly parsing DELIMITER
            
      Remove unnecessary and incorrect code that tried
      to pull delimiter commands out of the middle of
      statements.

Pushed into 5.0.66

Noted in 5.0.66, 5.1.26 changelogs.

The mysql client incorrectly parsed statements containing the word
delimiter in mid-statement.

Setting report to Patch queued pending push into 6.0.x.

The patch which was pushed into version 5.1.26-rc results in a new variant of the bug:

Having an inline comment without any text directly before keyword "DELIMITER" as you will find in mysqldump dump files (see snippet below), execution of such script will result in "... ERROR 1064 (42000): 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 4 ..."

snippet of mysqldump dump file:

--
-- Dumping routines for database 'test'
--
DELIMITER ;;
/*!50003 DROP FUNCTION IF EXISTS `f_TestFunction` */;;

If replacing the client (mysql.exe on my windows xp) with the one of version 5.1.25-rc this bug isn't shown.

Roland, thank you for your comment!  I have opened a new bug report about this:

Bug #38158, "mysql client regression, can't read dump files"

I used the text of your comment as the bug's description.  It will be given serious consideration.

Regards,

Timothy

Pushed into 5.1.26

Pushed into 5.1.27

This fix was reverted in 5.0.67 and 5.1.27 because it caused the problem noted in Bug#38158.

Pushed into 6.0.7-alpha  (revid:serg@mysql.com-20080722121106-wy84j0yvceyu72zr) (pib:2)

There is a patch for this bug at:

http://bazaar.launchpad.net/~jimw/mysql-server/bug-33812/revision/2673

if (com_go(&buffer, 0) > 0)             // < 0 is not fatal

A small oversight:  Comment neglects zero case.

Otherwise, ~jimw/mysql-server/bug-33812/revision/2673 approved.

Any little bit to simplify 'mysql' helps.  OK with this patch:

http://bazaar.launchpad.net/~jimw/mysql-server/bug-33812/revision/2673

Just a clarification on the versions to which it was pushed:

This bug was pushed to 5.1.28, but it _is not_ in current versions!

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.1.30-log | 
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1 (f1_delimiter INT);
ERROR 1064 (42000): 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 1

To clarify: there was a bug-fix for this that caused some ugly regressions. That was reverted. The updated patch to fix the original problem (without introducing more) has not yet been pushed.

Backporting to 5.0 and preparing to push.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/61391

2744 Chad MILLER	2008-12-11
      Bug#33812: mysql client incorrectly parsing DELIMITER
      
      Fix parsing of mysql client commands, especially in relation to
      single-line comments when --comments was specified.
      
      This is a little tricky, because we need to allow single-line
      comments in the middle of statements, but we don't want to allow
      client commands in the middle of statements. So in
      comment-preservation mode, we go ahead and send single-line
      comments to the server immediately when we encounter them on their
      own. 
      
      This is still slightly flawed, in that it does not handle a
      single-line comment with leading spaces, followed by a client-side
      command when --comment has been enabled. But this isn't a new
      problem, and it is quite an edge condition. Fixing it would require
      a more extensive overall of how the mysql client parses commands.

Queued to 5.0-, 5.1-, and 6.0-bugteam trees.

Pushed into 5.0.76 (revid:joro@sun.com-20090105160414-8q9j4bi1klkfwiup) (version source revid:azundris@mysql.com-20081230114734-nmsc37ak330zlygn) (merge vers: 5.0.76) (pib:6)

Noted in 5.0.76 changelog.

Setting report to NDI pending push into 5.1.x/6.0.x.

Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:azundris@mysql.com-20081230114838-cn52tu180wcrvh0h) (merge vers: 5.1.31) (pib:6)

Noted in 5.1.31 changelog.

Setting report to NDI pending push into 6.0.x.

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

Setting status back to NDI pending merge to 6.0 tree.

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)

Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:azundris@mysql.com-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)

Noted in 6.0.10 changelog.

Pushed into 5.0.82 (revid:chad@mysql.com-20090506130632-s1cl4ygdj9rt2rrz) (version source revid:chad@mysql.com-20090506130632-s1cl4ygdj9rt2rrz) (merge vers: 5.0.82) (pib:6)

Pushed into 5.1.36 (revid:joro@sun.com-20090528073639-yohsb4q1jzg7ycws) (version source revid:jimw@mysql.com-20090515174051-ndjvfd1e9hc9k9c3) (merge vers: 5.1.36) (pib:6)

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:joro@sun.com-20090515134506-5mq3a8fafgbkx6u1) (merge vers: 6.0.12-alpha) (pib:11)

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)