Monday, December 20, 2010

SOLVED: Moving InnoDB tables between Percona MySQL servers

Update. Warning: The procedure is not working. The table can be copied, imported into another server and successfully read, but an attempt to write to the table causes a server crash. The problem is probably caused by remaining tablespace ids in the ibd file.

Update 2. Kudos to the Percona team, who pointed me to an error in my configuration file. To import tablespaces, one more option had to be set on the server, innodb_expand_import. Xtrabackup documentation has been updated.

When trying to move a table from one server to another, I found a problem. I followed the procedures outlined in the Percona Xtrabackup manual, chapter Exporting Tables.

On the last step, when doing IMPORT TABLE, I received an error message saying:

mysql> alter table `document_entity_bodies` import tablespace;
ERROR 1030 (HY000): Got error -1 from storage engine

There was some more information in the log:

101220 16:14:51  InnoDB: Error: tablespace id and flags in file './dbx_replica/document_entity_bodies.ibd'
 are 21 and 0, but in the InnoDB
InnoDB: data dictionary they are 181 and 0.
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
101220 16:14:51  InnoDB: cannot find or open in the database directory the .ibd file of
InnoDB: table `dbx_replica`.`document_entity_bodies`
InnoDB: in ALTER TABLE ... IMPORT TABLESPACE

The page mentioned in the log was not really helpful. What saved me was this article: Recovering an InnoDB table from only an .ibd file.

Following the instructions, I used a hex editor (shed) to change tablespace id in the ibd file from 0x15 to 0xB5 and then the import worked fine.

I wonder if there is a way to avoid these manipulations. Perhaps, one more operation should be added to xtrabackup to make the tablespace ids agree?

Oh, and it's Xtrabackup 1.4 working with Percona Server 5.1.51

No comments:

Post a Comment