Bug #26215 mysql command line client should not strip comments from SQL statements
Submitted: 9 Feb 2007 8:17 Modified: 21 Nov 2007 4:32
Reporter: Valeriy Kravchuk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:4.1.x, 5.0.x, 5.0.36-BK, 5.1 OS:Any
Assigned to: Alexey Kopytov CPU Architecture:Any
Tags: bfsm_2007_02_15, regression

[9 Feb 2007 8:17] Valeriy Kravchuk
Description:
The mysql command line client in versions 4.1.x and 5.0.x strips comments from SQL statements before submitting them to the server. The binary for version 4.0.x does not do this.

Both single line and multiline comments like this: /* comment */ are striped.

How to repeat:
In 5.0.36-BK:

mysql> select /* comment */ sleep(1) from mysql.user;

In other session:

mysql> show processlist;
+----+------+-----------+------+---------+------+--------------+----------------
-------------------+
| Id | User | Host      | db   | Command | Time | State        | Info
                   |
+----+------+-----------+------+---------+------+--------------+----------------
-------------------+
|  1 | root | localhost | test | Query   |    0 | NULL         | show processlis
t                  |
|  2 | root | localhost | test | Query   |    1 | Sending data | select   sleep(
1) from mysql.user |
+----+------+-----------+------+---------+------+--------------+----------------
-------------------+
2 rows in set (0.00 sec)

mysql> select 1,
    -> /* multiline
   /*>  comment */
    -> sleep(1) from t1;

In other session:

mysql> show processlist;
+----+------+-----------+------+---------+------+--------------+----------------
-------------+
| Id | User | Host      | db   | Command | Time | State        | Info
             |
+----+------+-----------+------+---------+------+--------------+----------------
-------------+
|  1 | root | localhost | test | Query   |    0 | NULL         | show processlis
t            |
|  2 | root | localhost | test | Query   |    2 | Sending data | select 1,

sleep(1) from t1 |
+----+------+-----------+------+---------+------+--------------+----------------
-------------+
2 rows in set (0.00 sec)

Suggested fix:
Please, restore behaviour of 4.0.x as comments like this are useful for query monitoring/killing code.
[29 Jun 2007 9:38] Sveta Smirnova
Bug #29425 was marked as duplicate of this one.
[29 Jun 2007 10:00] Lucio Chiappetti
As submitter of the duplicate bug 29425, I would suggest to raise the Severity to  S3. I have been happily using echo of (specially formatted) comments to captured stdout since mysql 3.23, and currently with 4.0.21 in my  a number of database administration scripts of which I redirect stdout to  a file, which I then feed into an awk script which converts them into an HTML log. The awk script relies on the fact that mysql comments in a custom form (i.e. /* @my-own-code@ comment text */ ) are included in stdout together with the mysql commands and output. 

It is quite inconvenient that echoing comments to stdout does not work in 5.0, as  this break usage of all my older scripts on our new 5.0 server !
[24 Oct 2007 1:35] James Day
Changed from S4 (the default) to S3 based on request from Sinisa in an associated support issue.

Lucio, we're looking at the possibility of getting this done in the 5.1 version of the client. That client should also work with older versions of the server. Not guaranteed, still being discussed internally.
[29 Oct 2007 15:25] 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/36564

ChangeSet@1.2547, 2007-10-29 18:25:21+03:00, kaa@polly.(none) +4 -0
  Fix for:
    bug #26215: mysql command line client should not strip comments
                from SQL statements
  and
    bug #11230: Keeping comments when storing stored procedures
  
  With the introduction of multiline comments support in the command line
  client (mysql) in MySQL 4.1, it became impossible to preserve
  client-side comments within single SQL statements or stored routines.
  This feature was useful for monitoring tools and maintenance.
  
  The patch adds a new option to the command line client
  ('--enable-comments', '-c') which allows to preserve SQL comments and
  send them to the server for single SQL statements, and to keep comments
  in the code for stored procedures / functions / triggers.
  
  The patch is a modification of the contributed patch from bug #11230
  with the following changes:
  - code style changes to conform to the coding guidelines
  - changed is_prefix() to my_strnncoll() to detect the DELIMITER
  command, since the first one is case-sensitive and not charset-aware
  - renamed t/comments-51.* to t/mysql_comments.*
  - removed tests for comments in triggers since 5.0 does not have SHOW
  CREATE TRIGGER (those tests will be added back in 5.1).
  
  The test cases are only for bug #11230. No automated test case for bug
  #26215 is possible due to the test suite deficiencies (though the cases
  from the bug report were tested manually).
