Downloads

Blog

How to recover table structure from InnoDB dictionary

April 22, 2013

Author

Aleksandr Kuzminsky

Insight for DBAs

MySQL

Share this Post:

To recover a dropped or corrupt table with Percona Data Recovery Tool for InnoDB you need two things: media with records(ibdata1, *.ibd, disk image, etc.) and a table structure. Indeed, there is no information about the table structure in an InnoDB page. Normally we either recover the structure from .frm files or take it from some old backup.

A new tool sys_parser can recover the table structure from InnoDB dictionary.

Why do we need a new tool anyway? It is absolutely critical to have an accurate table definition to ensure a successful recovery. Even an unnoticeable difference like NULL or NOT NULL can shift all values by a byte and thus will spoil the result. That’s why I prefer the structure from .frm files over taken from backups. But in some cases even .frm files is not an option:

Table was dropped and innodb_file_per_table is ON

Frm file corrupt, zeroed out, lost or SHOW CREATE TABLE crashes MySQL

There is yet another source of information about the table structure – InnoDB dictionary. Let’s review tables from the dictionary and see what it can give us. We will need four of them:

SYS_TABLES

SYS_INDEXES

SYS_COLUMNS

SYS_FIELDS

SYS_TABLES
Here InnoDB keeps correspondence between human readable table names and their internal identifiers.

