MySQL Bugs: #87852: Unexpected quoting and dropping of comments in DROP TABLE commands

Bug #87852	Unexpected quoting and dropping of comments in DROP TABLE commands
Submitted:	25 Sep 2017 10:26	Modified:	25 Sep 2017 16:27
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.7.18, 5.7.19	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	drop table, quoting, removing comments

Description:
I see the following when CREATING and then DROPPING a table in 5.7.18:

[user@myserver ~]$ mysql -c mydb
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16035
Server version: 5.7.18-log MySQL Community Server (GPL)
...
root@myserver [mydb]> /* start comment */ create table /* middle comment */ simon (id int) /* end comment */;
Query OK, 0 rows affected (0.01 sec)

root@myserver [mydb]> /* start comment */ drop table /* middle comment */  simon /* end comment */;
Query OK, 0 rows affected (0.01 sec)

root@myserver [mydb]> exit
Bye

Looking at the binlogs I notice the following

[user@myserver ~]$ sudo mysqlbinlog /path/to/binlog.000003 | grep -2 simon
#170925 12:15:52 server id 123456789  end_log_pos 25855371 CRC32 0x80982568     Query   thread_id=16035 exec_time=0     error_code=0
SET TIMESTAMP=1506334552/*!*/;
/* start comment */ create table /* middle comment */ simon (id int) /* end comment */
/*!*/;
# at 25855371
--
#170925 12:15:56 server id 123456789  end_log_pos 26106246 CRC32 0xb5e6c341     Query   thread_id=16035 exec_time=0     error_code=0
SET TIMESTAMP=1506334556/*!*/;
DROP TABLE `simon` /* generated by server */
/*!*/;
# at 26106246
12:16:00 [user@myserver ~]$

What seems surprising is:
* CREATE TABLE commands are stored in the binlogs as is with no modifications
* DROP TABLE commands are stored in the binlogs with comments removed and also with the table name quoted. (not as originally supplied by the user)

How to repeat:
See above.

Suggested fix:
* Explain why this happens. It seems to be related to the use of and handling of temporary tables. However, in this case no temporary tables where involved.
* For consistency it seems good to treat both these cases similarly.
* Losing quoting can be a pain as often users provide extra information in the SQL comments to indicate the user or process that runs the command. For internal forensics this information can be very valuable so losing this information is unhelpful
* If you can avoid doing this when not needed that would be good.

Hello Simon,

Thank you for the report.
I'm not seeing the reported issue on 5.7.18|5.7.19, anything diff required to trigger this issue? Please let me know. Only noticeable difference was back ticks used for enclosing table names in the DROP statement.

## 5.7.18/5.7.19 - default binlog format, and even tried statement/mixed

rm -rf 87852
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/87852 -v
bin/mysqld --log-bin=master-bin --server_id=1 --basedir=$PWD --datadir=$PWD/87852 --core-file --socket=/tmp/mysql_ushastry.sock --port=3306 --log-error=$PWD/87852/log.err 2>&1 &
.
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.18: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> use test
Database changed
root@localhost [test]> /* start comment */ create table /* middle comment */ simon (id int) /* end comment */;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> /* start comment */ drop table /* middle comment */  simon /* end comment */;
Query OK, 0 rows affected (0.00 sec)

== 5.7.19

[umshastr@vilma57]~/87621/mysql-5.7.19: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> create database test;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> use test
Database changed
root@localhost [test]> /* start comment */ create table /* middle comment */ simon (id int) /* end comment */;
Query OK, 0 rows affected (0.03 sec)

root@localhost [test]> /* start comment */ drop table /* middle comment */  simon /* end comment */;
Query OK, 0 rows affected (0.01 sec)

## 

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.18:  bin/mysqlbinlog 87852/master-bin.000001|grep -2 simon
use `test`/*!*/;
SET TIMESTAMP=1506342646/*!*/;
create table  simon (id int)
/*!*/;
# at 480
--
#170925 14:31:07 server id 1  end_log_pos 663 CRC32 0xfbdc6826  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1506342667/*!*/;
DROP TABLE `simon` /* generated by server */
/*!*/;
# at 663
--
#170925 14:34:16 server id 1  end_log_pos 830 CRC32 0x342d77a2  Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1506342856/*!*/;
create table  simon (id int)
/*!*/;
# at 830
--
#170925 14:34:25 server id 1  end_log_pos 1013 CRC32 0xaffed3c2         Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1506342865/*!*/;
DROP TABLE `simon` /* generated by server */
/*!*/;
# at 1013
--
#170925 14:35:52 server id 1  end_log_pos 1180 CRC32 0x83693522         Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1506342952/*!*/;
create table  simon (id int)
/*!*/;
# at 1180
--
#170925 14:35:56 server id 1  end_log_pos 1363 CRC32 0xa62d363d         Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1506342956/*!*/;
DROP TABLE `simon` /* generated by server */
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

Thanks,
Umesh

Hi Umesh,

Your mysql misses a -c or --comments which prevents comments from being stripped out silently.

Thank you, Simon.

######### 5.7.19
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.19: bin/mysqlbinlog 87852/master-bin.000001|grep -2 simon
use `test`/*!*/;
SET TIMESTAMP=1506356745/*!*/;
/* start comment */ create table /* middle comment */ simon (id int) /* end comment */
/*!*/;
# at 538
--
#170925 18:25:54 server id 1  end_log_pos 721 CRC32 0x78755c60  Query   thread_id=6     exec_time=0     error_code=0
SET TIMESTAMP=1506356754/*!*/;
DROP TABLE `simon` /* generated by server */
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

regards,
Umesh