Connecting orphaned .ibd files

There are two ways InnoDB can organize tablespaces. First is when all data, indexes and system buffers are stored in a single tablespace. This is typicaly one or several ibdata files. A well known innodb_file_per_table option brings the second one. Tables and system areas are split into different files. Usually system tablespace is located in ibdata1 file and every InnoDB table has two files e.g.  actor.frm and actor.ibd.

The annoying thing about .ibd files you can’t easily copy the an .ibd file to another MySQL server. If you try to very often you’ll get an error in the log:

However sometimes you have to connect the .ibd file to an alien ibdata1.

There are several situation when you have to:

1. ibdata1 is erroneously removed

2. ibdata1 is heavily corrupted and innodb_force_recovery doesn’t help

Chris Calender suggests two methods. The first is create/drop the table many times until space_id in InnoDB dictionary and .ibd file match. The second is to edit space_id inside .ibd file with a hex editor.

I would like to elaborate the second method.

But let’s understand first what’s going on and why InnoDB refuses to use suggested .ibd file.

There is an InnoDB dictionary. It consists of several internal tables. For our topic only SYS_TABLES and SYS_INDEXES are relevant. These are usual InnoDB tables, but they’re hidden from a user(you can see them in information_scheme database in Percona Server though).

The structure of these tables is following:

SYS_TABLES:

SYS_INDEXES:

Please note field SPACE. For table actor it is equal to 15:

SPACE is equal to 15 in all actor’s indexes:

In InnoDB world actor.ibd is a tablespace. It has space_id and it is equal to 15 for this particular table at this particular server.

As you can see secondary indexes are stored in actor.ibd as well.

But where is space_id in actor.ibd?

Like any other tablespace actor.ibd costsists of a set of InnoDB pages. A page is 16k long (UNIV_PAGE_SIZE in the source code).

Let’s take a look at the page header:

InnoDB Page Header
Name Size Description
FIL_PAGE_SPACE_OR_CHKSUM 4 /* in < MySQL-4.0.14 space id the
page belongs to (== 0) but in later
versions the ‘new’ checksum of the
page */
FIL_PAGE_OFFSET 4 ordinal page number from start of space
FIL_PAGE_PREV 4 offset of previous page in key order
FIL_PAGE_NEXT 4 offset of next page in key order
FIL_PAGE_LSN 8 log serial number of page’s latest log record
FIL_PAGE_TYPE 2 current defined types are: FIL_PAGE_INDEX, FIL_PAGE_UNDO_LOG, FIL_PAGE_INODE, FIL_PAGE_IBUF_FREE_LIST
FIL_PAGE_FILE_FLUSH_LSN 8 “the file has been flushed to disk at least up to this lsn” (log serial number), valid only on the first page of the file
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_NO 4 /* starting from 4.1.x this
contains the space id of the page */

So, space_id is 4 bytes written to every InnoDB page. An .ibd file can be huge, while ibdata1 is usually smaller. Thus, it is easier to modify space_id in InnoDB dictionary once than in every InnoDB page.

How let’s connect actor.ibd from sakila database taken from some MySQL server.

0. Create empty InnoDB tablespace.

1. Create the table:

This command will create respective records in SYS_TABLES and SYS_INDEXES.

2. Now let’s modify SPACE in InnoDB dictionary. MySQL must be stopped at this point. There is a tool ibdconnect in Percona InnoDB Recovery Tool. Make sure you’re using the latest version from the trunk.

It reads space_id from an .ibd file and updates the dictionary in ibdata1.

It is possible that space_id from actor.ibd is already used by some other table.
In this case if ibdata was updated MySQL will fail to start with error:

To refrain from such error ibdconnect does check if the space_id is already used.
It will refuse to update ibdata1:

In this case you need to drop table test.t2 and create it again. InnoDB will assign other space_id, thus 12 will be freed.

3. Now SPACE is modified in the dictionary, but checksums are bad. To regenerate them use innochecksum from the same toolset. Run it two times: