| Bug #78121 | Incorrect value reported in SHOW SLAVE STATUS, Auto_Position | ||
|---|---|---|---|
| Submitted: | 18 Aug 2015 9:30 | Modified: | 10 Feb 2016 16:44 |
| Reporter: | Shlomi Noach (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.6, 5.6.26 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | GTID, replication | ||
[18 Aug 2015 15:58]
Shlomi Noach
"09" is of course a typo and should be "0"
[19 Aug 2015 8:31]
MySQL Verification Team
Hi Shlomi, Thank you for the report. Verified this behavior with 5.6.26 builds. Thanks, Umesh
[19 Aug 2015 8:33]
MySQL Verification Team
test results
Attachment: 78121_5.6.26.results (application/octet-stream, text), 13.30 KiB.
[10 Dec 2015 6:34]
Venkatesh Duggirala
Posted by developer: Post from Developer: ==================== There are two problems reported this bug. Issue 1) "change master to master_auto_position=0" command is accepted by slave server even when MASTER_LOG_FILE or MASTER_LOG_POS are *not* specified. In the documentation https://dev.mysql.com/doc/refman/5.6/en/replication-gtids-howto.html ,We say ======================================== If you need to revert from GTID-based replication (auto_position= 1) to replication based on files and positions(auto_position=0), you *must* use one or both of these options(MASTER_LOG_FILE AND MASTER_LOG_POS) together with MASTER_AUTO_POSITION = 0 in the CHANGE MASTER TO statement. ========================================= Unfortunately, documentation is wrong here and we will correct it soon. There is no requirement that you must specify one of the two parameters. If you dont specify, Slave will read the current values from slave_master_info and uses them at the time reconnecting to Master. Hence We will update https://dev.mysql.com/doc/refman/5.6/en/replication-gtids-howto.html and https://dev.mysql.com/doc/refman/5.6/en/change-master-to.html with the current information. Sorry for the inconvenience caused. Issue 2) Incorrect AUTO_POSITION value in show slave status. Given the above explanation for Issue-1 that the command is successful, I think this is not true. It is reporting correct AUTO_POSITION value in Show slave status. Here I would like to mention few things about AUTO_POSITION vs GTID_MODE Permissible values for GTID_MODE= { ON or OFF } (talking about 5.6 version here) Permissible values for AUTO_POSITION = { 0 or 1 } Position based Replication: (AUTO_POSITION= 0) means When Slave connects to Master, it says I have downloaded till binlog file X and binlog file position Y. And Master send the events starting from X:Y+1 location. GTID-based Replication: (AUTO_POSITION= 1 ) means When Slave connect to Master, it says I have downloaded X gtids. And Master sends all other gtid groups which are not part of set X. In case GTID_MODE=OFF, there is no meaning to AUTO_POSITION=1 and we throw error if some one try to set to. In case GTID_MODE=ON, both {0, 1} are allowed and it represents how Slave informs master what it has. And users can switch from 0 to 1 and 1 to 0 when Slave threads (IO and SQL threads) are down. At the re connection of IO thread, depends on AUTO_POSITION , initial handshake will happen. Please let me know if you have any doubts regarding the above explanation or please let me know if I missed something important from the bug report. If everything is OK, we will convert this bug into documentation bug and will fix it as reported in issue-1. Thanks, Venkatesh.
[10 Feb 2016 16:44]
David Moss
Thanks for your feedback. This was recently fixed in the documentation, with some incorrect information removed and mentions of CHANGE MASTER TO made more accurate.
Description: SHOW SLAVE STATUS presents with a "09" value of Auto_Position even while in fact replicating via GTID. This follows running the (incorrect yet accepted) statement: "change master to master_auto_position=0;" Said statement should also include MASTER_LOG_FILE or _MASTER_LOG_POS, but didn't. Which means it does not apply and slave continues to replicate via GTID. How to repeat: slave3 [localhost] {msandbox} ((none)) > stop slave; change master to master_auto_position=0; Query OK, 0 rows affected (0,02 sec) Query OK, 0 rows affected (0,03 sec) slave3 [localhost] {msandbox} ((none)) > show slave status \G *************************** 1. row *************************** Slave_IO_State: Master_Host: snoach-ams9 Master_User: rsandbox Master_Port: 21089 Connect_Retry: 60 Master_Log_File: mysql-bin.015607 Read_Master_Log_Pos: 883950 Relay_Log_File: mysql_sandbox21090-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.015607 Slave_IO_Running: No 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: 883950 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: 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: 102 Master_UUID: 2e8b5b01-81e3-11e4-972a-e25ec4bd140a Master_Info_File: /home/snoach/sandboxes/rsandbox_mysql-5_6_22/node3/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: 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: 230ea8ea-81e3-11e4-972a-e25ec4bd140a:1-2899 Auto_Position: 0 1 row in set (0,00 sec) slave3 [localhost] {msandbox} ((none)) > start slave; Query OK, 0 rows affected (0,02 sec) slave3 [localhost] {msandbox} ((none)) > show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: snoach-ams9 Master_User: rsandbox Master_Port: 21089 Connect_Retry: 60 Master_Log_File: mysql-bin.015607 Read_Master_Log_Pos: 898632 Relay_Log_File: mysql_sandbox21090-relay-bin.000002 Relay_Log_Pos: 14996 Relay_Master_Log_File: mysql-bin.015607 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: 898632 Relay_Log_Space: 15213 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: 102 Master_UUID: 2e8b5b01-81e3-11e4-972a-e25ec4bd140a Master_Info_File: /home/snoach/sandboxes/rsandbox_mysql-5_6_22/node3/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: 230ea8ea-81e3-11e4-972a-e25ec4bd140a:2900-2947 Executed_Gtid_Set: 230ea8ea-81e3-11e4-972a-e25ec4bd140a:1-2947 Auto_Position: 0 Suggested fix: Auto_Position value to present the true slave replication method. The statement "change master to master_auto_position=0;" should result with an error.