[2 Nov 2007 10:40] 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/36971

ChangeSet@1.2547, 2007-11-02 13:40:34+03:00, kaa@polly.(none) +4 -0
  Fix for:
    bug #26215: mysql command line client should not strip comments
                from SQL statements
  and
    bug #11230: Keeping comments when storing stored procedures
  
  With the introduction of multiline comments support in the command line
  client (mysql) in MySQL 4.1, it became impossible to preserve
  client-side comments within single SQL statements or stored routines.
  This feature was useful for monitoring tools and maintenance.
  
  The patch adds a new option to the command line client
  ('--enable-comments', '-c') which allows to preserve SQL comments and
  send them to the server for single SQL statements, and to keep comments
  in the code for stored procedures / functions / triggers.
  
  The patch is a modification of the contributed patch from bug #11230
  with the following changes:
  - code style changes to conform to the coding guidelines
  - changed is_prefix() to my_strnncoll() to detect the DELIMITER
  command, since the first one is case-sensitive and not charset-aware
  - renamed t/comments-51.* to t/mysql_comments.*
  - removed tests for comments in triggers since 5.0 does not have SHOW
  CREATE TRIGGER (those tests will be added back in 5.1).
  
  The test cases are only for bug #11230. No automated test case for bug
  #26215 is possible due to the test suite deficiencies (though the cases
  from the bug report were tested manually).
[2 Nov 2007 13:40] 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/36989

ChangeSet@1.2589, 2007-11-02 16:40:08+03:00, kaa@polly.(none) +3 -0
  5.1-specific changes for bug #26215 after merging the patch from 5.0:
  
  - Added trigger tests back.
  - Fixed test cases to match the extended output format of SHOW CREATE ...
  - Replaced 'gptr' with 'uchar *'.
[5 Nov 2007 10:30] 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/37076

ChangeSet@1.2548, 2007-11-05 13:30:31+03:00, kaa@polly.(none) +1 -0
  Fixed code that parses the DELIMITER command to correctly calculate 
  the length of the remaining input string.
  
  This is to fix mysqldump test failure in PB introduced by the patch
  for bug #26215.
[5 Nov 2007 19:26] Jon Block
I'm working on a project where databases are moved around via MySQL commandline imports. We need to ensure that comments do not get lost with the procedures. At what stage could we expect to see this patch incorporated into a production ready release?

Thank you!
Jon
[6 Nov 2007 17:58] Alexey Kopytov
Jon,

The fix has been pushed into internal trees. You can expect this to be available in the next releases of 5.0 and 5.1.
[16 Nov 2007 9:30] Bugs System
Pushed into 5.0.52
[16 Nov 2007 9:32] Bugs System
Pushed into 5.1.23-rc
[16 Nov 2007 9:35] Bugs System
Pushed into 6.0.4-alpha
[19 Nov 2007 12:28] 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/38049

ChangeSet@1.2551, 2007-11-19 15:27:44+03:00, kaa@polly.(none) +1 -0
  Changed the help text for --comments to make it clear which option
  disables the option explicitely.
  Changed the option location in code so that --help will show it in
  lexical option order.
  
  This is for bug #26215: mysql command line client should not strip
  comments from SQL statements
[21 Nov 2007 4:32] Paul Dubois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.

mysql stripped comments from statements sent to the server. Now the
--comments or --skip-comments option can be used to control whether
to retain or strip comments. The default is --skip-comments.
[14 Dec 2007 8:15] Bugs System
Pushed into 5.0.54
[14 Dec 2007 8:17] Bugs System
Pushed into 5.1.23-rc
[14 Dec 2007 8:21] Bugs System
Pushed into 6.0.5-alpha
[3 Jan 2008 21:06] Kolbe Kegel
This should absolutely *not* have been pushed to 5.0. If you don't understand why, please ask me and we will try to figure out a time when we can discuss this.
[17 Jul 2008 23:46] James Day
This caused bug #33057 and affects mysql client versions from 5.0.52 and up in that report.
[4 Mar 2009 15:53] Valeriy Kravchuk
Bug #43376 was marked as a duplicate of this one.