2

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?

  • 1
    The lesson to learn: Do not fiddle with any files. – Rick James Oct 24 '18 at 14:37
3

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:

  • Create an empty temporary database. Let it be tmp1234.
  • Move all tables from the original database to tmp1234.
  • Drop the original database (it’s empty by now, all tables are in tmp1234).
  • Create the original database again.
  • Move all tables from the temporary database to the original one.
  • Drop the empty temporary database.

Check out https://twindb.com/resolving-error-1050-42s01-at-line-1-table-already-exists/ for some background.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.