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
Triage: Triaged: D3 (Medium) / R1 (None/Negligible) / E2 (Low)

[10 Jan 2008 23:16] Sean Pringle
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)
[10 Jan 2008 23:22] Sean Pringle
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'
[10 Jan 2008 23:25] Miguel Solorzano
Thank you for the bug report.
[28 Feb 2008 20:35] Valeriy Kravchuk
Client from 5.1.23-rc is also affected.
[28 Feb 2008 20:38] Valeriy Kravchuk
Bug #34939 was marked as a duplicate of this one.
[2 May 2008 16:17] Jim Winstead
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.

[6 May 2008 17:00] Jim Winstead
The patch is against 5.1.
[20 Jun 2008 15:09] Bugs System
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
[20 Jun 2008 15:21] Bugs System
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
[20 Jun 2008 15:21] Georgi Kodinov
Pushed in 5.1.26
[20 Jun 2008 17:00] Bugs System
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.
[24 Jun 2008 16:09] Bugs System
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.
[26 Jun 2008 13:40] Bugs System
Pushed into 5.0.66
[10 Jul 2008 17:41] Paul Dubois
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.
[13 Jul 2008 0:08] Roland Volkmann
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.
[16 Jul 2008 1:51] Timothy Smith
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
[21 Jul 2008 4:26] Bugs System
Pushed into 5.1.26
[22 Jul 2008 18:30] Bugs System
Pushed into 5.1.27
[23 Jul 2008 1:52] Paul Dubois
This fix was reverted in 5.0.67 and 5.1.27 because it caused the problem noted in Bug#38158.
[23 Jul 2008 13:08] Bugs System
Pushed into 6.0.7-alpha  (revid:serg@mysql.com-20080722121106-wy84j0yvceyu72zr) (pib:2)
[25 Jul 2008 16:36] Jim Winstead
There is a patch for this bug at:

http://bazaar.launchpad.net/~jimw/mysql-server/bug-33812/revision/2673
[6 Oct 2008 21:00] Chad MILLER
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.
[11 Oct 2008 6:29] Timothy Smith
Any little bit to simplify 'mysql' helps.  OK with this patch:

http://bazaar.launchpad.net/~jimw/mysql-server/bug-33812/revision/2673
[25 Nov 2008 21:01] Geert Vanderkelen
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
[26 Nov 2008 0:54] Jim Winstead
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.
[11 Dec 2008 16:00] Chad MILLER
Backporting to 5.0 and preparing to push.
[11 Dec 2008 17:45] Bugs System
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.
[11 Dec 2008 18:02] Chad MILLER
Queued to 5.0-, 5.1-, and 6.0-bugteam trees.
[6 Jan 2009 13:56] Bugs System
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)
[9 Jan 2009 0:53] Paul Dubois
Noted in 5.0.76 changelog.

Setting report to NDI pending push into 5.1.x/6.0.x.
[15 Jan 2009 6:34] Bugs System
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)
[15 Jan 2009 16:20] Paul Dubois
Noted in 5.1.31 changelog.

Setting report to NDI pending push into 6.0.x.
[19 Jan 2009 11:24] Bugs System
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)
[19 Jan 2009 13:02] Bugs System
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)
[19 Jan 2009 14:34] Jon Stephens
Setting status back to NDI pending merge to 6.0 tree.
[19 Jan 2009 16:08] Bugs System
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)
[20 Jan 2009 18:55] Bugs System
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)
[28 Jan 2009 22:00] Paul Dubois
Noted in 6.0.10 changelog.
[6 May 2009 20:23] Bugs System
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)
[28 May 2009 8:15] Bugs System
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)
[17 Jun 2009 19:23] Bugs System
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)
[26 Aug 2009 13:46] Bugs System
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)
[26 Aug 2009 13:46] Bugs System
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)
[26 Aug 2009 13:48] Bugs System
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)
[27 Aug 2009 16:32] Bugs System
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)