MySQL Utilities: The New .frm Reader Utility

Have you ever wondered what was in those .frm files littered throughout your data directory? Better still, have you encountered a situation where your data is either missing (was deleted) or damaged and all you have is the .frm files but don't know the structure of the table? Well, wonder no more!

The MySQL Utilities Team is pleased to announce the newest utility - the .frm reader (mysqlfrm). This utility is designed to read .frm files and produce a facsimile of the CREATE statement for the table or view.

That's Impossible! How Can That Work?

It works by making a copy of the .frm file(s) and launching a new, read-only instance of your existing server. The server need not be running but you are required to provide an open port for the new instance with the --port option.

The utility will launch the cloned server without reading your configuration file (--no-defaults). The utility also makes some minor modifications to the cloned server instance configuration to allow reading of the .frm file without data. Yes, that's right - you don't need your data files to use this utility!

Using a new cloned instance and copying the .frm files means your original server is not altered in any way. The .frm reader also cleansup after itself by removing all temporary files and shutting down the cloned server.

Two Modes for Reading .frm Files

The .frm reader has two modes of operation. The default is intended for use in the normal process of discovering the CREATE statement in a .frm file. There is also a diagnostic mode for cases where the .frm file contains complex table settings or is damaged in some way.

Default Mode

The default mode, as described above, reads most .frm files and produces the CREATE statement for each. In this case, you need provide only the connection to the server via the --server option or the path to the server installation (for a downed server) with the --basedir option. You also need to provide a port with the --port option and, of course, a list of the .frm files you want to read or a list of directories to scan for .frm files.

If you do not want to (or cannot) use an existing server to clone, you can use the --diagnostic mode instead.

Diagnostic Mode

The diagnostic mode reads the .frm files byte-by-byte and makes a best effort to read the data in the file. We say best effort because there are many nuances to the .frm file that have been introduced over the years. Suffice to say that without the servers code to assist, deciphering the data is non-trivial.

We built the diagnostic mode as a feature to make it possible to get something useful from the files in the event the file is damaged or unreadable by the server or has complex table settings that the default mode cannot read or gives an error while reading. Thus, the CREATE statements produced in this mode may not be completely accurate and may be missing some parts. To get the most out of the diagnostic mode, provide a server connection to allow the reading of the character set information. This will improve the accuracy of column definitions.

While this may sound like the diagnostic mode isn't as useful, remember that it is designed to be a tool for diagnosing problems (hence the name) rather than a duplication of the server code. If you think about it in that light, the diagnostic mode is a very important tool that you may need in certain situations where no other tool will work.

While its accuracy is limited today, we plan to improve the feature in the future.

What's the Catch?

If you're thinking this is too good to be true, you're right - there are some limitations. Fortunately, these limitations are, er limited, for the default mode.

The default mode currently cannot handle storage engines marked as PARTITION and PERFORMANCE_SCHEMA. Also, some elements of the table structure are not stored in the .frm file so these are not included in the CREATE statement. Again, fortunately this is a short list that currently includes foreign keys and autoincrement values.

If you find the utility reporting it cannot read a .frm file in the default mode, try it again with the diagnostic mode.

Is That It?

The utility has a few features to help make it more versatile. For example, you can see the statistics for each file (dates, size, etc.) using the --show-stats option and you can substitute a new storage engine to be printed in the CREATE statement with the --new-storage-engine option (applies to all files read for each run of the utility).

There is also a debug mode that prints more information. When used with the diagnostic mode, and you can see the actual values read from the file.

Skip the Hype and Show Me How it Works!

Suppose you find yourself in a situation where your server has gone wonky in such a way as to make your data inaccessible. Suppose that you do have access to your .frm files but no record of the latest changes to the schema. Now suppose you have a backup of the raw data. How do you know if the .frm files in your existing server match those in the backup? Simple: just run the .frm reader on your existing server .frm files then compare the results to your backup.