CREATE TABLE `SYS_TABLES` (<br>  `NAME` varchar(255) NOT NULL DEFAULT '',<br>  `ID` bigint(20) unsigned NOT NULL DEFAULT '0',<br>  `N_COLS` int(10) DEFAULT NULL,<br>  `TYPE` int(10) unsigned DEFAULT NULL,<br>  `MIX_ID` bigint(20) unsigned DEFAULT NULL,<br>  `MIX_LEN` int(10) unsigned DEFAULT NULL,<br>  `CLUSTER_NAME` varchar(255) DEFAULT NULL,<br>  `SPACE` int(10) unsigned DEFAULT NULL,<br>  PRIMARY KEY (`NAME`)<br>) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `SYS_TABLES` ( `NAME` varchar(255) NOT NULL DEFAULT '', `ID` bigint(20) unsigned NOT NULL DEFAULT '0', `N_COLS` int(10) DEFAULT NULL, `TYPE` int(10) unsigned DEFAULT NULL, `MIX_ID` bigint(20) unsigned DEFAULT NULL, `MIX_LEN` int(10) unsigned DEFAULT NULL, `CLUSTER_NAME` varchar(255) DEFAULT NULL, `SPACE` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`NAME`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

NAME is a human readable table name in form database_name/table_name e.g. sakila/actor. ID is a table identifier. We will need the table id to find indexes of the table.

mysql&gt; select * from SYS_TABLES WHERE NAME='sakila/actor';<br>+--------------+-----+--------+------+--------+---------+--------------+-------+<br>| NAME         | ID  | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |<br>+--------------+-----+--------+------+--------+---------+--------------+-------+<br>| sakila/actor | 741 |      4 |    1 |      0 |       0 |              |   738 |<br>+--------------+-----+--------+------+--------+---------+--------------+-------+

mysql> select * from SYS_TABLES WHERE NAME='sakila/actor'; +--------------+-----+--------+------+--------+---------+--------------+-------+ | NAME | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE | +--------------+-----+--------+------+--------+---------+--------------+-------+ | sakila/actor | 741 | 4 | 1 | 0 | 0 | | 738 | +--------------+-----+--------+------+--------+---------+--------------+-------+

SYS_INDEXES
This table lists all indexes the table has, secondary as well as the primary.

CREATE TABLE `SYS_INDEXES` (<br>  `TABLE_ID` bigint(20) unsigned NOT NULL DEFAULT '0',<br>  `ID` bigint(20) unsigned NOT NULL DEFAULT '0',<br>  `NAME` varchar(120) DEFAULT NULL,<br>  `N_FIELDS` int(10) unsigned DEFAULT NULL,<br>  `TYPE` int(10) unsigned DEFAULT NULL,<br>  `SPACE` int(10) unsigned DEFAULT NULL,<br>  `PAGE_NO` int(10) unsigned DEFAULT NULL,<br>  PRIMARY KEY (`TABLE_ID`,`ID`)<br>) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `SYS_INDEXES` ( `TABLE_ID` bigint(20) unsigned NOT NULL DEFAULT '0', `ID` bigint(20) unsigned NOT NULL DEFAULT '0', `NAME` varchar(120) DEFAULT NULL, `N_FIELDS` int(10) unsigned DEFAULT NULL, `TYPE` int(10) unsigned DEFAULT NULL, `SPACE` int(10) unsigned DEFAULT NULL, `PAGE_NO` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`TABLE_ID`,`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

So, TABLE_ID is our table_id. ID here is the index identifier. We need it to find InnoDB pages which belong to the table’s index. Which one? A table can have many secondary indexes, but only in the primary index we can find all fields. It must exist for any InnoDB table. If explicitely defined its NAME is PRIMARY. If the primary key is not defined InnoDB will use a unique secondary index as the primary. If there is no any unique index InnoDB will create one implicitely. Its name will be GEN_CLUST_INDEX.
It doesn’t matter how the primary index gets created it will have minimal ID among the indexes of the table.

mysql&gt; select * from SYS_INDEXES WHERE TABLE_ID=741;<br>+----------+------+---------------------+----------+------+-------+---------+<br>| TABLE_ID | ID   | NAME                | N_FIELDS | TYPE | SPACE | PAGE_NO |<br>+----------+------+---------------------+----------+------+-------+---------+<br>|      741 | 1679 | PRIMARY             |        1 |    3 |   738 |       3 |<br>|      741 | 1680 | idx_actor_last_name |        1 |    0 |   738 |       4 |<br>+----------+------+---------------------+----------+------+-------+---------+

mysql> select * from SYS_INDEXES WHERE TABLE_ID=741; +----------+------+---------------------+----------+------+-------+---------+ | TABLE_ID | ID | NAME | N_FIELDS | TYPE | SPACE | PAGE_NO | +----------+------+---------------------+----------+------+-------+---------+ | 741 | 1679 | PRIMARY | 1 | 3 | 738 | 3 | | 741 | 1680 | idx_actor_last_name | 1 | 0 | 738 | 4 | +----------+------+---------------------+----------+------+-------+---------+

SYS_COLUMNS
Table SYS_COLUMNS stores fields names and type information of the table.

CREATE TABLE `SYS_COLUMNS` (<br>  `TABLE_ID` bigint(20) unsigned NOT NULL,<br>  `POS` int(10) unsigned NOT NULL,<br>  `NAME` varchar(255) DEFAULT NULL,<br>  `MTYPE` int(10) unsigned DEFAULT NULL,<br>  `PRTYPE` int(10) unsigned DEFAULT NULL,<br>  `LEN` int(10) unsigned DEFAULT NULL,<br>  `PREC` int(10) unsigned DEFAULT NULL,<br>  PRIMARY KEY (`TABLE_ID`,`POS`)<br>) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `SYS_COLUMNS` ( `TABLE_ID` bigint(20) unsigned NOT NULL, `POS` int(10) unsigned NOT NULL, `NAME` varchar(255) DEFAULT NULL, `MTYPE` int(10) unsigned DEFAULT NULL, `PRTYPE` int(10) unsigned DEFAULT NULL, `LEN` int(10) unsigned DEFAULT NULL, `PREC` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`TABLE_ID`,`POS`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Here TABLE_ID is a well known table identifier, POS – a position of a field in the table. NAME is the name of a field, MTYPE and PRTYPE store information about the field type, encoding, NULL/NOT NULL properties etc.
LEN is the maximum number of bytes a field uses to store a value. I’m not sure what PREC is used for. It sounds like a short from “precision”, but at least for DECIMAL type where it would make sense it is still zero. If you know how InnoDB uses PREC please let me know.

mysql&gt; select * from SYS_COLUMNS WHERE TABLE_ID=741;<br>+----------+-----+-------------+-------+---------+------+------+<br>| TABLE_ID | POS | NAME        | MTYPE | PRTYPE  | LEN  | PREC |<br>+----------+-----+-------------+-------+---------+------+------+<br>|      741 |   0 | actor_id    |     6 |    1794 |    2 |    0 |<br>|      741 |   1 | first_name  |    12 | 2162959 |  135 |    0 |<br>|      741 |   2 | last_name   |    12 | 2162959 |  135 |    0 |<br>|      741 |   3 | last_update |     6 |    1799 |    4 |    0 |<br>+----------+-----+-------------+-------+---------+------+------+

mysql> select * from SYS_COLUMNS WHERE TABLE_ID=741; +----------+-----+-------------+-------+---------+------+------+ | TABLE_ID | POS | NAME | MTYPE | PRTYPE | LEN | PREC | +----------+-----+-------------+-------+---------+------+------+ | 741 | 0 | actor_id | 6 | 1794 | 2 | 0 | | 741 | 1 | first_name | 12 | 2162959 | 135 | 0 | | 741 | 2 | last_name | 12 | 2162959 | 135 | 0 | | 741 | 3 | last_update | 6 | 1799 | 4 | 0 | +----------+-----+-------------+-------+---------+------+------+

So, we know all fields of the table, we can get the type. Is it enough for the recovery? No.

SYS_FIELDS
We need to know what fields form the primary key. The matter is regardless at what position primary key fields are defined in CREATE TABLE statement internally they always go first in a record. The second issue we should take into account is internal fields DB_TRX_ID and DB_ROLL_PTR . These two fields always reside between the primary key fields and the rest of the fields.
SYS_FIELDS lists fields of all indexes, including the primary.

CREATE TABLE `SYS_FIELDS` (<br>  `INDEX_ID` bigint(20) unsigned NOT NULL,<br>  `POS` int(10) unsigned NOT NULL,<br>  `COL_NAME` varchar(255) DEFAULT NULL,<br>  PRIMARY KEY (`INDEX_ID`,`POS`)<br>) ENGINE=InnoDB DEFAULT CHARSET=latin1

1	CREATE TABLE `SYS_FIELDS` (<br> `INDEX_ID` bigint(20) unsigned NOT NULL,<br> `POS` int(10) unsigned NOT NULL,<br> `COL_NAME` varchar(255) DEFAULT NULL,<br> PRIMARY KEY (`INDEX_ID`,`POS`)<br>) ENGINE=InnoDB DEFAULT CHARSET=latin1

Fields names explain their content.
Index id in our example is 1679:

mysql&gt; SELECT * FROM SYS_FIELDS WHERE INDEX_ID = 1679;<br>+----------+-----+----------+<br>| INDEX_ID | POS | COL_NAME |<br>+----------+-----+----------+<br>|     1679 |   0 | actor_id |<br>+----------+-----+----------+

1	mysql> SELECT * FROM SYS_FIELDS WHERE INDEX_ID = 1679;<br>+----------+-----+----------+<br>\| INDEX_ID \| POS \| COL_NAME \|<br>+----------+-----+----------+<br>\| 1679 \| 0 \| actor_id \|<br>+----------+-----+----------+

Which means the primary key of the table is one field actor_id.

Now we have all necessary information to generate CREATE TABLE statement.

0. Download the latest revision of the recovery tool

bzr branch lp:percona-data-recovery-tool-for-innodb

1	bzr branch lp:percona-data-recovery-tool-for-innodb

1. Compile the dictionary parsers

make dict_parsers

1	make dict_parsers

2. Split ibdata1 with page_parser

./page_parser -f /var/lib/mysql/ibdata1

1	./page_parser -f /var/lib/mysql/ibdata1

3. Recover SYS_TABLES, SYS_INDEXES, SYS_COLUMNS and SYS_FIELDS from indexes 0-1, 0-3, 0-2 and 0-4 respectively.

./bin/constraints_parser.SYS_FIELDS -4f pages-ibdata1/FIL_PAGE_INDEX/&lt;index_id&gt;

1	./bin/constraints_parser.SYS_FIELDS -4f pages-ibdata1/FIL_PAGE_INDEX/<index_id>

4. Load dumps of the dictionary tables into some MySQL server. Use LOAD DATA INFILE constraints_parser generates

mysql&gt;LOAD DATA INFILE '/path/to/SYS_FIELDS' REPLACE INTO TABLE `SYS_FIELDS` FIELDS TERMINATED BY 't' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_FIELDSt' (`INDEX_ID`, `POS`, `COL_NAME`);

1	mysql>LOAD DATA INFILE '/path/to/SYS_FIELDS' REPLACE INTO TABLE `SYS_FIELDS` FIELDS TERMINATED BY 't' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_FIELDSt' (`INDEX_ID`, `POS`, `COL_NAME`);

5. Now everything is ready to generate a CREATE TABLE statement for a table:

./sys_parser -u root sakila/actor<br>CREATE TABLE `actor`(<br>        `actor_id` SMALLINT UNSIGNED NOT NULL,<br>        `first_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL,<br>        `last_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL,<br>        `last_update` TIMESTAMP NOT NULL,<br>        PRIMARY KEY (`actor_id`)<br>) ENGINE=InnoDB;

./sys_parser -u root sakila/actor CREATE TABLE `actor`( `actor_id` SMALLINT UNSIGNED NOT NULL, `first_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL, `last_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL, `last_update` TIMESTAMP NOT NULL, PRIMARY KEY (`actor_id`) ) ENGINE=InnoDB;

In the end there are two notes: