Monday, June 2, 2014

MySQL 5.5 Maximum Sequence in Binary Logs

MySQL Binary Logs use a sequence number as the extension.

If you have binary logging turned on, the first binary log file will be mysql-bin.000001

What happens if you flush logs after mysql-bin.999999?

We could fire up the source code to take a look, or we could try it out.

I renamed the last binary log file to mysql-bin.999997 and changed the mysql-bin.index file to only list the one.    After a couple of flush logs we get the following.


This not like the odometer on your car. 

The filename is generated in sql/log.c  and it will throw an error when

if (max_found == MAX_LOG_UNIQUE_FN_EXT)

where  MAX_LOG_UNIQUE_FN_EXT is 0x7FFFFFFF or  2147483647, roughly 2.1 billion flushes. 

Let's try it out.


The error message definitely needs to be updated.  But the server didn't crash.  And that's a good thing.

The error log produced the following.
 140602 20:27:52 [ERROR] Log filename extension number exhausted: 2147483647. Please fix this by archiving old logs and updating the index files.
140602 20:27:52 [ERROR] Can't generate a unique log-filename mysqlbin.(1-999)


Conclusion
1.  There is a maximum sequence number in binary logs, and that is 2147483647.
2.  There's an error message update in the source code that could be more useful.
3.  Monitor your error logs!
4.  If mysql-bin.000001 doesn't exist, it seems to me that it's okay to roll over the odometer just like a good car.