Description:
In a GTID replicated set, after breaking replication by compressing a binlog needed by a slave server and then adjusting auto_position=0 and putting in file coordinates to fix replication, it is possible to set auto_position back to 1 but this causes "SHOW SLAVE STATUS" to not report the master_log_file.
If the slave is then stopped and started, it will show the correct file.
How to repeat:
$ make_replication_sandbox /Users/tatemcdaniel/sandboxes/binaries/mysql-5.6.37-macos10.12-x86_64.tar.gz
# executing "clear" on /Users/tatemcdaniel/sandboxes/rsandbox_mysql-5_6_37
executing "clear" on slave 1
executing "clear" on slave 2
executing "clear" on master
installing and starting master
installing slave 1
installing slave 2
starting slave 1
. sandbox server started
starting slave 2
. sandbox server started
initializing slave 1
initializing slave 2
replication directory installed in $HOME/sandboxes/rsandbox_mysql-5_6_37
$ ./enable_gtid
# option 'master-info-repository=table' added to master configuration file
# option 'relay-log-info-repository=table' added to master configuration file
# option 'gtid_mode=ON' added to master configuration file
# option 'log-slave-updates' added to master configuration file
# option 'enforce-gtid-consistency' added to master configuration file
# option 'master-info-repository=table' added to node1 configuration file
# option 'relay-log-info-repository=table' added to node1 configuration file
# option 'gtid_mode=ON' added to node1 configuration file
# option 'log-slave-updates' added to node1 configuration file
# option 'enforce-gtid-consistency' added to node1 configuration file
# option 'master-info-repository=table' added to node2 configuration file
# option 'relay-log-info-repository=table' added to node2 configuration file
# option 'gtid_mode=ON' added to node2 configuration file
# option 'log-slave-updates' added to node2 configuration file
# option 'enforce-gtid-consistency' added to node2 configuration file
# executing "stop" on /Users/tatemcdaniel/sandboxes/rsandbox_mysql-5_6_37
executing "stop" on slave 1
executing "stop" on slave 2
executing "stop" on master
# executing "start" on /Users/tatemcdaniel/sandboxes/rsandbox_mysql-5_6_37
executing "start" on master
. sandbox server started
executing "start" on slave 1
. sandbox server started
executing "start" on slave 2
. sandbox server started
$ ./m -e "SHOW BINARY LOGS;"
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 2721 |
| mysql-bin.000002 | 151 |
+------------------+-----------+
$ ./s1 -e "STOP SLAVE;"
$ ./m -e "CREATE DATABASE TEST1; FLUSH LOGS;"
$ ./m -e "SHOW BINARY LOGS;"
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 2721 |
| mysql-bin.000002 | 343 |
| mysql-bin.000003 | 191 |
+------------------+-----------+
$ gzip master/data/mysql-bin.00000{1..2}
$ ./s1 -e "START SLAVE; SELECT SLEEP(1); SHOW SLAVE STATUS\G"
+----------+
| SLEEP(1) |
+----------+
| 0 |
+----------+
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 127.0.0.1
Master_User: rsandbox
Master_Port: 24102
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 151
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
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: 151
Relay_Log_Space: 561
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: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 00024102-1111-1111-1111-111111111111
Master_Info_File: mysql.slave_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: 170727 14:36:33
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 1
$ gunzip master/data/mysql-bin.00000{1..2}.gz
$ 544 ./s1 -e "STOP SLAVE; RESET SLAVE; CHANGE MASTER TO master_log_file='mysql-bin.000002',master_log_pos=151, master_auto_position=0; START SLAVE; SELECT SLEEP(1); SHOW SLAVE STATUS\G"
-bash: 544: command not found
$ ./s1 -e "STOP SLAVE; RESET SLAVE; CHANGE MASTER TO master_log_file='mysql-bin.000002',master_log_pos=151, master_auto_position=0; START SLAVE; SELECT SLEEP(1); SHOW SLAVE STATUS\G"
+----------+
| SLEEP(1) |
+----------+
| 0 |
+----------+
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: rsandbox
Master_Port: 24102
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 191
Relay_Log_File: mysql-relay.000004
Relay_Log_Pos: 401
Relay_Master_Log_File: mysql-bin.000003
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: 191
Relay_Log_Space: 688
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: 00024102-1111-1111-1111-111111111111
Master_Info_File: mysql.slave_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: 00024102-1111-1111-1111-111111111111:1
Executed_Gtid_Set: 00024102-1111-1111-1111-111111111111:1
Auto_Position: 0
$ ./s1 -e "STOP SLAVE; RESET SLAVE; CHANGE MASTER TO master_auto_position=1; START SLAVE; SHOW SLAVE STATUS\G"
*************************** 1. row ***************************
Slave_IO_State: Checking master version
Master_Host: 127.0.0.1
Master_User: rsandbox
Master_Port: 24102
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
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: 0
Relay_Log_Space: 151
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: 00024102-1111-1111-1111-111111111111
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: System lock
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: 00024102-1111-1111-1111-111111111111:1
Auto_Position: 1
####NOTICE THE Master_Log_File is BLANK
$ ./s1 -e "STOP SLAVE; START SLAVE; SELECT SLEEP(1); SHOW SLAVE STATUS\G"
+----------+
| SLEEP(1) |
+----------+
| 0 |
+----------+
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: rsandbox
Master_Port: 24102
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 191
Relay_Log_File: mysql-relay.000003
Relay_Log_Pos: 361
Relay_Master_Log_File: mysql-bin.000003
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: 191
Relay_Log_Space: 771
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: 00024102-1111-1111-1111-111111111111
Master_Info_File: mysql.slave_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: 00024102-1111-1111-1111-111111111111:1
Auto_Position: 1
### NOTICE THE Master_Log_File is shown
Suggested fix:
Fix this so that master_log_file is correctly show in the above scenario
Description: In a GTID replicated set, after breaking replication by compressing a binlog needed by a slave server and then adjusting auto_position=0 and putting in file coordinates to fix replication, it is possible to set auto_position back to 1 but this causes "SHOW SLAVE STATUS" to not report the master_log_file. If the slave is then stopped and started, it will show the correct file. How to repeat: $ make_replication_sandbox /Users/tatemcdaniel/sandboxes/binaries/mysql-5.6.37-macos10.12-x86_64.tar.gz # executing "clear" on /Users/tatemcdaniel/sandboxes/rsandbox_mysql-5_6_37 executing "clear" on slave 1 executing "clear" on slave 2 executing "clear" on master installing and starting master installing slave 1 installing slave 2 starting slave 1 . sandbox server started starting slave 2 . sandbox server started initializing slave 1 initializing slave 2 replication directory installed in $HOME/sandboxes/rsandbox_mysql-5_6_37 $ ./enable_gtid # option 'master-info-repository=table' added to master configuration file # option 'relay-log-info-repository=table' added to master configuration file # option 'gtid_mode=ON' added to master configuration file # option 'log-slave-updates' added to master configuration file # option 'enforce-gtid-consistency' added to master configuration file # option 'master-info-repository=table' added to node1 configuration file # option 'relay-log-info-repository=table' added to node1 configuration file # option 'gtid_mode=ON' added to node1 configuration file # option 'log-slave-updates' added to node1 configuration file # option 'enforce-gtid-consistency' added to node1 configuration file # option 'master-info-repository=table' added to node2 configuration file # option 'relay-log-info-repository=table' added to node2 configuration file # option 'gtid_mode=ON' added to node2 configuration file # option 'log-slave-updates' added to node2 configuration file # option 'enforce-gtid-consistency' added to node2 configuration file # executing "stop" on /Users/tatemcdaniel/sandboxes/rsandbox_mysql-5_6_37 executing "stop" on slave 1 executing "stop" on slave 2 executing "stop" on master # executing "start" on /Users/tatemcdaniel/sandboxes/rsandbox_mysql-5_6_37 executing "start" on master . sandbox server started executing "start" on slave 1 . sandbox server started executing "start" on slave 2 . sandbox server started $ ./m -e "SHOW BINARY LOGS;" +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 2721 | | mysql-bin.000002 | 151 | +------------------+-----------+ $ ./s1 -e "STOP SLAVE;" $ ./m -e "CREATE DATABASE TEST1; FLUSH LOGS;" $ ./m -e "SHOW BINARY LOGS;" +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 2721 | | mysql-bin.000002 | 343 | | mysql-bin.000003 | 191 | +------------------+-----------+ $ gzip master/data/mysql-bin.00000{1..2} $ ./s1 -e "START SLAVE; SELECT SLEEP(1); SHOW SLAVE STATUS\G" +----------+ | SLEEP(1) | +----------+ | 0 | +----------+ *************************** 1. row *************************** Slave_IO_State: Master_Host: 127.0.0.1 Master_User: rsandbox Master_Port: 24102 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 151 Relay_Log_File: mysql-relay.000002 Relay_Log_Pos: 361 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: No 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: 151 Relay_Log_Space: 561 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: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.' Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 00024102-1111-1111-1111-111111111111 Master_Info_File: mysql.slave_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: 170727 14:36:33 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 1 $ gunzip master/data/mysql-bin.00000{1..2}.gz $ 544 ./s1 -e "STOP SLAVE; RESET SLAVE; CHANGE MASTER TO master_log_file='mysql-bin.000002',master_log_pos=151, master_auto_position=0; START SLAVE; SELECT SLEEP(1); SHOW SLAVE STATUS\G" -bash: 544: command not found $ ./s1 -e "STOP SLAVE; RESET SLAVE; CHANGE MASTER TO master_log_file='mysql-bin.000002',master_log_pos=151, master_auto_position=0; START SLAVE; SELECT SLEEP(1); SHOW SLAVE STATUS\G" +----------+ | SLEEP(1) | +----------+ | 0 | +----------+ *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: rsandbox Master_Port: 24102 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 191 Relay_Log_File: mysql-relay.000004 Relay_Log_Pos: 401 Relay_Master_Log_File: mysql-bin.000003 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: 191 Relay_Log_Space: 688 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: 00024102-1111-1111-1111-111111111111 Master_Info_File: mysql.slave_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: 00024102-1111-1111-1111-111111111111:1 Executed_Gtid_Set: 00024102-1111-1111-1111-111111111111:1 Auto_Position: 0 $ ./s1 -e "STOP SLAVE; RESET SLAVE; CHANGE MASTER TO master_auto_position=1; START SLAVE; SHOW SLAVE STATUS\G" *************************** 1. row *************************** Slave_IO_State: Checking master version Master_Host: 127.0.0.1 Master_User: rsandbox Master_Port: 24102 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: mysql-relay.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: 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: 0 Relay_Log_Space: 151 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: 00024102-1111-1111-1111-111111111111 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: System lock 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: 00024102-1111-1111-1111-111111111111:1 Auto_Position: 1 ####NOTICE THE Master_Log_File is BLANK $ ./s1 -e "STOP SLAVE; START SLAVE; SELECT SLEEP(1); SHOW SLAVE STATUS\G" +----------+ | SLEEP(1) | +----------+ | 0 | +----------+ *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: rsandbox Master_Port: 24102 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 191 Relay_Log_File: mysql-relay.000003 Relay_Log_Pos: 361 Relay_Master_Log_File: mysql-bin.000003 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: 191 Relay_Log_Space: 771 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: 00024102-1111-1111-1111-111111111111 Master_Info_File: mysql.slave_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: 00024102-1111-1111-1111-111111111111:1 Auto_Position: 1 ### NOTICE THE Master_Log_File is shown Suggested fix: Fix this so that master_log_file is correctly show in the above scenario