Bug #92629 Skipping a transaction does not work when replication(5.7->5.6) breaks
Description:
Skipping a transaction does not work when replication breaks due to a MySQL 5.6 slave receiving an incompatible DDL command from a MySQL 5.7 master.
For non-GTID replication, using sql_slave_skip_counter does not work. You need to use CHANGE MASTER and point MASTER_LOG_POS to the next transaction.
For GTID replication, it's not enough to inject an empty transaction. You need to use CHANGE MASTER, set MASTER_AUTO_POSITION=0 and point MASTER_LOG_POS to the next transaction.
How to repeat:
non-GTID replication
1. Setup async replication where master is 5.7 and slave is 5.6.
2. Create a user on the master.
mysql> CREATE USER 'testuser'@'localhost';
Query OK, 0 rows affected (0.01 sec)
3. Run an command that only works on 5.7 and does not work on 5.6
mysql> ALTER USER 'testuser'@'localhost' IDENTIFIED BY 'testuser';
Query OK, 0 rows affected (0.01 sec)
4. Check slave status on 5.6
** redacted **
Slave_IO_Running: Yes
Slave_SQL_Running: No
** redacted **
Last_Errno: 1064
Last_Error: Error '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 'IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A784' at line 1' on query. Default database: ''. Query: 'ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8''
** redacted **
5. Try skipping statement:
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE;
6. Check slave status and it's still not working:
** redacted **
Slave_IO_Running: Yes
Slave_SQL_Running: No
** redacted **
Last_Errno: 1064
Last_Error: Error '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 'IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A784' at line 1' on query. Default database: ''. Query: 'ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8''
** redacted **
Workaround is to use CHANGE MASTER and specify the position of the next transaction in MASTER_LOG_POS.
GTID replication:
1. Setup async replication where master is 5.7 and slave is 5.6.
2. Create a user on the master.
mysql> CREATE USER 'testuser'@'localhost';
Query OK, 0 rows affected (0.01 sec)
3. Run an command that only works on 5.7 and does not work on 5.6
mysql> ALTER USER 'testuser'@'localhost' IDENTIFIED BY 'testuser';
Query OK, 0 rows affected (0.01 sec)
4. Check slave status on 5.6
** redacted **
Slave_IO_Running: Yes
Slave_SQL_Running: No
** redacted **
Last_Errno: 1064
Last_Error: Error '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 'IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A784' at line 1' on query. Default database: ''. Query: 'ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8''
Skip_Counter: 0
Exec_Master_Log_Pos: 364
Relay_Log_Space: 1038
** redacted **
Seconds_Behind_Master: NULL
** redacted **
Last_SQL_Errno: 1064
Last_SQL_Error: Error '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 'IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A784' at line 1' on query. Default database: ''. Query: 'ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8''
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 00005723-0000-0000-0000-000000005723
Master_Info_File: /ssd/sandboxes/msb_5_6_41/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 181002 13:51:47
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 00005723-0000-0000-0000-000000005723:1-2
Executed_Gtid_Set: 00005723-0000-0000-0000-000000005723:1
Auto_Position: 1
5. Inject empty transaction on the slave to skip offending statement
STOP SLAVE;
SET GTID_NEXT='00005723-0000-0000-0000-000000005723:2';
BEGIN;
COMMIT;
SET GTID_NEXT=AUTOMATIC;
START SLAVE;
6. Check slave status and replication is still not working.
** redacted **
Slave_IO_Running: Yes
Slave_SQL_Running: No
** redacted **
Last_Errno: 1064
Last_Error: Error '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 'IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A784' at line 1' on query. Default database: ''. Query: 'ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8''
Skip_Counter: 0
Exec_Master_Log_Pos: 364
Relay_Log_Space: 1554
** redacted **
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1064
Last_SQL_Error: Error '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 'IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A784' at line 1' on query. Default database: ''. Query: 'ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8''
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 00005723-0000-0000-0000-000000005723
Master_Info_File: /ssd/sandboxes/msb_5_6_41/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 181002 13:56:28
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 00005723-0000-0000-0000-000000005723:1-2
Executed_Gtid_Set: 00005723-0000-0000-0000-000000005723:1-2
Auto_Position: 1
In addition to injecting an empty transaction, you need to use CHANGE MASTER and set MASTER_AUTO_POSITION to 0 and point MASTER_LOG_POS to the next transaction.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: Skipping a transaction does not work when replication breaks due to a MySQL 5.6 slave receiving an incompatible DDL command from a MySQL 5.7 master. For non-GTID replication, using sql_slave_skip_counter does not work. You need to use CHANGE MASTER and point MASTER_LOG_POS to the next transaction. For GTID replication, it's not enough to inject an empty transaction. You need to use CHANGE MASTER, set MASTER_AUTO_POSITION=0 and point MASTER_LOG_POS to the next transaction. How to repeat: non-GTID replication 1. Setup async replication where master is 5.7 and slave is 5.6. 2. Create a user on the master. mysql> CREATE USER 'testuser'@'localhost'; Query OK, 0 rows affected (0.01 sec) 3. Run an command that only works on 5.7 and does not work on 5.6 mysql> ALTER USER 'testuser'@'localhost' IDENTIFIED BY 'testuser'; Query OK, 0 rows affected (0.01 sec) 4. Check slave status on 5.6 ** redacted ** Slave_IO_Running: Yes Slave_SQL_Running: No ** redacted ** Last_Errno: 1064 Last_Error: Error '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 'IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A784' at line 1' on query. Default database: ''. Query: 'ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8'' ** redacted ** 5. Try skipping statement: mysql> STOP SLAVE; Query OK, 0 rows affected (0.00 sec) mysql> SET GLOBAL sql_slave_skip_counter=1; Query OK, 0 rows affected (0.00 sec) mysql> START SLAVE; 6. Check slave status and it's still not working: ** redacted ** Slave_IO_Running: Yes Slave_SQL_Running: No ** redacted ** Last_Errno: 1064 Last_Error: Error '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 'IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A784' at line 1' on query. Default database: ''. Query: 'ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8'' ** redacted ** Workaround is to use CHANGE MASTER and specify the position of the next transaction in MASTER_LOG_POS. GTID replication: 1. Setup async replication where master is 5.7 and slave is 5.6. 2. Create a user on the master. mysql> CREATE USER 'testuser'@'localhost'; Query OK, 0 rows affected (0.01 sec) 3. Run an command that only works on 5.7 and does not work on 5.6 mysql> ALTER USER 'testuser'@'localhost' IDENTIFIED BY 'testuser'; Query OK, 0 rows affected (0.01 sec) 4. Check slave status on 5.6 ** redacted ** Slave_IO_Running: Yes Slave_SQL_Running: No ** redacted ** Last_Errno: 1064 Last_Error: Error '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 'IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A784' at line 1' on query. Default database: ''. Query: 'ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8'' Skip_Counter: 0 Exec_Master_Log_Pos: 364 Relay_Log_Space: 1038 ** redacted ** Seconds_Behind_Master: NULL ** redacted ** Last_SQL_Errno: 1064 Last_SQL_Error: Error '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 'IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A784' at line 1' on query. Default database: ''. Query: 'ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8'' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 00005723-0000-0000-0000-000000005723 Master_Info_File: /ssd/sandboxes/msb_5_6_41/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 181002 13:51:47 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 00005723-0000-0000-0000-000000005723:1-2 Executed_Gtid_Set: 00005723-0000-0000-0000-000000005723:1 Auto_Position: 1 5. Inject empty transaction on the slave to skip offending statement STOP SLAVE; SET GTID_NEXT='00005723-0000-0000-0000-000000005723:2'; BEGIN; COMMIT; SET GTID_NEXT=AUTOMATIC; START SLAVE; 6. Check slave status and replication is still not working. ** redacted ** Slave_IO_Running: Yes Slave_SQL_Running: No ** redacted ** Last_Errno: 1064 Last_Error: Error '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 'IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A784' at line 1' on query. Default database: ''. Query: 'ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8'' Skip_Counter: 0 Exec_Master_Log_Pos: 364 Relay_Log_Space: 1554 ** redacted ** Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1064 Last_SQL_Error: Error '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 'IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A784' at line 1' on query. Default database: ''. Query: 'ALTER USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8'' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 00005723-0000-0000-0000-000000005723 Master_Info_File: /ssd/sandboxes/msb_5_6_41/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 181002 13:56:28 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 00005723-0000-0000-0000-000000005723:1-2 Executed_Gtid_Set: 00005723-0000-0000-0000-000000005723:1-2 Auto_Position: 1 In addition to injecting an empty transaction, you need to use CHANGE MASTER and set MASTER_AUTO_POSITION to 0 and point MASTER_LOG_POS to the next transaction.