Thursday, December 31, 2009

MySQL Table Exists (1050) but Doesn't Exist (1051)

Ran into an interesting issue this morning:


mysql> drop table RELATIONSHIP_TYPE;
ERROR 1051 (42S02): Unknown table 'relationship_type'


Which wouldn't be that weird except for:

mysql> CREATE TABLE `RELATIONSHIP_TYPE` (`relationshipTypeId` INT UNSIGNED AUTO_INCREMENT NOT NULL, `name` VARCHAR(10) NOT NULL, `code` VARCHAR(2) NOT NULL, CONSTRAINT `PK_RELATIONSHIP_TYPE` PRIMARY KEY (`relationshipTypeId`)) ENGINE INNODB;
ERROR 1050 (42S01): Table 'relationship_type' already exists


After a fair amount of Google searching, I finally found an answer that implied I should try this:

$ mysqladmin flush-tables


Which seems to have solved the problem. I'm posting this in the hopes that the next person will have an easier time finding the answer.

5 comments:

David Green said...

Thanks, I just encountered the same issue and your solution fixed the problem.

Oddly, MySQL seems to need this flush tables every time after dropping one or more tables.

Geoffrey Wiseman said...

Strange; I've only ever encountered it once in several years of MySQL use, so you're hitting it much more often than I was.

Anonymous said...

Finally .. the simple solution ... thnx .. saved me lots of time probably

Paul said...

Wierd. Had exact same problem and this fixed it. Thanks.

Anonymous said...

Hello,

had the same issue, flush tables didnt help

Finally, i had to delete .frm files in mysql/db_name directory

Worked like a charm