3

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?

jcoppens
  • 5,306
  • 6
  • 27
  • 47
  • 1
    Maybe take a look at some of the answers here: http://stackoverflow.com/questions/3302476/mysql-1050-error-table-already-exists-when-in-fact-it-does-not – takteek Jul 06 '13 at 04:01
  • Yes, of course I read that thread. I did several of the proposed solutions, and also checked many of the 'foreign key' references I found in other mails. No all of the solutions are really 'testable' here, as I am trying to convert, so I cannot really delete the original database. I added a couple to the original message. – jcoppens Jul 06 '13 at 04:29
  • I suppose you could create new tables in a different schema and copy the data with INSERT .. SELECT. I don't really have any good ideas about what's causing your error. Permissions issue on the table files...? – takteek Jul 06 '13 at 05:09

1 Answers1

1

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.

jcoppens
  • 5,306
  • 6
  • 27
  • 47