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.
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.