MySQL Bugs: #18199: PURGE BINARY LOGS fails silently with missing logs

Bug #18199	PURGE BINARY LOGS fails silently with missing logs
Submitted:	13 Mar 2006 21:01	Modified:	5 Apr 2008 16:24
Reporter:	Kolbe Kegel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	Linux (Linux)
Assigned to:	Andrei Elkin	CPU Architecture:	Any

Description:
PURGE BINARY LOGS with a BEFORE clause neglects to remove any logs if some logs have been manually removed from the filesystem but not the binary log index. No warning or error message is issued.

How to repeat:
- Enable binary logging
- Execute FLUSH LOGS numerous times over a period of several minutes
- Remove the first binary log file from the filesystem without touching the index
- Execute FLUSH BINARY LOGS BEFORE from the MySQL client and provide a date that includes some but not all of the remaining binary logs.
- The statement appears to finish successfully but has not actually removed any logs
- This behavior could allow binary logs to pile up and consume disk space, even though the statement seems to be doing what it is supposed to

kolbe@beluga:~/MySQL/inst/5.0.18> ls -l data/beluga-bin.*
-rw-rw----  1 kolbe users 497 2006-03-13 10:50 data/beluga-bin.000010
-rw-rw----  1 kolbe users 142 2006-03-13 10:58 data/beluga-bin.000011
-rw-rw----  1 kolbe users 142 2006-03-13 12:05 data/beluga-bin.000012
-rw-rw----  1 kolbe users 174 2006-03-13 12:05 data/beluga-bin.000013
-rw-rw----  1 kolbe users 100 2006-03-13 12:05 data/beluga-bin.index

kolbe@beluga:~/MySQL/inst/5.0.18> cat data/beluga-bin.index
./beluga-bin.000009
./beluga-bin.000010
./beluga-bin.000011
./beluga-bin.000012
./beluga-bin.000013

mysql 5.0.18-max-log (root) [test]> purge binary logs before '2006-03-13 12:05:00';
Query OK, 0 rows affected (0.01 sec)

kolbe@beluga:~/MySQL/inst/5.0.18> ls -l data/beluga-bin.*
-rw-rw----  1 kolbe users 497 2006-03-13 10:50 data/beluga-bin.000010
-rw-rw----  1 kolbe users 142 2006-03-13 10:58 data/beluga-bin.000011
-rw-rw----  1 kolbe users 142 2006-03-13 12:05 data/beluga-bin.000012
-rw-rw----  1 kolbe users 174 2006-03-13 12:05 data/beluga-bin.000013
-rw-rw----  1 kolbe users 100 2006-03-13 12:35 data/beluga-bin.index

Suggested fix:
An error message should be issued if the operation cannot be completed for any reason.

This seems like a pretty annoying problem... makes it difficult to use this facility reliably.

Another reason to get rid of binlogs, people playing around with them by hand.

It would be nice if it would remove files from the index if they have been deleted from the filesystem, and keep processing through the file.  I might not see an error message from my cron job to be able to fix things in time (my app generates several gigs of log per day).

Note that PURGE BINARY LOGS TO 'filename' does work if there are missing files on the filesystem.  

So it can be used to 'sync' the .index file back to whatever is on the disk. There-after the option binlog_expire_days? and FLUSH LOGS and PURGE LOGS BEFORE 'date' should work again.

Maybe not only an error message but also a short explanation how to rebuild manually the index-file - or a switch so mysql will itself re-create the index-file?

Bug #18453 will be fixed with the patch for the current bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43946

ChangeSet@1.2598, 2008-03-13 19:33:43+02:00, aelkin@mysql1000.(none) +2 -0
  Bug #18199  PURGE BINARY LOGS fails silently with missing logs;
  Bug #18453  Warning/error message if there is a mismatch between ...
  
  There were three problems:
  
  1. the reported lack of warnings for the BEFORE syntax of PURGE;
  2. the similar lack of warnings for the TO syntax;
  3. incompatible behaviour between the two in that the latter blanked out
     regardlessly of presence or lack the actual file corresponding to
     an index record; the former version gave up at the first mismatch.
  
  fixed with polishing and synronizing logics of purge_logs() and
  purge_logs_before_date().

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43947

