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:
None 
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 11:13] Akshay Suryawanshi
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.
[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.