Friday, April 07, 2006

InnoDB table creation fails after an improperly dropped table

I was playing around with InnoDB on my development server, somehow screwed things up pretty bad, and had to delete some .frm files from the db directory manually. However, I found that even though some of my tables didn't exist in show tables, I couldn't recreate them. the mySQL client was spitting out fairly ambiguous InnoDB errors (can't rename table, etc), and it was confusing me quite a bit. I even found that I could create the tables as myISAM, but once I tried to change engine to InnoDB, it wouldn't let me. I even tried dropping the entire database and recreating it, but the InnoDB data dictionary still had information about those tables in that database! I found some more insight by using the mySQL command "show engine innoDB status;", which told me the last foreign key errors. Turns out the foreign key constraints were still applied to my table even though the table didn't actually exist. I tried dropping the keys by name, but that didn't work. It didn't issue any errors, but it also didn't do anything. Ultimately I had to recreate all the keys as they had originally appeared (matching the name and type exactly), then drop the table properly.