Bug #82154 binlog file counter resets when files aren't in place (compressed or deleted)
Submitted: 7 Jul 2016 21:04 Modified: 8 Jul 2016 6:35
Reporter: Mike Benshoof Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.6, 5.7, 5.7.13 OS:Any
Assigned to: CPU Architecture:Any

[7 Jul 2016 21:04] Mike Benshoof
When the current binlog file was accidentally compressed, the server continues to take writes and advance the position even though nothing is being written to the binlog.

Also, when there is a FLUSH LOGS (or max_binlog_size is exceeded) and the files aren't readable, the filename counter reverts back to the the highest readable file in mysql-bin.index + 1 or all the way back to .000001 if none of the files are readable.

Also, there were no entries in the error log notifying that logs in the index file weren't found or that the current log wasn't being written to.

Granted, the proper way to remove binlogs is to PURGE BINARY LOGS, but in the event of an errant cleanup process or other mistake, it would seem reasonable to use the max counter number in the mysql-bin.index file as opposed to working backwards.

How to repeat:
1. Enable binlog and perform some writes


log-bin = mysql-bin
server-id = 1
sync-binlog = 0 (or 1)

mysql > use test
mysql > create table t1 (id int);
mysql > insert into t1 values (1), (2);

# Check to see the current file (lets assume fresh install .00001)
mysql > show master status; 

2. Run FLUSH LOGS to advance to the next binlog

mysql > flush logs;

# Confirm moved to next file (assume .00002)
mysql > show master status; 

3. Perform additional writes and confirm binlog is moving

mysql > insert into t1 values (1), (2);
mysql > insert into t1 values (1), (2);
mysql > show master status; 

4. Compress all binlogs manually without purge

cd /path/to/binlogs
gzip mysql-bin.0* (or something like that)
cat mysql-bin.index (verify that there are 2 binlogs)

5. Continue to perform writes

mysql > insert into t1 values (1), (2);
mysql > insert into t1 values (1), (2);
mysql > show master status; 


mysql > flush logs;

# The counter should now go back to .000001
mysql > show master status; 

# Should have .000001, .000002, .000001 (in that order)
mysql > show binary logs;

Suggested fix:
Take the highest binlog suffix from mysql-bin.index and increment, regardless of the file location on the disk.

This should make any potential recovery mechanism easier as the files would still be sequential and not potentially overwritten.

Also, report an error or notice to the error log noting that a file in mysql-bin.index wasn't readable.
[8 Jul 2016 0:47] zhai weixiang
While rotating to next log file, mysql will get the next number by scanning  the dir where binlogs are stored which is a inefficient action. (ref bug#70428)
[8 Jul 2016 6:35] MySQL Verification Team
Hello Mike Benshoof,

Thank you for the report and feedback!

[8 Jul 2016 6:36] MySQL Verification Team
Related Bug #70428