How to obtain the MySQL version from an FRM file

I recently helped a customer figure out why a minor version MySQL upgrade was indicating that some tables needed to be rebuilt. The mysql_upgrade program should be run for every upgrade, no matter how big or small the version difference is, but when only the minor version changes, I would normally not expect it to require tables to be rebuilt.

Turns out some of their tables were still marked with an older MySQL version, which could mean a few things… most likely that something went wrong with a previous upgrade, or that the tables were copied from a server with an older version.

In cases like this, did you know there is a fast, safe and simple way to check the version associated with a table? You can do this by reading the FRM file, following the format specification found here.

If you look at that page, you’ll see that the version is 4 bytes long and starts at offset 0x33. Since it is stored in little endian format, you can get the version just by reading the first two bytes.

This means you can use hexdump to read 2 bytes, starting at offset 0x33, and getting the decimal representation of them, to obtain the MySQL version, like so:


telecaster:test fernandoipar$ hexdump -s 0x33 -n 2 -v -d 55_test.frm
0000033 50532
0000035
telecaster:test fernandoipar$ hexdump -s 0x33 -n 2 -v -d 51_test.frm
0000033 50173
0000035


The first example corresponds to a table created on MySQL version 5.5.32, while the second one corresponds to 5.1.73.

Does that mean the 51_test table was created on 5.1.73? Not necessarily, as MySQL will update the version on the FRM whenever the table is rebuilt or altered.

The manual page says the details can change with the transition to the new text based format, but I was able to get the version using this command up to version MySQL 5.7.7.

Hope you found that useful!

Fernando Ipar

Fernando is part of Percona's team working as Senior Consultant. Prior to joining Percona, Fernando worked as a consultant for financial services institutions, telcos, and technology providers.