ChangeSet@1.2598, 2008-03-13 19:40:41+02:00, aelkin@mysql1000.(none) +4 -0
  Bug #18199  PURGE BINARY LOGS fails silently with missing logs;
  Bug #18453  Warning/error message if there is a mismatch between ...
  
  There were three problems:
  
  1. the reported lack of warnings for the BEFORE syntax of PURGE;
  2. the similar lack of warnings for the TO syntax;
  3. incompatible behaviour between the two in that the latter blanked out
     regardlessly of presence or lack the actual file corresponding to
     an index record; the former version gave up at the first mismatch.
  
  fixed with polishing and synronizing logics of purge_logs() and
  purge_logs_before_date().

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44017

ChangeSet@1.2598, 2008-03-14 19:31:07+02:00, aelkin@mysql1000.(none) +5 -0
  Bug #18199  PURGE BINARY LOGS fails silently with missing logs;
  Bug #18453  Warning/error message if there is a mismatch between ...
   
  There were three problems:
   
   1. the reported lack of warnings for the BEFORE syntax of PURGE;
   2. the similar lack of warnings for the TO syntax;
   3. incompatible behaviour between the two in that the latter blanked out
      regardlessly of presence or lack the actual file corresponding to
      an index record; the former version gave up at the first mismatch.
  
  fixed with deploying the warning's generation and synronizing logics of 
  purge_logs() and purge_logs_before_date().

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44026

ChangeSet@1.2598, 2008-03-14 19:58:59+02:00, aelkin@mysql1000.(none) +5 -0
  Bug #18199  PURGE BINARY LOGS fails silently with missing logs;
  Bug #18453  Warning/error message if there is a mismatch between ...
   
  There were three problems:
   
   1. the reported lack of warnings for the BEFORE syntax of PURGE;
   2. the similar lack of warnings for the TO syntax;
   3. incompatible behaviour between the two in that the latter blanked out
      regardlessly of presence or lack the actual file corresponding to
      an index record; the former version gave up at the first mismatch.
  
  fixed with deploying the warning's generation and synronizing logics of 
  purge_logs() and purge_logs_before_date().

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44036

ChangeSet@1.2598, 2008-03-14 21:24:28+02:00, aelkin@mysql1000.(none) +5 -0
  Bug #18199  PURGE BINARY LOGS fails silently with missing logs;
  Bug #18453  Warning/error message if there is a mismatch between ...
   
  There were three problems:
   
   1. the reported lack of warnings for the BEFORE syntax of PURGE;
   2. the similar lack of warnings for the TO syntax;
   3. incompatible behaviour between the two in that the latter blanked out
      regardlessly of presence or lack the actual file corresponding to
      an index record; the former version gave up at the first mismatch.
  
  fixed with deploying the warning's generation and synronizing logics of 
  purge_logs() and purge_logs_before_date().
  my_stat() is called in either of two branches of purge_logs() (responsible
  for the TO syntax of PURGE) similarly to how it has behaved in the BEFORE syntax.
  If there is no actual binlog file, my_stat returns NULL and my_delete is
  not invoked.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44074

ChangeSet@1.2598, 2008-03-15 17:55:40+02:00, aelkin@mysql1000.(none) +5 -0
  Bug #18199  PURGE BINARY LOGS fails silently with missing logs;
  Bug #18453  Warning/error message if there is a mismatch between ...
   
  There were three problems:
   
   1. the reported lack of warnings for the BEFORE syntax of PURGE;
   2. the similar lack of warnings for the TO syntax;
   3. incompatible behaviour between the two in that the latter blanked out
      regardlessly of presence or lack the actual file corresponding to
      an index record; the former version gave up at the first mismatch.
  
  fixed with deploying the warning's generation and synronizing logics of 
  purge_logs() and purge_logs_before_date().
  my_stat() is called in either of two branches of purge_logs() (responsible
  for the TO syntax of PURGE) similarly to how it has behaved in the BEFORE syntax.
  If there is no actual binlog file, my_stat returns NULL and my_delete is
  not invoked.
  A critical error is reported to the user if a file from the index
  could not be retrieved info about or deleted with a system error code
  different than ENOENT.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44076

