Mysql 1050 Error “Table already exists” when in fa

Question

I'm adding this table:

CREATE TABLE contenttype (
        contenttypeid INT UNSIGNED NOT NULL AUTO_INCREMENT,
        class VARBINARY(50) NOT NULL,
        packageid INT UNSIGNED NOT NULL,
        canplace ENUM('0','1') NOT NULL DEFAULT '0',
        cansearch ENUM('0','1') NOT NULL DEFAULT '0',
        cantag ENUM('0','1') DEFAULT '0',
        canattach ENUM('0','1') DEFAULT '0',
        isaggregator ENUM('0', '1') NOT NULL DEFAULT '0',
        PRIMARY KEY (contenttypeid),
        UNIQUE KEY packageclass (packageid, class)
);

And I get a 1050 "table already exists"

But the table does NOT exist. Any ideas?

EDIT: more details because everyone seems to not believe me :)

DESCRIBE contenttype

yields:

1146 - Table 'gunzfact_vbforumdb.contenttype' doesn't exist

and

CREATE TABLE gunzfact_vbforumdb.contenttype(
contenttypeid INT UNSIGNED NOT NULL AUTO_INCREMENT ,
class VARBINARY( 50 ) NOT NULL ,
packageid INT UNSIGNED NOT NULL ,
canplace ENUM( '0', '1' ) NOT NULL DEFAULT '0',
cansearch ENUM( '0', '1' ) NOT NULL DEFAULT '0',
cantag ENUM( '0', '1' ) DEFAULT '0',
canattach ENUM( '0', '1' ) DEFAULT '0',
isaggregator ENUM( '0', '1' ) NOT NULL DEFAULT '0',
PRIMARY KEY ( contenttypeid ) ,

Yields:

1050 - Table 'contenttype' already exists

Answer 1

Sounds like you have Schroedinger's table...

Seriously now, you probably have a broken table. Try:

• DROP TABLE IF EXISTS contenttype
• REPAIR TABLE contenttype
• If you have sufficient permissions, delete the data files (in /mysql/data/db_name)

Answer 2

This problem also occurs if a 'view' (imaginary table) exists in database as same name as our new table name.

Answer 3

Same problem occurred with me while creating a view. The view was present earlier then due to some changes it got removed But when I tried to add it again it was showing me "view already exists" error message.

Solution:

You can do one thing manually.

1. Go to the MySQL folder where you have installed it
2. Go to the data folder inside it.
3. Choose your database and go inside it.
4. Data base creates ".frm" format files.
5. delete the particular table's file.
6. Now create the table again.

It will create the table successfully.

Answer 4

My CREATE statement was part of staging env dump.

I did try everything that has been mentioned above. I DID NOT get solution. However my path to redemption was:

1. I stumble upon the fact that (one of many in) the CREATE statement did get through when I rectified the database name case sensitivity. This clicked something. I repeated the same for the other tables.

2. However a new error came into the scene. The straight quotes for 'comments' were throwing syntax error. I was shocked. replaced them but the new error started popping up. Finally i knew the solution.

SOLUTION: The dump i was using might have been from a different version of MySql. I got permission to connect to the staging MYsql using the local(installed on my machine) mysql workbench. I did not rdp into the staging server to login to staging mysql workbench. Created a dump from there. Ran the dump and it worked like a sweet.

Answer 5

You may need to flush the table cache. For example:

DROP TABLE IF EXISTS `tablename` ;
FLUSH TABLES `tablename` ; /* or exclude `tablename` to flush all tables */
CREATE TABLE `tablename` ...

Answer 6

In my case I found this to be an issue with InnoDB; I never discovered what the actual problem was, but creating as a MyISAM allowed it to build