Bug #49785 Discrepancy in how RESET SLAVE operates
Submitted: 17 Dec 2009 21:37 Modified: 10 Jan 2013 11:30
Reporter: Roel Van de Paar (OCA) Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1.32,5.1.36, 5.1.39 OS:Linux
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[17 Dec 2009 21:37] Roel Van de Paar
Description:
Scenario 1: in my.cnf configure the following settings:

--------
server-id=<someserverid>
master_host='<hostname>'
master_port=<portofmaster>
master_user=<userid>
master_password=<pwd>
--------

Then, issue the following on the slave: 
STOP SLAVE; RESET SLAVE;

Observe what happens (among other things):
- relay-log.info deleted
- master.info deleted

Now, restart the slave (START SLAVE;), wait 20 seconds for the slave to sort itself out, then check the contents of the files:

---------------------
roel@roel-ubuntu-vm:/rep/5136/slave/data$ cat master.info 
15
mysql-bin.000001
106
<hostname>
<userid>
<pwd>
<portofmaster>
60
0

0
roel@roel-ubuntu-vm:/rep/5136/slave/data$ cat relay-log.info 
./roel-ubuntu-vm-relay-bin.000003
251
mysql-bin.000001
106
---------------------

Scenario 2: stop the server, remove all the my.cnf settings above (except for server-id) and restart the slave. Check that slave is working fine using SHOW SLAVE STATUS.

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Now, issue another STOP SLAVE; RESET SLAVE;

The same happens (relay-log.info deleted, master.info deleted)

Restart the slave with START SLAVE; and check the files again

---------------------
roel@roel-ubuntu-vm:/rep/5136/slave/data$ cat master.info 
15
                  /* << Master_log_file was lost       */
4                 /* << Read_Master_Log_pos was lost   */
<hostname>        /* << the host **is reused**         */
test              /* << the userid resets to 'test'    */
<pwd>             /* << the password **is reused**     */
3306              /* << the port resets to 3306        */
60
0

0
roel@roel-ubuntu-vm:/rep/5136/slave/data$ cat relay-log.info 
./roel-ubuntu-vm-relay-bin.000002
4

0
0
---------------------

SHOW SLAVE STATUS at this point:

---------------------
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: <hostname> 
                  Master_User: test
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:                /* << Master_log_file was lost */
          Read_Master_Log_Pos: 4              /* << Read_Master_Log_pos was lost */
               Relay_Log_File: roel-ubuntu-vm-relay-bin.000002
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             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: 0
              Relay_Log_Space: 106
              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: 2013
                Last_IO_Error: error connecting to master 'test@roel-ubuntu-vm:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1 row in set (0.00 sec)
---------------------

How to repeat:
Recreate scenario 1 / scenario 2 as per the above.

Suggested fix:
If scenario 1 works correctly, scenario 2 should be able to do the same. In scenario 1, it seems like the settings are re-read from the my.cnf file. In both scenario's the server is never restarted. As such, it should be possible to make scenario 2 work in exactly the same way as scenario 1 (and it already partly does: the hostname and password of the master are preserved) by storing such values in memory and re-retrieving them when a START SLAVE is issued after a RESET SLAVE. 

If for some reason (though I cannot see one) this is not the desired procedure/result (to have scenario 2 work in the same way as scenario 1) then instead all things should be cleaned up properly (i.e. no 'test' user, no port 3306, no storing of the previous master hostname, no storing of the previous password).
[17 Dec 2009 21:52] Roel Van de Paar
Verifying as D3, same on 5.1.39. Found & verified on Linux, not tested on Windows yet.

Workaround is to have all settings in my.cnf
[17 Dec 2009 22:00] Donna Harmon
Had same results as Scenario #2 above on 5.1.32
[17 Dec 2009 22:07] Roel Van de Paar
See bug #985
See bug #39782
See bug #39783
[5 Jan 2011 0:39] Roel Van de Paar
See also bug #28796