We have one InnoDB file per table on our database, which was set with the following option: innodb_file_per_table.
This allows me to portably transport Innodb files on a system level with minimal hassle, and is much faster than mysqldump, as these tables are several GB each.
When transporting an .idb file from one server to another, I ran into the following error:
ERROR 1030 (HY000): Got error -1 from storage engineHow did I get to this point? I did the following:
1) created a dummy table in the database;
2) discarded its tablespace;
3) moved the .ibd file into the database folder on the system;
4) attached the tablespace back to the table
This is how these actions were accomplished:
mysql> CREATE TABLE new_innodb_table (value1 INT UNSIGNED NOT NULL, value2 CHAR(5) NOT NULL);The next thing you automatically do when you see an error in your mysql client is check your error log immediately. If this log is not enabled, please stop reading this post and go here.
mysql> ALTER TABLE new_innodb_table DISCARD TABLESPACE;
[sys_prompt]# mv new_innodb_table.ibd /var/lib/mysql/db_name/
mysql> ALTER TABLE new_innodb_table ATTACH TABLESPACE;
ERROR 1030 (HY000): Got error -1 from storage engine
Here is what the log recorded:
081009 14:21:02 InnoDB: Error: tablespace id in file './db_name/new_innodb_table.ibd' is 26, but in the InnoDBFrom the error log we see that the error concerned mismatched tablespace ids.
InnoDB: data dictionary it is 28.
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: Please refer to
InnoDB: for how to resolve the issue.
081009 18:21:02 InnoDB: cannot find or open in the database directory the .ibd file of
InnoDB: table `stbHealth/sub_stb_health_associate`
InnoDB: in ALTER TABLE ... IMPORT TABLESPACE
If the tablespace id for my .ibd file was 26, then the tablespace would have been successfully imported. Also, if the tablespace id in the data dictionary 25, then all I would need to do is create a dummy innoDB table, thus incrementing the tablespace id in the data dictionary to 26, then I could try importing the tablespace again. Tricky, but it works (especially if that number was lower and you would make several dummy innodb tables).
However, the easier option is this: restart mysql, then do the same four steps listed near the beginning of the post. This way, the tablespace id on the data dictionary and the file matched; thus importing the tablespace succeeded.
This can give you greater confidence in dealing with some of the InnoDB "gotcha's" during the recovery process or even file transfers. It did with me!