But wait, what if the data directory is protected (your datadir is protected, isn't it)? Well, the .frm reader provides the --user option to allow you to launch the utility with elevated privileges to read the .frm files but execute the cloned server with a different user account.

Observe the command:

$ sudo env PYTHONPATH=$PYTHONPATH mysqlfrm --server=root:pass@localhost --port=3310 --user=mysql /usr/local/mysql/data/employees
In this command, we clone the server with the mysql user account and tell the .frm reader to read all of the .frm files for the employees database.

Here's the output.

# Source on localhost: ... connected.
# Starting the spawned server on port 3310 ... done.
# Reading .frm files
#
# Reading the departments.frm file.
#
# CREATE statement for /usr/local/mysql/data/employees/departments.frm:
#

CREATE TABLE `employees`.`departments` (
  `dept_no` char(4) NOT NULL,
  `dept_name` varchar(40) NOT NULL,
  PRIMARY KEY (`dept_no`),
  UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

#
# Reading the dept_emp.frm file.
#
# CREATE statement for /usr/local/mysql/data/employees/dept_emp.frm:
#

CREATE TABLE `employees`.`dept_emp` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`),
  KEY `emp_no` (`emp_no`),
  KEY `dept_no` (`dept_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

#
# Reading the dept_manager.frm file.
#
# CREATE statement for /usr/local/mysql/data/employees/dept_manager.frm:
#

CREATE TABLE `employees`.`dept_manager` (
  `dept_no` char(4) NOT NULL,
  `emp_no` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`),
  KEY `emp_no` (`emp_no`),
  KEY `dept_no` (`dept_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

#
# Reading the employees.frm file.
#
# CREATE statement for /usr/local/mysql/data/employees/employees.frm:
#

CREATE TABLE `employees`.`employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

#
# Reading the salaries.frm file.
#
# CREATE statement for /usr/local/mysql/data/employees/salaries.frm:
#

CREATE TABLE `employees`.`salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`),
  KEY `emp_no` (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

#
# Reading the titles.frm file.
#
# CREATE statement for /usr/local/mysql/data/employees/titles.frm:
#

CREATE TABLE `employees`.`titles` (
  `emp_no` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`title`,`from_date`),
  KEY `emp_no` (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

#
# Reading the view1.frm file.
#
# CREATE statement for /usr/local/mysql/data/employees/view1.frm:
#

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `employees_temp`.`view1` AS select `employees`.`t1`.`a` AS `a` from `employees`.`t1`

#...done.

Is that cool or what? Notice it also reads .frm files for views. Very cool.

What About Diagnostic Mode?

Let's take one of the tables in the employees database and run it with the diagnostic mode. For fun, lets change the storage engine and show the file statistics. We specify the server (this is optional) so that if there are multiple byte character sets the diagnostic mode will correctly translate the field lengths. For example, if your table has a 3-byte character set and you do not provide a server connection, there is no way to know that a value of 30 read from the file is actually a field length of size 10 thus char(10) would appear char(30) without the server connection.

Observe the command and output:

$ sudo env PYTHONPATH=$PYTHONPATH mysqlfrm --server=root:pass@localhost --diagnostic --show-stats --new-storage-engine=MEMORY /usr/local/mysql/data/employees/titles.frm
# Source on localhost: ... connected.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for /usr/local/mysql/data/employees/titles.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `employees`.`titles` (
  `emp_no` int(11) NOT NULL,
  `title` varchar(50) COLLATE `latin1_swedish_ci` NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
PRIMARY KEY `PRIMARY` (`emp_no`,`title`,`from_date`),
KEY `emp_no` (`emp_no`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1;

# File Statistics:
#         Last Modified : Thu Jan 26 14:23:14 2012
#         Creation Time : Wed Jan  4 12:15:21 2012
#         Last Accessed : Fri Mar 22 15:18:33 2013
#                  Mode : 33200
#                  Size : 8672

# Table Statistics:
#                Engine : INNODB
#           frm Version : 10
#         MySQL Version : 5.1.50
#      frm File_Version : 5
#               IO_SIZE : 4096
#  Def Partition Engine : None

#...done.

Interesting, eh? If the disclaimer is scary, it is intended to be a reminder that the output may not be as accurate as the default mode. In this case, we see some very minor differences but none of which are showstoppers nor do they demure the usefulness of the output. Lastly, notice we did indeed change the storage engine.

Notice also the file stats show the modification and creation date as well as the original storage engine and version of the server when the .frm file was created.

Ok, I'm Hooked! Where Can I Get It?

The .frm reader utility is part of the new release-1.3.0 Alpha available as a separate download at http://dev.mysql.com/downloads/tools/utilities/. Simply choose the platform repository or source repository and download it.

We welcome your comments and hope that this utility will help expand your diagnosis and recovery toolkit.

Postlude 

Many thanks to Giuseppe Maxia for creating the surprisingly useful employees test database! You can download it from: https://launchpad.net/test-db/