EmergencyEMERGENCY? Get 24/7 Help Now!

How to obtain the MySQL version from an FRM file

 | July 9, 2015 |  Posted In: Insight for DBAs, MySQL, MySQL 101

PREVIOUS POST
NEXT POST

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!

PREVIOUS POST
NEXT POST
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.

9 Comments

  • 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

  • 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,

  • 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

  • @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 |
    +—————–+

  • @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!

  • mysqlfrm –diagnostic -s t.frm

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

  • 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.

Leave a Reply