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!
Comments (9)
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(idint(11) NOT NULL AUTO_INCREMENT,namevarchar(20) DEFAULT NULL,pizzasint(11) DEFAULT NULL,beersint(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(aint(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.
@Fernando: Works perfectly. Thanks!!
@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.