Bug #1594 zero length binlog file is not removed with PURGE MASTER LOGS
Submitted: 18 Oct 2003 6:33 Modified: 18 Oct 2003 7:20
Reporter: [ name withheld ] Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0, 3.23 OS:
Assigned to: CPU Architecture:Any

[18 Oct 2003 6:33] [ name withheld ]
Description:
Due to a upgrade issue at some point in the past, I have a 0 length binlog file. Doing a 'PURGE MASTER LOGS TO <some log after the zero length file>' results in all of the logs surrounding it being removed, but the zero length file isn't cleaned up. While I realize this is an extremely minor issue and I can remove the file by hand, it should probably be handled better by the server. This is an issue in both 3.23 and 4.0

How to repeat:
57 [porter][9:26am][data]# ls -l
total 335704
-rw-rw----    1 mysql    mysql       25088 Oct 18 08:17 ib_arch_log_0000000000
-rw-rw----    1 mysql    mysql    10485760 Oct 18 08:17 ibdata1
-rw-rw----    1 mysql    mysql     5242880 Oct 18 08:18 ib_logfile0
-rw-rw----    1 mysql    mysql     5242880 Oct 18 08:17 ib_logfile1
-rw-rw----    1 mysql    mysql          60 Oct 18 09:26 master.info
-rw-r--r--    1 mysql    mysql        3191 May 24  2002 my.cnf
drwx--x--x    5 mysql    mysql        4096 Oct 18 08:19 mysql/
lrwxrwxrwx    1 mysql    mysql          15 Feb 25  2003 mysql.sock -> /tmp/mysql
.sock=
-rw-rw----    1 mysql    mysql           0 Feb 24  2003 porter-bin.009
-rw-rw----    1 mysql    mysql    308937161 Oct 18 08:17 porter-bin.014
-rw-rw----    1 mysql    mysql          79 Oct 18 08:17 porter-bin.015
-rw-rw----    1 mysql    mysql        2561 Oct 18 08:17 porter-bin.016
-rw-rw----    1 mysql    mysql      150161 Oct 18 09:26 porter-bin.017
-rw-rw----    1 mysql    mysql          68 Oct 18 08:18 porter-bin.index
-rw-r-----    1 mysql    mysql       92331 Oct 18 08:19 porter.err
-rw-rw----    1 mysql    mysql           5 Oct 18 08:18 porter.pid
-rw-rw----    1 mysql    mysql           0 Oct 18 08:17 porter-relay-bin.002
-rw-rw----    1 mysql    mysql    13133646 Oct 18 09:26 porter-relay-bin.004
-rw-rw----    1 mysql    mysql          23 Oct 18 08:18 porter-relay-bin.index
-rw-rw----    1 mysql    mysql          57 Oct 18 09:26 relay-log.info
58 [porter][9:26am][data]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1096 to server version: 4.0.15-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> purge master logs to 'porter-bin.017';
Query OK, 0 rows affected (0.43 sec)

mysql> exit
Bye
59 [porter][9:26am][data]# ls -l
total 33696
-rw-rw----    1 mysql    mysql       25088 Oct 18 08:17 ib_arch_log_0000000000
-rw-rw----    1 mysql    mysql    10485760 Oct 18 08:17 ibdata1
-rw-rw----    1 mysql    mysql     5242880 Oct 18 08:18 ib_logfile0
-rw-rw----    1 mysql    mysql     5242880 Oct 18 08:17 ib_logfile1
-rw-rw----    1 mysql    mysql          60 Oct 18 09:26 master.info
-rw-r--r--    1 mysql    mysql        3191 May 24  2002 my.cnf
drwx--x--x    5 mysql    mysql        4096 Oct 18 08:19 mysql/
lrwxrwxrwx    1 mysql    mysql          15 Feb 25  2003 mysql.sock -> /tmp/mysql
.sock=
-rw-rw----    1 mysql    mysql           0 Feb 24  2003 porter-bin.009
-rw-rw----    1 mysql    mysql      150161 Oct 18 09:26 porter-bin.017
-rw-rw----    1 mysql    mysql          17 Oct 18 09:26 porter-bin.index
-rw-r-----    1 mysql    mysql       92331 Oct 18 08:19 porter.err
-rw-rw----    1 mysql    mysql           5 Oct 18 08:18 porter.pid
-rw-rw----    1 mysql    mysql           0 Oct 18 08:17 porter-relay-bin.002
-rw-rw----    1 mysql    mysql    13133646 Oct 18 09:26 porter-relay-bin.004
-rw-rw----    1 mysql    mysql          23 Oct 18 08:18 porter-relay-bin.index
-rw-rw----    1 mysql    mysql          57 Oct 18 09:26 relay-log.info

Suggested fix:
PURGE MASTER LOGS should remove 0 length log files as part of it's cleanup
[18 Oct 2003 6:34] [ name withheld ]
I forgot to add that this has been experienced on various versions of RedHat linux although I'd suspect that it's OS independent
[18 Oct 2003 7:20] Guilhem Bichot
Hi!
The problem is not that the file is of length 0.
First, a binlog is always at least 4 bytes, so if you have a 0-byte one then something strange happened. Yes, that was during an upgrade; it could be that MySQL was killed before it had time to write the 4 first bytes, or there was some manual operations on the binlogs, I don't know. Anyway, the reason why MySQL does not delete the binlog is that this binlog is not listed in your .index file: look, you .index file is 68 bytes before you do the PURGE, which is 17*4, and 17 is the length of the name of one binlog. So before PURGE, the .index file probably contains
porter-bin.014
porter-bin.015
porter-bin.016
porter-bin.017
and that's all, not porter-bin.009.
PURGE MASTER LOGS works like this:
 open the .index file
 while (true) 
    {
     read one line of the .index file
     if equals to the argument of PURGE MASTER LOGS, break;
     else delete the file
    }
 rebuild the .index file accordingly.
So as .009 was not in the index file (which was caused by the strange things at upgrade time probably), it is not deleted.
I tested on our machines: a zero-length binlog is deleted, if it is in the .index file.
So that's not a bug. But your report was very clear, thank you!

Guilhem