KickFire is Back

After receiving an email about talking with Robert David, Director of Sales at KickFire, I checked out recent news on KickFire, as there had been little written about this company aside from the big splash they made at the MySQL Users Conference back in April 2008.

And, lo and behold, there was a piece of news, posted on October 14 - Kickfire Enters Into MySQL Enterprise Agreement With Sun Microsystems. It did not seem to garner any attention from the MySQL community yet, and the only source that picked up on this was "The Financial". Interestingly, the tagline of "The Financial" is "Open Source Information", which does not represent the same meaning to those of us working with open source tools. I don't know who is doing the PR for KickFire, but there might have been a skipped number on the speed dial somewhere.

This obviously was not as exciting as a headline like "Kickfire Machine Now in Production, Already Saving Dozens of Companies Thousands of Dollars". Or "Extended Data Warehouse Testing Beyond TPC-H headquarters Proves the Robustness of the First MySQL Data Appliance". Perhaps a review from Percona, Pythian, The 451 Group, or others listed along the panel of PlanetMySQL.org could add some fury to the data warehouse fire that is burning in the news lately.

Anyways, tomorrow I will find out what the latest news on this is; after all, since April 2008, it does seem like a race of turtles between MySQL 5.1, the KickFire Appliance, and the introduction of an affordable electric car with a decent range. I understand that quality of the product exceeds the quantity of time, so I am willing to deal with that. But for the last 2+ years, I have learned not to hold my breath over that.

Fixing InnoDB IMPORT TABLESPACE Error: ERROR 1030 (HY000): Got error -1 from storage engine

Setup
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.

Problem
When transporting an .idb file from one server to another, I ran into the following error:

ERROR 1030 (HY000): Got error -1 from storage engine

How 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);

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

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.

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 InnoDB
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: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
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

From the error log we see that the error concerned mismatched tablespace ids.

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).

Solution
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!