ChangeSet@1.2598, 2008-03-15 17:58:42+02:00, aelkin@mysql1000.(none) +5 -0
  Bug #18199  PURGE BINARY LOGS fails silently with missing logs;
  Bug #18453  Warning/error message if there is a mismatch between ...
   
  There were three problems:
   
   1. the reported lack of warnings for the BEFORE syntax of PURGE;
   2. the similar lack of warnings for the TO syntax;
   3. incompatible behaviour between the two in that the latter blanked out
      regardlessly of presence or lack the actual file corresponding to
      an index record; the former version gave up at the first mismatch.
  
  fixed with deploying the warning's generation and synronizing logics of 
  purge_logs() and purge_logs_before_date().
  my_stat() is called in either of two branches of purge_logs() (responsible
  for the TO syntax of PURGE) similarly to how it has behaved in the BEFORE syntax.
  If there is no actual binlog file, my_stat returns NULL and my_delete is
  not invoked.
  A critical error is reported to the user if a file from the index
  could not be retrieved info about or deleted with a system error code
  different than ENOENT.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44118

ChangeSet@1.2598, 2008-03-17 17:55:55+02:00, aelkin@mysql1000.(none) +5 -0
  Bug #18199  PURGE BINARY LOGS fails silently with missing logs;
  Bug #18453  Warning/error message if there is a mismatch between ...
   
  There were three problems:
   
   1. the reported lack of warnings for the BEFORE syntax of PURGE;
   2. the similar lack of warnings for the TO syntax;
   3. incompatible behaviour between the two in that the latter blanked out
      regardlessly of presence or lack the actual file corresponding to
      an index record; the former version gave up at the first mismatch.
  
  fixed with deploying the warning's generation and synronizing logics of 
  purge_logs() and purge_logs_before_date().
  my_stat() is called in either of two branches of purge_logs() (responsible
  for the TO syntax of PURGE) similarly to how it has behaved in the BEFORE syntax.
  If there is no actual binlog file, my_stat returns NULL and my_delete is
  not invoked.
  A critical error is reported to the user if a file from the index
  could not be retrieved info about or deleted with a system error code
  different than ENOENT.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44119

ChangeSet@1.2598, 2008-03-17 17:59:25+02:00, aelkin@mysql1000.(none) +5 -0
  Bug #18199  PURGE BINARY LOGS fails silently with missing logs;
  Bug #18453  Warning/error message if there is a mismatch between ...
   
  There were three problems:
   
   1. the reported lack of warnings for the BEFORE syntax of PURGE;
   2. the similar lack of warnings for the TO syntax;
   3. incompatible behaviour between the two in that the latter blanked out
      regardlessly of presence or lack the actual file corresponding to
      an index record; the former version gave up at the first mismatch.
  
  fixed with deploying the warning's generation and synronizing logics of 
  purge_logs() and purge_logs_before_date().
  my_stat() is called in either of two branches of purge_logs() (responsible
  for the TO syntax of PURGE) similarly to how it has behaved in the BEFORE syntax.
  If there is no actual binlog file, my_stat returns NULL and my_delete is
  not invoked.
  A critical error is reported to the user if a file from the index
  could not be retrieved info about or deleted with a system error code
  different than ENOENT.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44122

ChangeSet@1.2598, 2008-03-17 18:03:47+02:00, aelkin@mysql1000.(none) +5 -0
  Bug #18199  PURGE BINARY LOGS fails silently with missing logs;
  Bug #18453  Warning/error message if there is a mismatch between ...
   
  There were three problems:
   
   1. the reported lack of warnings for the BEFORE syntax of PURGE;
   2. the similar lack of warnings for the TO syntax;
   3. incompatible behaviour between the two in that the latter blanked out
      regardlessly of presence or lack the actual file corresponding to
      an index record; the former version gave up at the first mismatch.
  
  fixed with deploying the warning's generation and synronizing logics of 
  purge_logs() and purge_logs_before_date().
  my_stat() is called in either of two branches of purge_logs() (responsible
  for the TO syntax of PURGE) similarly to how it has behaved in the BEFORE syntax.
  If there is no actual binlog file, my_stat returns NULL and my_delete is
  not invoked.
  A critical error is reported to the user if a file from the index
  could not be retrieved info about or deleted with a system error code
  different than ENOENT.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44131

