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:

Create a new database where you can create the new table just as yo uwould do it in the original database.

Stop MySQL

Copy the .frm file from the new temporary database to the original one.

About charlesnagy

I'm out of many things mostly automation expert, database specialist, system engineer and software architect with passion towards data, searching it, analyze it, learn from it. I learn by experimenting and this blog is a result of these experiments and some other random thought I have time to time.

Categories

About Charles Nagy

Database specialist

Automation expert

System engineer

Software architect

“An expert is a man who has made all the mistakes which can be made, in a narrow field.” - Niels Bohr

Don't be afraid to fail, to test, to experiment. This is what teaches you things you cannot learn from books. Everything written, told, heard are things somebody already know. If you want to be better in something you have to do things that nobody did before and push those boundaries as much as you can.