Bug #92629 Skipping a transaction does not work when replication(5.7->5.6) breaks
Submitted: 2 Oct 6:09 Modified: 3 Oct 9:58
Reporter: Jaime Sicam Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.6 OS:Any
Assigned to: Bogdan Kecman CPU Architecture:Any

[2 Oct 6:09] Jaime Sicam
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.
[3 Oct 9:58] Bogdan Kecman
Hi,

What you are trying to do is not supported. You cannot replicate statements from newer to older mysql version that are not supported on the older version.

https://dev.mysql.com/doc/refman/8.0/en/replication-compatibility.html
https://dev.mysql.com/doc/refman/5.7/en/replication-compatibility.html

all best
Bogdan