How to obtain the MySQL version from an FRM file

09 Jul 2015

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!

Author

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.

Comments (9)

Peter (Stig) Edwards Reply

Also see these tools for reading .frm files:

The .frm reader utility, in diagnostic mode:
http://drcharlesbell.blogspot.co.uk/2013/04/mysql-utilities-new-frm-reader-utility.html
http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqlfrm.html
https://www.percona.com/blog/2014/01/02/recover-table-structure-frm-files-mysql-utilities/

frmdump.php and frmdump.pl:
https://blogs.oracle.com/thava/entry/dump_mysql_frm_file_header

frmdump from dbsake
https://github.com/abg/dbsake/blob/master/docs/commands/frmdump.rst

July 9, 2015 at 4:10 am
Frederic Descamps Reply

Hi Fernando,

Nice post, very interesting to know.

Usually I use dbsake (http://docs.dbsake.net/en/latest/readme.html) to find the version (and more) from the frm:

# dbsake frmdump community_dinner.frm
—
— Table structure for table community_dinner
— Created with MySQL Version 5.6.22
—

CREATE TABLE community_dinner (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(20) DEFAULT NULL,
pizzas int(11) DEFAULT NULL,
beers int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Cheers,

July 9, 2015 at 4:22 am
Federico Razzoli Reply

Any way to use this trick with MariaDB 10.0? I get this result:

root@ubu1:/var/lib/mysql/my_db# hexdump -s 0x33 -n 2 -v -d country.frm
0000033 34481
0000035

July 9, 2015 at 5:55 am
Fernando Ipar Reply

@Fred:

Thanks! I was unaware of dbsake

@Federico:

MariaDB 10 is, well, > 10, and so it caught me off guard 🙂
There may be a more straightforward way to do this, but I got the version by getting the bytes in the right order and then converting to hex:

[root@mariadb10 test]# hexdump -s 0x33 -n 4 -v test.frm |awk ‘{print “0x” $3 $2}’|head -1|xargs printf ‘%d\n’
100020

Which matches:

[root@mariadb10 test]# mysql -e ‘select @@version’
+—————–+
| @@version |
+—————–+
| 10.0.20-MariaDB |
+—————–+

July 9, 2015 at 9:01 am
Fernando Ipar Reply

@Peter:

Thanks for the info. I did test mysqlfrm but I think it only generates the corresponding create table statement, without printing the version found on the frm file. The tool from dbsake does both though. With two recommendations on the same day I am wondering how I had missed that utility before!

July 9, 2015 at 9:04 am
Scott Noyes Reply

mysqlfrm –diagnostic -s t.frm

# Table Statistics:
# Engine : INNODB
# frm Version : 9
# MySQL Version : 5.6.23
# frm File_Version : 5

July 9, 2015 at 11:24 am
Chuck Bell Reply

Hello. The mysqlfrm utility does print the version and more when you use the –showstats (-s) option:

mysqlfrm.py –server=… –port=3310 t1.frm -s

# Source on localhost: … connected.
# Starting the spawned server on port 3310 … done.
# Reading .frm files
#
# Reading the t1.frm file.
#
# CREATE statement for ./mysql-test/std_data/frm_files/t1.frm:
#

CREATE TABLE frm_files.t1 (
a int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

# File Statistics:
# Last Modified : Thu Jul 9 11:20:34 2015
# Creation Time : Thu Jul 9 11:20:34 2015
# Last Accessed : Thu Jul 9 11:20:34 2015
# Mode : 33188
# Size : 8554

# Table Statistics:
# Engine : HEAP
# frm Version : 9
# MySQL Version : 5.6.11
# frm File_Version : 5
# IO_SIZE : 4096
# Def Partition Engine : None

#…done.

You can get even more details if you use the verbosity (-vvv) option.

July 9, 2015 at 11:27 am
Federico Razzoli Reply

@Fernando: Works perfectly. Thanks!!

July 9, 2015 at 11:32 am
Fernando Ipar Reply

@Scott, @Chuck:

Thanks! I had not used the –diagnostic options when I tested mysqlfrm, but I can see it works fine to get the version too.

July 9, 2015 at 11:54 am

How to obtain the MySQL version from an FRM file