| Bug #70818 | Implement CHANGE MASTER exec_log_pos | ||
|---|---|---|---|
| Submitted: | 5 Nov 2013 11:13 | Modified: | 7 Nov 2013 18:21 |
| Reporter: | Akshay Suryawanshi (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S4 (Feature request) |
| Version: | 5.5.33 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | CHANGE MASTER, error log | ||
[5 Nov 2013 19:43]
Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Please read at http://dev.mysql.com/doc/refman/5.5/en/change-master-to.html: CHANGE MASTER TO changes the parameters that the slave server uses for connecting to the master server, for reading the master binary log, and reading the slave relay log. So you are changing read position, not exec position.
[6 Nov 2013 11:09]
Akshay Suryawanshi
Thanks for comment Sveta. We ran through the code, and yes the CHANGE MASTER really affects the SLAVE_IO thread and hence the read_master_log_pos. However, can we request the addition of the SLAVE_SQL thread info as well, i.e. including exec_master_log_pos and Relay_Master_Log_File too. This will help in diagnosing replication failures, via the error log, which is its main purpose.
[7 Nov 2013 18:21]
Sveta Smirnova
Of course, we can file this as a feature request. But I changed the request title to "Implement CHANGE MASTER exec_log_pos", so it better reflects current state. If you disagree simply propose your title.
Description: Change master status inaccurately indicated in the error log while doing a change master. mysql> stop slave sql_thread; mysql> show slave status \G ************************* 1. row ************************* Slave_IO_State: Waiting for master to send event Master_Host: 192.168.253.136 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 168924930 Relay_Log_File: host1-relay-bin.000009 Relay_Log_Pos: 344 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: No 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: 2456 Relay_Log_Space: 168903342 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: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 136 mysql> STOP SLAVE; Query OK, 0 rows affected (0.04 sec) mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=2456; Query OK, 0 rows affected (0.06 sec) 131105 23:41:18 [Note] Slave I/O thread killed while reading event 131105 23:41:18 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000005', position 168924930 131105 23:41:21 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='192.168.253.136', master_port='3306', master_log_file='mysql-bin.000005', master_log_pos='168924930'. New state master_host='192.168.253.136', master_port='3306', master_log_file='mysql-bin.000005', master_log_pos='2456'. As you can see the "master_log_pos" reported in the error log is the "read_master_log_pos" from the "SHOW SLAVE STATUS" output, whereas it should have the "exec_master_log_pos", which states the binlog events executed till now on the slave. How to repeat: Stop the SLAVE_SQL thread, and let SLAVE_IO copy the binlog events in the relay logs. stop slave sql_thread; After sometime, execute a CHANGE MASTER .. with the MASTER_LOG_FILE as "read_master_log_file" and MASTER_LOG_POS as "exec_master_log_pos". STOP SLAVE; CHANGE MASTER TO MASTER_LOG_FILE='<read_master_log_file>', MASTER_LOG_POS=<exec_master_log_pos>; Observe the status in the error log.