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

Your disk also might just be full. (just had that)

Answer 2

from MySQL Log:

InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and copying the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.

Answer 3

I had this same problem and it looks like the Database name was case sensitive. My Database is called:

Mydatabase

Whilst my script included

USE mydatabase

Once I changed the database name to the correct case it all seemed to work. Using MYSQL Workbench on MAC OSX

Answer 4

In my case the problem was that there was a view with the same name as my table, so I had to drop the view to allow the import to continue.

drop view `my-view-that-has-same-name-as-table`;

An automated solution that worked for me is to replace the normal drop table with this sed during the dump to also drop any views that might exist:

mysqldump my-db \
| sed -E 's/^DROP TABLE IF EXISTS(.+)$/\0 DROP VIEW IF EXISTS\1/g' \
| mysql my-other-db

Or if you would rather print to a file for backup

mysqldump my-db \
| sed -E 's/^DROP TABLE IF EXISTS(.+)$/\0 DROP VIEW IF EXISTS\1/g' \
> my-db.dump.sql

Or if you received the dumped file and you are importing it to your db

cat my-db.dump.sql \
| sed -E 's/^DROP TABLE IF EXISTS(.+)$/\0 DROP VIEW IF EXISTS\1/g' \
| mysql my-other-db

You get the idea

Note: it is important that you add the ^ at the beginning of the replacement regex, because there are other types of DROP TABLE IF EXISTS commands in dumps that you don't want to touch.

You go from having something like this:

--
-- Table structure for table `my_table`
--

DROP TABLE IF EXISTS `my_table`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `my_table` (
...

To having something like this:

--
-- Table structure for table `my_table`
--

DROP TABLE IF EXISTS `my_table`; DROP VIEW IF EXISTS `my_table`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `my_table` (
...

Answer 5

First check if you are in the right database USE yourDB and try Select * from contenttype just to see what is it and if it exists really...

Answer 6

gosh, i had the same problem with osCommerce install script until i figured out the mysql system has many databases and the create table query copies itself into each one and thus droping only the working table on active db didnt help, i had to drop the table from all dbs