Bug #842 relay logs are not cleaned out
Submitted: 14 Jul 2003 6:05 Modified: 14 Jul 2003 6:24
Reporter: David Newcomb (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:4.0.13 OS:Microsoft Windows (Windows 2000)
Assigned to: CPU Architecture:Any

[14 Jul 2003 6:05] David Newcomb
Description:
When starting and stopping slaving, the relay-bin logs are not deleted.
The "change master to ..."  closes and re-opens the relay logs, even if master_host='';
If you are switching between replication masters, the relay-bin logs just fill up the directory.

How to repeat:

Contents of Initial MySQL data direcory:

C:\mysql\data>dir /on
 Volume in drive C has no label.
 Volume Serial Number is 283E-C3F4

 Directory of C:\mysql\data

14/07/2003  13:31       <DIR>          .
14/07/2003  13:31       <DIR>          ..
01/07/2003  12:37       <DIR>          mysql
               0 File(s)              0 bytes
               3 Dir(s)  11,521,646,592 bytes free

Startup MySQL:
C:\mysql\data>dir /on
 Volume in drive C has no label.
 Volume Serial Number is 283E-C3F4

 Directory of C:\mysql\data

14/07/2003  13:31       <DIR>          .
14/07/2003  13:31       <DIR>          ..
14/07/2003  13:31                  925 Gamlan.err
14/07/2003  13:31                   79 Gamlan-bin.001
14/07/2003  13:31                   17 Gamlan-bin.index
14/07/2003  13:31                  123 Gamlan-slow.log
14/07/2003  13:31               25,088 ib_arch_log_0000000000
14/07/2003  13:31            5,242,880 ib_logfile0
14/07/2003  13:31            5,242,880 ib_logfile1
14/07/2003  13:31           10,485,760 ibdata1
01/07/2003  12:37       <DIR>          mysql
               8 File(s)     20,997,752 bytes
               3 Dir(s)  11,500,642,304 bytes free

mysql> change master to master_host='stour';
C:\mysql\data>dir /on
 Volume in drive C has no label.
 Volume Serial Number is 283E-C3F4

 Directory of C:\mysql\data

14/07/2003  13:32       <DIR>          .
14/07/2003  13:32       <DIR>          ..
14/07/2003  13:31                  925 Gamlan.err
14/07/2003  13:31                   79 Gamlan-bin.001
14/07/2003  13:31                   17 Gamlan-bin.index
14/07/2003  13:32                    4 Gamlan-relay-bin.001
14/07/2003  13:32                   23 Gamlan-relay-bin.index
14/07/2003  13:31                  123 Gamlan-slow.log
14/07/2003  13:31               25,088 ib_arch_log_0000000000
14/07/2003  13:31            5,242,880 ib_logfile0
14/07/2003  13:31            5,242,880 ib_logfile1
14/07/2003  13:31           10,485,760 ibdata1
14/07/2003  13:32                   23 master.info
01/07/2003  12:37       <DIR>          mysql
14/07/2003  13:32                   28 relay-log.info
              12 File(s)     20,997,830 bytes
               3 Dir(s)  11,500,642,304 bytes free

mysql> start slave;
C:\mysql\data>dir /on
 Volume in drive C has no label.
 Volume Serial Number is 283E-C3F4

 Directory of C:\mysql\data

14/07/2003  13:32       <DIR>          .
14/07/2003  13:32       <DIR>          ..
14/07/2003  13:33                1,197 Gamlan.err
14/07/2003  13:31                   79 Gamlan-bin.001
14/07/2003  13:31                   17 Gamlan-bin.index
14/07/2003  13:33                  119 Gamlan-relay-bin.001
14/07/2003  13:32                   23 Gamlan-relay-bin.index
14/07/2003  13:31                  123 Gamlan-slow.log
14/07/2003  13:31               25,088 ib_arch_log_0000000000
14/07/2003  13:31            5,242,880 ib_logfile0
14/07/2003  13:31            5,242,880 ib_logfile1
14/07/2003  13:31           10,485,760 ibdata1
14/07/2003  13:33                   37 master.info
01/07/2003  12:37       <DIR>          mysql
14/07/2003  13:33                   44 relay-log.info
              12 File(s)     20,998,247 bytes
               3 Dir(s)  11,500,642,304 bytes free

mysql> stop slave;
C:\mysql\data>dir /on
 Volume in drive C has no label.
 Volume Serial Number is 283E-C3F4

 Directory of C:\mysql\data

14/07/2003  13:32       <DIR>          .
14/07/2003  13:32       <DIR>          ..
14/07/2003  13:33                1,541 Gamlan.err
14/07/2003  13:31                   79 Gamlan-bin.001
14/07/2003  13:31                   17 Gamlan-bin.index
14/07/2003  13:33                  119 Gamlan-relay-bin.001
14/07/2003  13:32                   23 Gamlan-relay-bin.index
14/07/2003  13:31                  123 Gamlan-slow.log
14/07/2003  13:31               25,088 ib_arch_log_0000000000
14/07/2003  13:31            5,242,880 ib_logfile0
14/07/2003  13:31            5,242,880 ib_logfile1
14/07/2003  13:31           10,485,760 ibdata1
14/07/2003  13:33                   37 master.info
01/07/2003  12:37       <DIR>          mysql
14/07/2003  13:33                   44 relay-log.info
              12 File(s)     20,998,591 bytes
               3 Dir(s)  11,500,642,304 bytes free

mysql> reset slave;
C:\mysql\data>dir /on
 Volume in drive C has no label.
 Volume Serial Number is 283E-C3F4

 Directory of C:\mysql\data

14/07/2003  13:36       <DIR>          .
14/07/2003  13:36       <DIR>          ..
14/07/2003  13:33                1,541 Gamlan.err
14/07/2003  13:31                   79 Gamlan-bin.001
14/07/2003  13:31                   17 Gamlan-bin.index
14/07/2003  13:36                    4 Gamlan-relay-bin.001
14/07/2003  13:36                   23 Gamlan-relay-bin.index
14/07/2003  13:31                  123 Gamlan-slow.log
14/07/2003  13:31               25,088 ib_arch_log_0000000000
14/07/2003  13:31            5,242,880 ib_logfile0
14/07/2003  13:31            5,242,880 ib_logfile1
14/07/2003  13:31           10,485,760 ibdata1
01/07/2003  12:37       <DIR>          mysql
              10 File(s)     20,998,395 bytes
               3 Dir(s)  11,500,642,304 bytes free

mysql> show slave status\G
*************************** 1. row ***************************
          Master_Host: stour
          Master_User: qrepl
          Master_Port: 3306
        Connect_retry: 2
      Master_Log_File:
  Read_Master_Log_Pos: 4
       Relay_Log_File: Gamlan-relay-bin.001
        Relay_Log_Pos: 4
Relay_Master_Log_File:
     Slave_IO_Running: No
    Slave_SQL_Running: No
      Replicate_do_db: quantel
  Replicate_ignore_db:
           Last_errno: 0
           Last_error:
         Skip_counter: 0
  Exec_master_log_pos: 0
      Relay_log_space: 4
1 row in set (0.00 sec)

mysql> change master to master_host='';
C:\mysql\data>dir /on
 Volume in drive C has no label.
 Volume Serial Number is 283E-C3F4

 Directory of C:\mysql\data

14/07/2003  13:37       <DIR>          .
14/07/2003  13:37       <DIR>          ..
14/07/2003  13:33                1,541 Gamlan.err
14/07/2003  13:31                   79 Gamlan-bin.001
14/07/2003  13:31                   17 Gamlan-bin.index
14/07/2003  13:36                    4 Gamlan-relay-bin.001
14/07/2003  13:37                    4 Gamlan-relay-bin.002
14/07/2003  13:37                   23 Gamlan-relay-bin.index
14/07/2003  13:31                  123 Gamlan-slow.log
14/07/2003  13:31               25,088 ib_arch_log_0000000000
14/07/2003  13:31            5,242,880 ib_logfile0
14/07/2003  13:31            5,242,880 ib_logfile1
14/07/2003  13:31           10,485,760 ibdata1
14/07/2003  13:37                   23 master.info
01/07/2003  12:37       <DIR>          mysql
14/07/2003  13:37                   28 relay-log.info
              13 File(s)     20,998,450 bytes
               3 Dir(s)  11,500,642,304 bytes free

type relay-log.info:
------start------
.\Gamlan-relay-bin.001
4

0
------end------

Suggested fix:
When they issue a change master to master_host='' then clean all the relay logs, as it says in the manual.

There should be a way of completely stopping replication so that it can not remember anything about what is was replicating from. Also you must be able to do this remotely. Shutting down MySQL and deleting the logs by hand is not a good enough solution.
[14 Jul 2003 6:24] Guilhem Bichot
Hi,

> Contents of Initial MySQL data direcory:
> 
> C:\mysql\data>dir /on
>  Volume in drive C has no label.
>  Volume Serial Number is 283E-C3F4
> 
>  Directory of C:\mysql\data
> 
> 14/07/2003  13:31       <DIR>          .
> 14/07/2003  13:31       <DIR>          ..
> 01/07/2003  12:37       <DIR>          mysql
>                0 File(s)              0 bytes
>                3 Dir(s)  11,521,646,592 bytes free
> 
> Startup MySQL:
> C:\mysql\data>dir /on
>  Volume in drive C has no label.
>  Volume Serial Number is 283E-C3F4
> 
>  Directory of C:\mysql\data
> 
> 14/07/2003  13:31       <DIR>          .
> 14/07/2003  13:31       <DIR>          ..
> 14/07/2003  13:31                  925 Gamlan.err
> 14/07/2003  13:31                   79 Gamlan-bin.001
> 14/07/2003  13:31                   17 Gamlan-bin.index
> 14/07/2003  13:31                  123 Gamlan-slow.log
> 14/07/2003  13:31               25,088 ib_arch_log_0000000000
> 14/07/2003  13:31            5,242,880 ib_logfile0
> 14/07/2003  13:31            5,242,880 ib_logfile1
> 14/07/2003  13:31           10,485,760 ibdata1
> 01/07/2003  12:37       <DIR>          mysql
>                8 File(s)     20,997,752 bytes
>                3 Dir(s)  11,500,642,304 bytes free
> 
> mysql> change master to master_host='stour';
> C:\mysql\data>dir /on
>  Volume in drive C has no label.
>  Volume Serial Number is 283E-C3F4
> 
>  Directory of C:\mysql\data
> 
> 14/07/2003  13:32       <DIR>          .
> 14/07/2003  13:32       <DIR>          ..
> 14/07/2003  13:31                  925 Gamlan.err
> 14/07/2003  13:31                   79 Gamlan-bin.001
> 14/07/2003  13:31                   17 Gamlan-bin.index
> 14/07/2003  13:32                    4 Gamlan-relay-bin.001
> 14/07/2003  13:32                   23 Gamlan-relay-bin.index
> 14/07/2003  13:31                  123 Gamlan-slow.log
> 14/07/2003  13:31               25,088 ib_arch_log_0000000000
> 14/07/2003  13:31            5,242,880 ib_logfile0
> 14/07/2003  13:31            5,242,880 ib_logfile1
> 14/07/2003  13:31           10,485,760 ibdata1
> 14/07/2003  13:32                   23 master.info
> 01/07/2003  12:37       <DIR>          mysql
> 14/07/2003  13:32                   28 relay-log.info
>               12 File(s)     20,997,830 bytes
>                3 Dir(s)  11,500,642,304 bytes free
> 
> mysql> start slave;
> C:\mysql\data>dir /on
>  Volume in drive C has no label.
>  Volume Serial Number is 283E-C3F4
> 
>  Directory of C:\mysql\data
> 
> 14/07/2003  13:32       <DIR>          .
> 14/07/2003  13:32       <DIR>          ..
> 14/07/2003  13:33                1,197 Gamlan.err
> 14/07/2003  13:31                   79 Gamlan-bin.001
> 14/07/2003  13:31                   17 Gamlan-bin.index
> 14/07/2003  13:33                  119 Gamlan-relay-bin.001
> 14/07/2003  13:32                   23 Gamlan-relay-bin.index
> 14/07/2003  13:31                  123 Gamlan-slow.log
> 14/07/2003  13:31               25,088 ib_arch_log_0000000000
> 14/07/2003  13:31            5,242,880 ib_logfile0
> 14/07/2003  13:31            5,242,880 ib_logfile1
> 14/07/2003  13:31           10,485,760 ibdata1
> 14/07/2003  13:33                   37 master.info
> 01/07/2003  12:37       <DIR>          mysql
> 14/07/2003  13:33                   44 relay-log.info
>               12 File(s)     20,998,247 bytes
>                3 Dir(s)  11,500,642,304 bytes free

After START SLAVE, the slave writes to the relay log.

> mysql> stop slave;
> C:\mysql\data>dir /on
>  Volume in drive C has no label.
>  Volume Serial Number is 283E-C3F4
> 
>  Directory of C:\mysql\data
> 
> 14/07/2003  13:32       <DIR>          .
> 14/07/2003  13:32       <DIR>          ..
> 14/07/2003  13:33                1,541 Gamlan.err
> 14/07/2003  13:31                   79 Gamlan-bin.001
> 14/07/2003  13:31                   17 Gamlan-bin.index
> 14/07/2003  13:33                  119 Gamlan-relay-bin.001
> 14/07/2003  13:32                   23 Gamlan-relay-bin.index
> 14/07/2003  13:31                  123 Gamlan-slow.log
> 14/07/2003  13:31               25,088 ib_arch_log_0000000000
> 14/07/2003  13:31            5,242,880 ib_logfile0
> 14/07/2003  13:31            5,242,880 ib_logfile1
> 14/07/2003  13:31           10,485,760 ibdata1
> 14/07/2003  13:33                   37 master.info
> 01/07/2003  12:37       <DIR>          mysql
> 14/07/2003  13:33                   44 relay-log.info
>               12 File(s)     20,998,591 bytes
>                3 Dir(s)  11,500,642,304 bytes free

Yes, the relay log is not deleted. If you do START SLAVE now, the slave will write at the end of this relay log. Until it exceeds max_binlog_size, in that case a new relay log will be created, and the old one deleted as soon as the SQL slave thread has executed it.

> mysql> reset slave;
> C:\mysql\data>dir /on
>  Volume in drive C has no label.
>  Volume Serial Number is 283E-C3F4
> 
>  Directory of C:\mysql\data
> 
> 14/07/2003  13:36       <DIR>          .
> 14/07/2003  13:36       <DIR>          ..
> 14/07/2003  13:33                1,541 Gamlan.err
> 14/07/2003  13:31                   79 Gamlan-bin.001
> 14/07/2003  13:31                   17 Gamlan-bin.index
> 14/07/2003  13:36                    4 Gamlan-relay-bin.001
> 14/07/2003  13:36                   23 Gamlan-relay-bin.index
> 14/07/2003  13:31                  123 Gamlan-slow.log
> 14/07/2003  13:31               25,088 ib_arch_log_0000000000
> 14/07/2003  13:31            5,242,880 ib_logfile0
> 14/07/2003  13:31            5,242,880 ib_logfile1
> 14/07/2003  13:31           10,485,760 ibdata1
> 01/07/2003  12:37       <DIR>          mysql
>               10 File(s)     20,998,395 bytes
>                3 Dir(s)  11,500,642,304 bytes free

The slave has deleted the previous Gamlan-relay-bin.001 and created a new empty one (see the size: 4). RESET SLAVE deletes all existing relay logs and creates a new empty one. It also deletes master.info and relay-log.info.

> mysql> show slave status\G
> *************************** 1. row ***************************
>           Master_Host: stour
>           Master_User: qrepl
>           Master_Port: 3306
>         Connect_retry: 2
>       Master_Log_File:
>   Read_Master_Log_Pos: 4
>        Relay_Log_File: Gamlan-relay-bin.001
>         Relay_Log_Pos: 4
> Relay_Master_Log_File:
>      Slave_IO_Running: No
>     Slave_SQL_Running: No
>       Replicate_do_db: quantel
>   Replicate_ignore_db:
>            Last_errno: 0
>            Last_error:
>          Skip_counter: 0
>   Exec_master_log_pos: 0
>       Relay_log_space: 4
> 1 row in set (0.00 sec)
> 
> mysql> change master to master_host='';
> C:\mysql\data>dir /on
>  Volume in drive C has no label.
>  Volume Serial Number is 283E-C3F4
> 
>  Directory of C:\mysql\data
> 
> 14/07/2003  13:37       <DIR>          .
> 14/07/2003  13:37       <DIR>          ..
> 14/07/2003  13:33                1,541 Gamlan.err
> 14/07/2003  13:31                   79 Gamlan-bin.001
> 14/07/2003  13:31                   17 Gamlan-bin.index
> 14/07/2003  13:36                    4 Gamlan-relay-bin.001
> 14/07/2003  13:37                    4 Gamlan-relay-bin.002
> 14/07/2003  13:37                   23 Gamlan-relay-bin.index
> 14/07/2003  13:31                  123 Gamlan-slow.log
> 14/07/2003  13:31               25,088 ib_arch_log_0000000000
> 14/07/2003  13:31            5,242,880 ib_logfile0
> 14/07/2003  13:31            5,242,880 ib_logfile1
> 14/07/2003  13:31           10,485,760 ibdata1
> 14/07/2003  13:37                   23 master.info
> 01/07/2003  12:37       <DIR>          mysql
> 14/07/2003  13:37                   28 relay-log.info
>               13 File(s)     20,998,450 bytes
>                3 Dir(s)  11,500,642,304 bytes free

CHANGE MASTER TO MASTER_HOST='' should not work.
The way to make a slave forget it is a slave is with RESET SLAVE.
But what is confusing you is that after RESET SLAVE, a small empty relay log exists, and SHOW SLAVE STATUS returns a row (not "empty set").
But the important thing is that RESET SLAVE has deleted master.info and relay-log.info.
So when the slave mysqld will restart, it will not find any *.info files, and then SHOW SLAVE STATUS will return "empty set".
Yes, this small 4-byte relay log will still exist and has to be deleted by hand.

> 
> type relay-log.info:
> ------start------
> .\Gamlan-relay-bin.001
> 4
> 
> 0
> ------end------
> 
> 
> Suggested fix:
> When they issue a change master to master_host='' then clean all the relay
> logs, as it says in the manual.

It clears all relay logs but starts a new empty one, to prepare for future replication.

> There should be a way of completely stopping replication so that it can
> not remember anything about what is was replicating from.

RESET SLAVE is intended for that. I agree total cleaning (no 4-byte relay log, empty set in SHOW SLAVE STATUS) could be on our TODO, but this does not seem very urgent, as it is, to me, no strong annoyance.
[14 Jul 2003 7:28] David Newcomb
Fair enough. It's more of an annoyance than anything else.
http://www.mysql.com/doc/en/CHANGE_MASTER_TO.html says "CHANGE MASTER TO deletes all relay logs and starts a new one,..." which is currently not true as the relay logs will build up in the directory on each change master.
[14 Jul 2003 7:46] Guilhem Bichot
> http://www.mysql.com/doc/en/CHANGE_MASTER_TO.html says "CHANGE MASTER
> TO deletes all relay logs and starts a new one,..." which is currently
> not true as the relay logs will build up in the directory on each
> change master.

Yes, sometimes doing CHANGE MASTER you will end up with 2 4-byte relay logs. This is because we have some redundant functions in code (functions which handle relay logs, which we call twice at different places; not very efficient). But the first 4-byte relay log will be deleted as soon as you do START SLAVE.
Still, I checked our code and didn't find any possibility for more than 2 4-bytes relays logs when calling CHANGE MASTER. So not more than two relay logs *should* build up.