I was playing around on a test server and i actually removed both "frm" and "innodb" files of particular table. Ok – I tried creating that table again but got information that table already exists and that i should discard it's table space. I wasn't able to discard table space because there were no underlying files, i've got error that table doesn't exist. Creating new table (or simply doing copy from other server) won't work too.
In despair i tried removing any mention of that table in "information_schema", it seemed obvious – removing any trace that table ever existed but "information_schema" is read only 🙁
What should i do to actually fix this issue?
Best Answer
The table still exists in the InnoDB dictionary. It's similar to how MySQL left temporary tables after a failed ALTER.
I found that the easiest way to remove an entry from the dictionary tables is to drop whole database. Obviously you want to keep the data, so moving it to a temporary database will work. Anyways, the steps are:
Check out https://twindb.com/resolving-error-1050-42s01-at-line-1-table-already-exists/ for some background.