ChangeSet@1.2598, 2008-03-17 19:17:39+02:00, aelkin@mysql1000.(none) +5 -0
  Bug #18199  PURGE BINARY LOGS fails silently with missing logs;
  Bug #18453  Warning/error message if there is a mismatch between ...
   
  There were three problems:
   
   1. the reported lack of warnings for the BEFORE syntax of PURGE;
   2. the similar lack of warnings for the TO syntax;
   3. incompatible behaviour between the two in that the latter blanked out
      regardlessly of presence or lack the actual file corresponding to
      an index record; the former version gave up at the first mismatch.
  
  fixed with deploying the warning's generation and synronizing logics of 
  purge_logs() and purge_logs_before_date().
  my_stat() is called in either of two branches of purge_logs() (responsible
  for the TO syntax of PURGE) similarly to how it has behaved in the BEFORE syntax.
  If there is no actual binlog file, my_stat returns NULL and my_delete is
  not invoked.
  A critical error is reported to the user if a file from the index
  could not be retrieved info about or deleted with a system error code
  different than ENOENT.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44133

ChangeSet@1.2598, 2008-03-17 19:25:38+02:00, aelkin@mysql1000.(none) +5 -0
  Bug #18199  PURGE BINARY LOGS fails silently with missing logs;
  Bug #18453  Warning/error message if there is a mismatch between ...
   
  There were three problems:
   
   1. the reported lack of warnings for the BEFORE syntax of PURGE;
   2. the similar lack of warnings for the TO syntax;
   3. incompatible behaviour between the two in that the latter blanked out
      regardlessly of presence or lack the actual file corresponding to
      an index record; the former version gave up at the first mismatch.
  
  fixed with deploying the warning's generation and synronizing logics of 
  purge_logs() and purge_logs_before_date().
  my_stat() is called in either of two branches of purge_logs() (responsible
  for the TO syntax of PURGE) similarly to how it has behaved in the BEFORE syntax.
  If there is no actual binlog file, my_stat returns NULL and my_delete is
  not invoked.
  A critical error is reported to the user if a file from the index
  could not be retrieved info about or deleted with a system error code
  different than ENOENT.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44137

ChangeSet@1.2598, 2008-03-17 20:19:04+02:00, aelkin@mysql1000.(none) +5 -0
  Bug #18199  PURGE BINARY LOGS fails silently with missing logs;
  Bug #18453  Warning/error message if there is a mismatch between ...
   
  There were three problems:
   
   1. the reported lack of warnings for the BEFORE syntax of PURGE;
   2. the similar lack of warnings for the TO syntax;
   3. incompatible behaviour between the two in that the latter blanked out
      regardlessly of presence or lack the actual file corresponding to
      an index record; the former version gave up at the first mismatch.
  
  fixed with deploying the warning's generation and synronizing logics of 
  purge_logs() and purge_logs_before_date().
  my_stat() is called in either of two branches of purge_logs() (responsible
  for the TO syntax of PURGE) similarly to how it has behaved in the BEFORE syntax.
  If there is no actual binlog file, my_stat returns NULL and my_delete is
  not invoked.
  A critical error is reported to the user if a file from the index
  could not be retrieved info about or deleted with a system error code
  different than ENOENT.

Pushed to 5.0-rpl.

Pushed into 5.1.24-rc

Pushed into 5.0.60

Pushed into 6.0.5-alpha

Documented this fix in the 5.0.60, 5.1.24, and 6.0.5 changelogs as follows:

        PURGE BINARY LOGS TO and PURGE BINARY LOGS BEFORE did not handle missing
        binary log files either correctly or in the same way. Now for both of         
        these statements, if any files listed in the .index file are missing from 
        the filesystem, the statement fails with an error.

Also added a note to purge-master-logs about how to handle such situations.