Mar 03
Digg
Stumbleupon
Technorati
Delicious

InnoDB creating table problem

Hi, a quick note.
Somehow I had a problem with performing propel-build-all. I had a few schema.yml files and building process was going fine for them except last one schema. It did not create a table for that schema.yml and reported about mysql error (cant create a table). I think I killed about an hour trying to fix yml file (I thought that’s the problem with tabs or syntax in yml). And finally I figured out I had to take a look into mysql error log. And here is what I have found out there:

080223 16:17:48 InnoDB: Error: table `dbname/tablename` already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43? InnoDB: See the Restrictions section of the InnoDB manual. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html

So it turned out I had broken tablename.frm file and when I overwritten it with anothertablename.frm it started it work.
Maybe when you’ll get into such problem you wont kill your time because of this article 🙂


Author: symfonian

3 Comments

[…] InnoDB creating table problem […]

brainfault
March 10, 2008

I encountered something similar some days ago and my problem was a bad foreign key declaration integer vs bigint
I wrote an entry about it http://www.brainfault.com/2008/02/15/mysql-error-1005-hy000-cant-create-table-tablefrm-errno-150/

Tim
January 3, 2011

You can also get this error if you’re trying to make a foreign between two tables that use two different database engines (ie: one uses INNODB and the other MyISAM)

Comments RSS TrackBack Identifier URI

Leave a comment