CODEWITHSUNDEEP
mysqlinnodbmyisamalter
jcoppens
jcoppens

Reputation: 5440

Conversion myisam to innodb gives "table already exists" on (almost) all tables

It should have been easy... I have a database with about 20 tables in it, all in MyISAM. I wanted to convert them to InnoSB, so I executed:

alter table xxxx engine = InnoDB;

As a result I get 

ERROR 1050 (42S01): Table './yyy/xxx' already exists

I tried several of the tables, and thought it would happen with all, till I hit two tables which got converted correctly. A SHOW TABLE STATUS confirmed the correct conversion. Strangely enough several of the other tables had a very similar or equal structure but didn't convert.

MySQL Version is 5.5.27... I tried through phpMyAdmin, and directly through mysql commands. Same result.

Note: I had found the thread indicated below, and here are some of the tests: - I did mysqlchk the table - it's ok. - The DROP TABLE suggested works, table disappears, - REPAIR TABLE then reports it can't file the table (as is logical). - If I re-instate the table from a backup, Same error re-appears.

Could a parallel InnoDB table exist, which I can't see?

Upvotes: 3

Views: 1957

Answers (1)

jcoppens
jcoppens

Reputation: 5440

To be fair, the solution is inspired by one of the last entries in the thread indicated by #takteek.

Deciding the problem was probably a duplicate table (the original in MyISAM, and probably some tables in InnoDB, left over from previous experiments), I decided to use the sledgehammer approach:

  • DROP DATABASE yyy;
  • Reinstated the MyISAM database from a backup (Stopped the server, untarred the database archive, restarted the server).
  • then did the ALTER TABLES xxx ENGINE InniDB on each table

I generally dislike such dramatic solutions, as they give the feeling there are tools (or knowledge) lacking. In this case, I couldn't find a way to confirm or confirm the spurious tables, or a way to selectively drop those tables.

Anyway, maybe this helps someone.

Upvotes: 1

Related Questions