Bug #91766 Log of replication position when running CHANGE MASTER TO is wrong.
Submitted: 23 Jul 14:59 Modified: 24 Jul 13:23
Reporter: Jean-François Gagné Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.7.22 OS:Any
Assigned to: CPU Architecture:Any

[23 Jul 14:59] Jean-François Gagné
Description:
Hi,

when running CHANGE MASTER TO MASTER_HOST='a new host', MySQL adds a line in the error logs with the previous state of replication.  Here is an example of such line.

2018-07-23T14:47:37.674972Z 6 [Note] 'CHANGE MASTER TO FOR CHANNEL '' executed'. Previous state master_host='127.0.0.1', master_port= 16745, master_log_file='mysql-bin.000001', master_log_pos= 26783, master_bind=''. New state master_host='aa', master_port= 16745, master_log_file='', master_log_pos= 4, master_bind=''.

However, if the SQL Thread was stopped before the IO Thread, the log above is wrong.  I would expect the log to have data about the SQL Thread position, but it has data about the IO Thread position.

Many thanks for looking into that,

JFG

How to repeat:
slave1 [localhost] {msandbox} ((none)) > SELECT @@version;
+------------+
| @@version  |
+------------+
| 5.7.22-log |
+------------+
1 row in set (0.00 sec)

slave1 [localhost] {msandbox} ((none)) > STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

slave1 [localhost] {msandbox} ((none)) > STOP SLAVE; SHOW SLAVE STATUS\G
Query OK, 0 rows affected (0.00 sec)

*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 16745
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 26783
               Relay_Log_File: mysql-relay.000003
                Relay_Log_Pos: 2678
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
[...]
          Exec_Master_Log_Pos: 19447
              Relay_Log_Space: 27365
              Until_Condition: None
[...]
1 row in set (0.00 sec)

slave1 [localhost] {msandbox} ((none)) > CHANGE MASTER TO MASTER_HOST = 'aa';
Query OK, 0 rows affected (0.01 sec)

When running above commands, I have the following in the logs:

2018-07-23T14:47:01.450764Z 8 [Note] Error reading relay log event for channel '': slave SQL thread was killed
2018-07-23T14:47:29.904618Z 7 [Note] Slave I/O thread killed while reading event for channel ''
2018-07-23T14:47:29.904655Z 7 [Note] Slave I/O thread exiting for channel '', read up to log 'mysql-bin.000001', position 26783
2018-07-23T14:47:37.674972Z 6 [Note] 'CHANGE MASTER TO FOR CHANNEL '' executed'. Previous state master_host='127.0.0.1', master_port= 16745, master_log_file='mysql-bin.000001', master_log_pos= 26783, master_bind=''. New state master_host='aa', master_port= 16745, master_log_file='', master_log_pos= 4, master_bind=''.

But the position of the SQL Thread is _not_ "master_log_file='mysql-bin.000001', master_log_pos= 26783", but it is (as shown by the SHOW SLAVE STATUS\G):

        Relay_Master_Log_File: mysql-bin.000001
          Exec_Master_Log_Pos: 19447

So I would expect "master_log_file='mysql-bin.000001', master_log_pos= 19447" in the logs.
[24 Jul 13:23] Umesh Shastry
Hello Jean,

Thank you for the report.
Observed this with 5.7.22 build.

regards,
Umesh