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:
1 2 | InnoDB: Error: tablespace id is 10 in the data dictionary InnoDB: but in file ./sakila/actor.ibd it is 15! |
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:
1 2 3 4 5 6 7 8 9 10 11 | 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 |
SYS_INDEXES:
1 2 3 4 5 6 7 8 9 10 | 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 |
Please note field SPACE. For table actor it is equal to 15:
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> select * from `INNODB_SYS_TABLES` where `SCHEMA` = 'sakila' AND `NAME` = 'actor'G *************************** 1. row *************************** SCHEMA: sakila NAME: actor ID: 13 N_COLS: 2147483652 TYPE: 1 MIX_ID: 0 MIX_LEN: 0 CLUSTER_NAME: SPACE: 15 1 row in set (0.00 sec) |
SPACE is equal to 15 in all actor’s indexes:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql> select * from INNODB_SYS_INDEXES WHERE TABLE_ID = 13G *************************** 1. row *************************** TABLE_ID: 13 ID: 15 NAME: PRIMARY N_FIELDS: 1 TYPE: 3 SPACE: 15 PAGE_NO: 3 *************************** 2. row *************************** TABLE_ID: 13 ID: 16 NAME: idx_actor_last_name N_FIELDS: 1 TYPE: 0 SPACE: 15 PAGE_NO: 4 2 rows in set (0.00 sec) |
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:
| 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:
1 2 3 4 5 6 7 8 | mysql>CREATE TABLE actor ( actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (actor_id), KEY idx_actor_last_name (last_name) )ENGINE=InnoDB DEFAULT CHARSET=utf8; |
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.
1 2 3 4 5 6 7 | # ./ibdconnect -o /var/lib/mysql/ibdata1 -f /var/lib/mysql/sakila/actor.ibd -d sakila -t actor actor.ibd actor.ibd belongs to space #15 ... Skipped output... SYS_TABLES is updated successfully ... Skipped output... SYS_INDEXES is updated successfully |
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:
1 2 3 4 5 | InnoDB: Reading tablespace information from the .ibd files... InnoDB: Error: trying to add tablespace 15 of name './sakila/customer.ibd' InnoDB: to the tablespace memory cache, but tablespace InnoDB: 15 of name './sakila/actor.ibd' already exists in the tablespace InnoDB: memory cache! |
To refrain from such error ibdconnect does check if the space_id is already used.
It will refuse to update ibdata1:
1 2 3 | $ ./ibdconnect -o ibdata1 -f t2.ibd -d sakila -t actor ... Error: Space id: 12 is already used in InnoDB dictionary for table test/t2 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # ./innochecksum -f /var/lib/mysql/ibdata1 page 8 invalid (fails new style checksum) page 8: new style: calculated = 0x B7C5C82C; recorded = 0x BFE71C21 fixing new checksum of page 8 page 11 invalid (fails new style checksum) page 11: new style: calculated = 0x E4189B9B; recorded = 0x C168689B fixing new checksum of page 11 # # ./innochecksum -f /var/lib/mysql/ibdata1 page 8 invalid (fails old style checksum) page 8: old style: calculated = 0x 8195646B; recorded = 0x DA79A2EE fixing old checksum of page 8 page 8 invalid (fails new style checksum) page 8: new style: calculated = 0x 119FD630; recorded = 0x B7C5C82C fixing new checksum of page 8 page 11 invalid (fails old style checksum) page 11: old style: calculated = 0x 908297E7; recorded = 0x 6536CEE8 fixing old checksum of page 11 page 11 invalid (fails new style checksum) page 11: new style: calculated = 0x D5DC3269; recorded = 0x E4189B9B fixing new checksum of page 11 |
4. The third time to be sure ibdata1 has valid checksums:
1 2 | #./innochecksum /var/lib/mysql/ibdata1 # |
5. Now you can start MySQL and take a dump from the table. The table is accessible, but due to obvious reason it shold not be used in production.
The tool ibdconnect was tested on MySQL 5.1 on CentOS 5.6 x86_64. However it expected to work on all versions of MySQL/InnoDB.
Excellent post! That is a great explanation, and most appreciated.
Hi Y,
Thanks for feedback.
> May 16 14:19:30 debian /etc/mysql/debian-start[14967]: Triggering myisam-recover for all MyISAM tables
> May 16 14:19:30 debian mysqld: 110516 14:19:30 InnoDB: Error: page 3 log sequence number 20 1214354523
> May 16 14:19:30 debian mysqld: InnoDB: is in the future! Current system log sequence number 0 56436.
This message is expected. The table should be readable despite of it.
>
> Only point I can see is step 0 that I have not completed. What do you mean by “0. Create empty InnoDB tablespace.” ?
> Must I import .ibd into an empty database ? In this case ibdconnect does not need to check if the space_id is already used, am I right ?
“0. Create empty InnoDB tablespace.” means you have to stop mysqld, remove ibdata1 and ib_logfile* files. Then start MySQL, InnoDB will create a new tablespace. At this point the dictionary is empty. To create a record in the dictionary you have to create the table you are going to “connect”. So, you run “CREATE TABLE …”. Then, ibdconnect and so on accoring to the instructions in the post.
Hi Aleksandr, I ‘ve just tried your promising tool and imported an .ibd file from 5.1.31 to 5.1.51 under debian.
Each step was OK, but at the end, I obtained an empty table and following messages :
May 16 14:19:30 debian /etc/mysql/debian-start[14967]: Triggering myisam-recover for all MyISAM tables
May 16 14:19:30 debian mysqld: 110516 14:19:30 InnoDB: Error: page 3 log sequence number 20 1214354523
May 16 14:19:30 debian mysqld: InnoDB: is in the future! Current system log sequence number 0 56436.
May 16 14:19:30 debian mysqld: InnoDB: Your database may be corrupt or you may have copied the InnoDB
May 16 14:19:30 debian mysqld: InnoDB: tablespace but not the InnoDB log files. See
May 16 14:19:30 debian mysqld: InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
May 16 14:19:30 debian mysqld: InnoDB: for more information.
Only point I can see is step 0 that I have not completed. What do you mean by “0. Create empty InnoDB tablespace.” ?
Must I import .ibd into an empty database ? In this case ibdconnect does not need to check if the space_id is already used, am I right ?
Many thanks
y
Fantastic work! I’ll send you guys a case of beer when this saves my [moderated] in the future.
Cheers,
Tim
The tool
innochecksum /usr/mysql/data/ibdata1
page 0 invalid (fails old style checksum)
is not working at all. In your post , you use a certain “-f”, but the tool says “Parameter incorrect.
Can you make sure that this tool works with Percona Server 5.5?
Actually I was using the innochecksum that comes with mysql. Once I used yours, I run it several times until there were no more errors. But the database is useless. The data is corrupt and I get these messages, hundreds
111108 3:29:15 InnoDB: Error: page 1023743 log sequence number 30042909687
InnoDB: is in the future! Current system log sequence number 14824393635.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
111108 3:29:15 InnoDB: Error: page 1023735 log sequence number 34414181181
InnoDB: is in the future! Current system log sequence number 14824393635.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
The question, is how do we overcome this issue? Is there a way to ingest an *.ibd file or this is a lost cause?
Philip,
Page 0 must not be invalid. InnoDB dictionary starts from page 8 (SYS_TABLES) and page 11 (SYS_INDEXES).
Maybe you didn’t re-create InnoDB tablespace. Anyway, ibdconnect doesn’t modify page 0.
You should also ignore errors about LSN from the future:
111108 3:29:15 InnoDB: Error: page 1023735 log sequence number 34414181181
InnoDB: is in the future! Current system log sequence number 14824393635.
Just run mysqld with innodb_force_recovery=[456] and take a dump of the table you’ve connected.
Aleksandr,
What changes if I have two files ibdata (ibdata1 and ibdata2) defined by innodb_data_file_path?
Because I’m not getting the data recovered. The table shows up empty.
Thank you!
Hugs!
Bevilaqua,
The dictionary resides in ibdata1 regardless the number of common table spaces.
If you were able to SELECT from table, then ibdconnect did its job.
You don’t see records on some other reason. Probably it’s better to restore the records with constraints_parser then.
some automation:
https://github.com/piotrze/ibd_recovery
Hi. This is a great stuff! Thanx.
But i have a problem with partitionings table. For example. If i try:
./ibdconnect -o /var/lib/mysql/ibdata1 -f /var/lib/mysql/mydb/st_results#P#p_1.ibd -d mydb -t st_results
where st_results#P#p_1.idb is first partition, than i give:
TABLE_ID of
mydb.st_resultscan not be 0in output.
Is there any ways to connecting partitioning table. Thanx for response! Excuse me for my language experience.
Yuri,
I think a partition appears in the dictionary as a normal table, but with name st_results#P#p_1.
ibdconnect scans SYS_TABLES in order to find table st_results. There is no such of course.
Try this way:
./ibdconnect -o /var/lib/mysql/ibdata1 -f “/var/lib/mysql/mydb/st_results#P#p_1.ibd” -d mydb -t “st_results#P#p_1”
If doesn’t work please file a bug here https://bugs.launchpad.net/percona-data-recovery-tool-for-innodb
Aleksandr,
I’m trying to restore my database (ibdata has been removed…), step-by-step instructions.
After running “innochecksum” and starting the server and trying:
> use database;
> desc bad_table;
>
> ERROR 2006 (HY000): MySQL server has gone away
> No connection. Trying to reconnect…
> Connection id: 3
> Current database: database
> ERROR 2006 (HY000): MySQL server has gone away
> No connection. Trying to reconnect…
> ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (61)
> ERROR:
> Can’t connect to the server
In error log:
120612 02:43:36 InnoDB: Error: page 0 log sequence number 30042909687
InnoDB: is in the future! Current system log sequence number 14824393635.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
InnoDB: for more information
120613 02:43:36 mysqld_safe mysqld from pid file /work/mysql/mysql.pid ended
I checked several times steps – all right.
Versions:
FreeBSD 9, MySQL: Server version: 5.1.60-log FreeBSD port: mysql-server-5.1.60
InnoDB Plugin is using!
Uaahhaa!! I did it!
There are more steps for my installation with InnoDB Plugin:
0. InnoDB Plugin must be enabled.
1. Do steps 0 and 1 described in this post.
2. Shutdown MySQL.
3. Edit my.cnf to disable InnoDB Plugin.
4. Do steps 2, 3 and 4.
5. Start on MySQL (with built-in innodb, not plugin)
6. do “ALTER TABLE bad_table ENGINE=MyISAM;”
Done.
Now we can create dump via mysqldump and restore data.
Aleksandr, thank you for your help!!!
Alex,
I’m glad the tool was useful for you.
Few followup notes:
It’s good to start MySQL with innodb_force_recovery=6 to make sure InnoDB does nothing at the start – any unneeded action may cause crash.
If you were able to ALTER TABLE at #6 you probably could SELECT from the table at this point. That would save some time.
Aleksandr,
Thanks for advice!
Hi,
When trying to use innochecksum to fix ibdata1 I get this error:
./innochecksum -f /var/lib/mysql/ibdata1.recovery
page 65 invalid (fails log sequence number check)
page 65: log sequence number: first = 0x80000000; second = 0x1A4
I had to make a copy of ibdata1 using dd because the server crashed and ibdata got corrupted:
dd if=ibdata1 of=ibdata1.recovery conv=noerrors
Any idea what to do to fix ibdata? thanks!
Octavian,
Check out the last revision from the trunk (a critical bug was fixed recently https://bugs.launchpad.net/percona-data-recovery-tool-for-innodb/+bug/1033677) and run innochecksum with -l option. It existed before, but wasn’t documented:
$ ./innochecksum page-65
page 0 invalid (fails log sequence number check)
page 0: log sequence number: first = 0x80000000; second = 0x000001A4
$ ./innochecksum page-65 -l 0x000001A4
page 0 invalid (fails log sequence number check)
page 0: log sequence number: first = 0x80000000; second = 0x000001A4
New LSN will be 0x1A4
$ ./innochecksum page-65
$
Thank you Aleksandr!
I’ve checked out the code and recompiled it. Looks promising so far (I think! 🙂 ):
page 84 invalid (fails log sequence number check)
page 84: log sequence number: first = 0x50209105; second = 0xA33EC800
[root@lightbringer percona-data-recovery-tool-for-innodb]# ./innochecksum /var/lib/mysql/ibdata1.recovery -l 0xA33EC800
page 84 invalid (fails log sequence number check)
page 84: log sequence number: first = 0x50209105; second = 0xA33EC800
New LSN will be 0xA33EC800
page 84 invalid (fails new style checksum)
page 84: new style: calculated = 0x029337C2; recorded = 0x8AB50009
[root@lightbringer percona-data-recovery-tool-for-innodb]# ./innochecksum /var/lib/mysql/ibdata1.recovery -f
page 84 invalid (fails new style checksum)
page 84: new style: calculated = 0x029337C2; recorded = 0x8AB50009
fixing new checksum of page 84
page 84 invalid (fails old style checksum)
page 84: old style: calculated = 0x41D7F596; recorded = 0x57000001
fixing old checksum of page 84
page 85 invalid (fails log sequence number check)
page 85: log sequence number: first = 0x00210003; second = 0x00000000
I’ll let you know if I manage to recover the data.
Thanks again for this tool!
Thanks a lot for this post! I had an old mysql server, with raid but without a logical backup. I couldn’t restore data until I read your post. BTW I had to start mysql with innodb_force_recovery=5 to be able to dump the data. BTW-2 I read this blog since a while, you guys rock!
Hi, I’m sorry for my english.
I use percona-data-recovery and almost all restored.
but there is a table beaten to pieces.
[code]
CREATE TABLE
sms_storage_2013_08(idint(11) unsigned NOT NULL,in_sms_idint(11) unsigned DEFAULT NULL,datedatetime DEFAULT NULL,statusint(1) DEFAULT NULL,texttext,msisdnvarchar(10) NOT NULL,bnumvarchar(10) DEFAULT NULL,servicevarchar(10) DEFAULT NULL,typeenum(‘in’,’out’) DEFAULT NULL,subscribe_guidbinary(36) DEFAULT NULL,transaction_idvarchar(20) DEFAULT NULL,delivery_datedatetime DEFAULT NULL,PRIMARY KEY (
msisdn,id),KEY
id(id),KEY
msisdn_cguid(subscribe_guid,transaction_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC
/*!50100 PARTITION BY KEY (msisdn)
PARTITIONS 10 */;
[/code]
structure restored but connected parts. IDB file did not work
Use this command # ./ibdconnect -o /var/lib/mysql/ibdata1 -f /var/lib/mysql/mts_info_archives/sms_storage_2013_08#P#p0.ibd -d mts_info_archives -t sms_storage_2013_08#P#p0
and
/ibdconnect -o /var/lib/mysql/ibdata1 -f /var/lib/mysql/mts_info_archives/sms_storage_2013_08#P#p0.ibd -d mts_info_archives -t sms_storage_2013_08
and
/ibdconnect -o /var/lib/mysql/ibdata1 -f /var/lib/mysql/mts_info_archives/sms_storage_2013_08.par -d mts_info_archives -t sms_storage_2013_08
but it did not
does any one have ibdconnect.exe for windows
please mail me irwan_forum at yahoo.co.id
Irwanto: you can compile it with cygwin, i did it and it worked 🙂
Hi, in what my be problem?
Next record at offset: C8
Record position: C8
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 21 25 62 2 0 0 114 111 116
Db/table: dbtovar77/b_sale_lang
Space id: 1634953984 (0x61736B00)
Next record at offset: 74
TABLE_ID of
dbbest/.b_agentcan not be 0./ibdconnect -o /var/lib/mysql/ibdata1 -f /var/lib/mysql/dbbest/b_agent.ibd -d dbbest -t b_agent
Upgrade to 5.6 will simplify the restoration a lot! I highly recommend to upgrade to the latest version and try Chris Calender suggests methods if Percona tools do not help.
Hello,
Does this still hold valid for 5.6/5.7?