Bug #75772 5.7.6->5.6 replication fails
Description:
Basic replication from 5.7.6 master to 5.6.23 slave fails after first statement.
Pre-validation by replication developer Joao Gramacho:
The "Executed_Gtid_Set: 00000000-0000-0000-0000-000000000000:0" at the end of the "How to Repeat" section is not a good sign.
It seems that the slave is assuming the ANONYMOUS GTID as '00000000-0000-0000-0000-000000000000:0'. So it applied the 'CREATE TABLE' and logged this GTID for it. The next statement (the INSERT) is using the same GTID, so it produces no changes, and no errors, just like a transaction that was already executed.
How to repeat:
Started 5.7.6 server (master) with --port=10001 --log-bin=master-bin --server-id=1
Started 5.6.23 server (slave) with --port=10002 --log-bin=slave-bin --server-id=2 --log-slave-updates
[slave] change master to master_host='localhost',master_port=10001, master_user='root'
[slave] start slave
[slave] show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 10001
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 153
Relay_Log_File: pid-relay-bin.000002
Relay_Log_Pos: 334
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 153
Relay_Log_Space: 505
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 243f4ba5-ac96-11e4-ad99-a44e31b96590
Master_Info_File: /home/jorge/Temp/57-56/slave/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
[master] create table t1 (c INT)
[slave] show tables
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
[master] insert into t1 values(1)
[master] select * from t1
+------+
| c |
+------+
| 1 |
+------+
[slave] select * from t1
[slave] select count(*) from t1
+----------+
| count(*) |
+----------+
| 0 |
+----------+
[slave] show binlog events
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| slave-bin.000001 | 4 | Format_desc | 2 | 120 | Server ver: 5.6.23-log, Binlog ver: 4 |
| slave-bin.000001 | 120 | Query | 1 | 217 | use `test`; create table t1 (c INT) |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
[slave] show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 10001
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 588
Relay_Log_File: pid-relay-bin.000002
Relay_Log_Pos: 769
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 588
Relay_Log_Space: 940
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 243f4ba5-ac96-11e4-ad99-a44e31b96590
Master_Info_File: /home/jorge/Temp/57-56/slave/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 00000000-0000-0000-0000-000000000000:0
Auto_Position: 0
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.064 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: Basic replication from 5.7.6 master to 5.6.23 slave fails after first statement. Pre-validation by replication developer Joao Gramacho: The "Executed_Gtid_Set: 00000000-0000-0000-0000-000000000000:0" at the end of the "How to Repeat" section is not a good sign. It seems that the slave is assuming the ANONYMOUS GTID as '00000000-0000-0000-0000-000000000000:0'. So it applied the 'CREATE TABLE' and logged this GTID for it. The next statement (the INSERT) is using the same GTID, so it produces no changes, and no errors, just like a transaction that was already executed. How to repeat: Started 5.7.6 server (master) with --port=10001 --log-bin=master-bin --server-id=1 Started 5.6.23 server (slave) with --port=10002 --log-bin=slave-bin --server-id=2 --log-slave-updates [slave] change master to master_host='localhost',master_port=10001, master_user='root' [slave] start slave [slave] show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: root Master_Port: 10001 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 153 Relay_Log_File: pid-relay-bin.000002 Relay_Log_Pos: 334 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 153 Relay_Log_Space: 505 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 243f4ba5-ac96-11e4-ad99-a44e31b96590 Master_Info_File: /home/jorge/Temp/57-56/slave/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 [master] create table t1 (c INT) [slave] show tables +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ [master] insert into t1 values(1) [master] select * from t1 +------+ | c | +------+ | 1 | +------+ [slave] select * from t1 [slave] select count(*) from t1 +----------+ | count(*) | +----------+ | 0 | +----------+ [slave] show binlog events +------------------+-----+-------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+-------------+---------------------------------------+ | slave-bin.000001 | 4 | Format_desc | 2 | 120 | Server ver: 5.6.23-log, Binlog ver: 4 | | slave-bin.000001 | 120 | Query | 1 | 217 | use `test`; create table t1 (c INT) | +------------------+-----+-------------+-----------+-------------+---------------------------------------+ [slave] show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: root Master_Port: 10001 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 588 Relay_Log_File: pid-relay-bin.000002 Relay_Log_Pos: 769 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 588 Relay_Log_Space: 940 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 243f4ba5-ac96-11e4-ad99-a44e31b96590 Master_Info_File: /home/jorge/Temp/57-56/slave/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 00000000-0000-0000-0000-000000000000:0 Auto_Position: 0