Corrupted InnoDB dictionary

Last week one of my collagues came to me with the following problem. He was not able to create an innodb table. MySQL returned ERROR 1005: Can’t create table (errno: -1). Most of the times this problem is a foreign key related issue but now this wasn’t the case (Foreign key error will return errno: 150). What actually happened is that ibdata already contains the registration of the new table but the frm file wasn’t there. This way when he wanted to create the table the server responded with error. Although he wasn’t able to remove the table either. It was caused the file-by-file cloning of the database.

For better understanding you have to be familiar with the inner operations of MySQL. With the most simplistic explaination I could say that MySQL is just working on a high SQL abstraction level which can use different storage engine for storing datas. Viewing from this aspect MySQL doesn’t care how the files will be written on the disk and how they will be read back. This is the responsibility of the storage engine (InnoDB, MyISAM etc.). /Of course some storage engine provide more functions for MySQL but this is not the post where we will go into details about differences of engines./ MySQL just communicate in “data language” with the lower levels. For more about storage engines go here: http://dev.mysql.com/doc/refman/5.5/en/storage-engines.html

Frm files are needed by MySQL regardless of the storage engine. This contains the data level information MySQL will need to be able to fetch real data from (in this example) InnoDB tables. A table creation or deletion can go wrong on different levels. Maybe MySQL thinks that we already have this table but there’s no data file for it – InnoDb doesn’t know anything about this table. Or InnoDB has registry entry about this table in InnoDB dictionary but MySQL doesn’t have schema for it. If there’s an illegal frm file we can just simply delete it. But if innodb dictionary file is corrupted we have to trick it a little because we cannot edit it or simply delete it. Steps:

1. Create a new database where you can create the new table just as yo uwould do it in the original database.
2. Stop MySQL
3. Copy the .frm file from the new temporary database to the original one.
4. Start MySQL
5. DROP TABLE [some_table_name]; (this will remove table registration entry from innodb data dictionary)
6. CREATE TABLE [some_table_name] … ;

That’s it.