| Bug #77249 | Drop table may cause slaves to break | ||
|---|---|---|---|
| Submitted: | 4 Jun 2015 22:35 | Modified: | 14 Dec 2015 16:02 |
| Reporter: | Santosh Praneeth Banda | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.6.23, 5.6.26, 5.5.45, 5.6.27 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[4 Jun 2015 22:35]
Santosh Praneeth Banda
[5 Jun 2015 7:12]
MySQL Verification Team
Hello Santosh, Thank you for the report and test case. Observed that 5.6.26, and 5.5.45 latest builds are affected. Thanks, Umesh
[5 Jun 2015 7:15]
MySQL Verification Team
// 5.6.26 [umshastr@hod03]/export/umesh/server/binaries/mysql-5.6.26/mysql-test: ./mtr 77249 Logging: ./mtr 77249 2015-06-05 08:58:26 0 [Note] /export/umesh/server/binaries/mysql-5.6.26/bin/mysqld (mysqld 5.6.26-enterprise-commercial-advanced) starting as process 30685 ... 2015-06-05 08:58:26 30685 [Note] Plugin 'FEDERATED' is disabled. 2015-06-05 08:58:26 30685 [Note] Binlog end 2015-06-05 08:58:26 30685 [Note] Shutting down plugin 'MyISAM' 2015-06-05 08:58:26 30685 [Note] Shutting down plugin 'CSV' MySQL Version 5.6.26 Checking supported features... - SSL connections supported Collecting tests... Checking leftover processes... Removing old var directory... Creating var directory '/export/umesh/server/binaries/mysql-5.6.26/mysql-test/var'... Installing system database... ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009 include/master-slave.inc Warnings: Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. [connection master] create table `drop_tablé_test`(a int); drop table `drop_tablé_test`; include/rpl_end.inc DO_NOT_CHECK_IN_THIS_LINE: include/show_rpl_debug_info.inc should only be used for debugging. Never check in a test that calls it on success. CURRENT_CONNECTION = 'server_2' ############################## server_2 ############################## **** SHOW WARNINGS on server_2 **** SHOW WARNINGS; Level Code Message Note 1759 Sending passwords in plain text without SSL/TLS is extremely insecure. Note 1760 Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. **** SELECT replication-related variables on server_2 **** SELECT NOW(), @@SERVER_ID, @@SERVER_UUID, @@PORT; NOW() 2015-06-05 09:58:29 @@SERVER_ID 2 @@SERVER_UUID 455385aa-0b50-11e5-974c-0010e05f3e06 @@PORT 13001 **** GTID_* on server_2 **** SELECT @@GLOBAL.GTID_EXECUTED, @@GLOBAL.GTID_PURGED, @@GLOBAL.GTID_OWNED, @@SESSION.GTID_EXECUTED, @@SESSION.GTID_OWNED; @@GLOBAL.GTID_EXECUTED @@GLOBAL.GTID_PURGED @@GLOBAL.GTID_OWNED @@SESSION.GTID_EXECUTED @@SESSION.GTID_OWNED **** SHOW SLAVE STATUS on server_2 **** SHOW SLAVE STATUS; Slave_IO_State Waiting for master to send event Master_Host 127.0.0.1 Master_User root Master_Port 13000 Connect_Retry 1 Master_Log_File master-bin.000001 Read_Master_Log_Pos 363 Relay_Log_File slave-relay-bin.000002 Relay_Log_Pos 396 Relay_Master_Log_File master-bin.000001 Slave_IO_Running Yes Slave_SQL_Running No Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno 1051 Last_Error Error 'Unknown table 'test.drop_tablé_test'' on query. Default database: 'test'. Query: 'DROP TABLE `drop_tablé_test` /* generated by server */' Skip_Counter 0 Exec_Master_Log_Pos 232 Relay_Log_Space 700 Until_Condition None Until_Log_File Until_Log_Pos 0 Master_SSL_Allowed No Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master NULL Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error Last_SQL_Errno 1051 Last_SQL_Error Error 'Unknown table 'test.drop_tablé_test'' on query. Default database: 'test'. Query: 'DROP TABLE `drop_tablé_test` /* generated by server */' Replicate_Ignore_Server_Ids Master_Server_Id 1 Master_UUID 451f5867-0b50-11e5-974c-0010e05f3e06 Master_Info_File /export/umesh/server/binaries/mysql-5.6.26/mysql-test/var/mysqld.2/data/master.info SQL_Delay 0 SQL_Remaining_Delay NULL Slave_SQL_Running_State Master_Retry_Count 10 Master_Bind Last_IO_Error_Timestamp Last_SQL_Error_Timestamp 150605 09:58:29 Master_SSL_Crl Master_SSL_Crlpath Retrieved_Gtid_Set Executed_Gtid_Set Auto_Position 0
[5 Jun 2015 7:15]
MySQL Verification Team
// 5.5.45
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.45/mysql-test: ./mtr 77249
defined(@array) is deprecated at lib/mtr_cases.pm line 339.
(Maybe you should just omit the defined()?)
defined(@array) is deprecated at ./mtr line 493.
(Maybe you should just omit the defined()?)
Logging: ./mtr 77249
150605 9:09:33 [Note] /export/umesh/server/binaries/mysql-5.5.45/bin/mysqld (mysqld 5.5.45-enterprise-commercial-advanced) starting as process 31495 ...
150605 9:09:33 [Note] Plugin 'FEDERATED' is disabled.
MySQL Version 5.5.45
Checking supported features...
- SSL connections supported
Collecting tests...
Removing old var directory...
Creating var directory '/export/umesh/server/binaries/mysql-5.5.45/mysql-test/var'...
Installing system database...
==============================================================================
TEST RESULT TIME (ms) or COMMENT
--------------------------------------------------------------------------
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
include/master-slave.inc
[connection master]
create table `drop_tablé_test`(a int);
drop table `drop_tablé_test`;
include/rpl_end.inc
############################## server_2 ##############################
**** SHOW WARNINGS on server_2 ****
SHOW WARNINGS;
Level Code Message
**** SELECT replication-related variables on server_2 ****
SELECT NOW(), @@SERVER_ID;
NOW() @@SERVER_ID
2015-06-05 10:09:36 2
**** SHOW SLAVE STATUS on server_2 ****
SHOW SLAVE STATUS;
Slave_IO_State Waiting for master to send event
Master_Host 127.0.0.1
Master_User root
Master_Port 13000
Connect_Retry 1
Master_Log_File master-bin.000001
Read_Master_Log_Pos 328
Relay_Log_File slave-relay-bin.000002
Relay_Log_Pos 355
Relay_Master_Log_File master-bin.000001
Slave_IO_Running Yes
Slave_SQL_Running No
Replicate_Do_DB
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table
Last_Errno 1051
Last_Error Error 'Unknown table 'drop_tablé_test'' on query. Default database: 'test'. Query: 'DROP TABLE `drop_tablé_test` /* generated by server */'
Skip_Counter 0
Exec_Master_Log_Pos 208
Relay_Log_Space 631
Until_Condition None
Until_Log_File
Until_Log_Pos 0
Master_SSL_Allowed No
Master_SSL_CA_File
Master_SSL_CA_Path
Master_SSL_Cert
Master_SSL_Cipher
Master_SSL_Key
Seconds_Behind_Master NULL
Master_SSL_Verify_Server_Cert No
Last_IO_Errno 0
Last_IO_Error
Last_SQL_Errno 1051
Last_SQL_Error Error 'Unknown table 'drop_tablé_test'' on query. Default database: 'test'. Query: 'DROP TABLE `drop_tablé_test` /* generated by server */'
Replicate_Ignore_Server_Ids
Master_Server_Id 1
[5 Jun 2015 7:16]
MySQL Verification Team
// 5.7.8 [umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.8/mysql-test: ./mtr 77249 Logging: ./mtr 77249 MySQL Version 5.7.8 Checking supported features... - SSL connections supported Collecting tests... Removing old var directory... Creating var directory '/export/umesh/server/binaries/mysql-5.7.8/mysql-test/var'... Installing system database... ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009 include/master-slave.inc Warnings: Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. [connection master] create table `drop_tablé_test`(a int); drop table `drop_tablé_test`; include/rpl_end.inc main.77249 [ pass ] 204 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 0.204 of 16 seconds executing testcases Completed: All 1 tests were successful.
[13 Aug 2015 6:06]
MySQL Verification Team
Bug #78036 marked as duplicate of this
[1 Sep 2015 7:15]
MySQL Verification Team
// 5.6.27 - affected [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.27: cat docs/INFO_SRC commit: 0eb0cf1dbe3b10724b4fdfe80298fc51f9a4c8c9 date: 2015-08-28 13:22:30 +0200 build-date: 2015-08-29 09:01:11 +0200 short: 0eb0cf1 branch: mysql-5.6 MySQL source 5.6.27 [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.27: cd mysql-test/ [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.27/mysql-test: cat t/77249.test source include/master-slave.inc; connection master; create table `drop_tablé_test`(a int); sync_slave_with_master; connection master; drop table `drop_tablé_test`; source include/rpl_end.inc; [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.27/mysql-test: ./mtr 77249 Logging: ./mtr 77249 2015-09-01 09:12:51 0 [Note] /export/umesh/server/binaries/mysql-advanced-5.6.27/bin/mysqld (mysqld 5.6.27-enterprise-commercial-advanced) starting as process 18218 ... 2015-09-01 09:12:51 18218 [Note] Plugin 'FEDERATED' is disabled. 2015-09-01 09:12:51 18218 [Note] Binlog end 2015-09-01 09:12:51 18218 [Note] Shutting down plugin 'MyISAM' 2015-09-01 09:12:51 18218 [Note] Shutting down plugin 'CSV' MySQL Version 5.6.27 Checking supported features... - SSL connections supported Collecting tests... Checking leftover processes... Removing old var directory... Creating var directory '/export/umesh/server/binaries/mysql-advanced-5.6.27/mysql-test/var'... Installing system database... ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009 include/master-slave.inc Warnings: Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. [connection master] create table `drop_tablé_test`(a int); drop table `drop_tablé_test`; include/rpl_end.inc DO_NOT_CHECK_IN_THIS_LINE: include/show_rpl_debug_info.inc should only be used for debugging. Never check in a test that calls it on success. CURRENT_CONNECTION = 'server_2' ############################## server_2 ############################## **** SHOW WARNINGS on server_2 **** SHOW WARNINGS; Level Code Message Note 1759 Sending passwords in plain text without SSL/TLS is extremely insecure. Note 1760 Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. **** SELECT replication-related variables on server_2 **** SELECT NOW(), @@SERVER_ID, @@SERVER_UUID, @@PORT; NOW() 2015-09-01 10:12:54 @@SERVER_ID 2 @@SERVER_UUID dd3fd9b8-5078-11e5-9a45-0010e05f3e06 @@PORT 13001 **** GTID_* on server_2 **** SELECT @@GLOBAL.GTID_EXECUTED, @@GLOBAL.GTID_PURGED, @@GLOBAL.GTID_OWNED, @@SESSION.GTID_EXECUTED, @@SESSION.GTID_OWNED; @@GLOBAL.GTID_EXECUTED @@GLOBAL.GTID_PURGED @@GLOBAL.GTID_OWNED @@SESSION.GTID_EXECUTED @@SESSION.GTID_OWNED **** SHOW SLAVE STATUS on server_2 **** SHOW SLAVE STATUS; Slave_IO_State Waiting for master to send event Master_Host 127.0.0.1 Master_User root Master_Port 13000 Connect_Retry 1 Master_Log_File master-bin.000001 Read_Master_Log_Pos 363 Relay_Log_File slave-relay-bin.000002 Relay_Log_Pos 396 Relay_Master_Log_File master-bin.000001 Slave_IO_Running Yes Slave_SQL_Running No Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno 1051 Last_Error Error 'Unknown table 'test.drop_tablé_test'' on query. Default database: 'test'. Query: 'DROP TABLE `drop_tablé_test` /* generated by server */' Skip_Counter 0 Exec_Master_Log_Pos 232 Relay_Log_Space 700 Until_Condition None Until_Log_File Until_Log_Pos 0 Master_SSL_Allowed No Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master NULL Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error Last_SQL_Errno 1051 Last_SQL_Error Error 'Unknown table 'test.drop_tablé_test'' on query. Default database: 'test'. Query: 'DROP TABLE `drop_tablé_test` /* generated by server */' Replicate_Ignore_Server_Ids Master_Server_Id 1 Master_UUID dd0c6390-5078-11e5-9a45-0010e05f3e06 Master_Info_File /export/umesh/server/binaries/mysql-advanced-5.6.27/mysql-test/var/mysqld.2/data/master.info SQL_Delay 0 SQL_Remaining_Delay NULL Slave_SQL_Running_State Master_Retry_Count 10 Master_Bind Last_IO_Error_Timestamp Last_SQL_Error_Timestamp 150901 10:12:54 Master_SSL_Crl Master_SSL_Crlpath Retrieved_Gtid_Set Executed_Gtid_Set Auto_Position 0
[11 Sep 2015 0:48]
Teng Li
Proposed fix for the bug
Attachment: droptableBinlogEncodingFix.txt (text/plain), 6.20 KiB.
[11 Sep 2015 0:49]
Teng Li
I have attached a proposed fix for this bug, please find it in the attached file I have just posted Best, Teng
[4 Dec 2015 15:31]
David Moss
Thanks for your feedback. This has been fixed in upcoming versions and the following was noted in the 5.5.48, 5.6.29 and 5.7.11 change logs: DROP TABLE statements are regenerated by the server before being written to the binary log. If a table or database name contained a non-regular character, such as non-latin characters, the regenerated statement was using the wrong name, breaking replication. The fix ensures that in such a case the regenerated name is correctly converted back to the original character set. Also during work on this bug, it was discovered that in the rare case that a table or database name contained 64 characters, the server was throwing an assert(M_TBLLEN < 128) assertion. The assertion has been corrected to be less than or equal 128.
