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

I am struggling with the same issue. I cannot create a table, even though it does not exist. I tried all the above solutions with no success.

My solution was to delete the files ib_logfil0, ib_logfile1, ibdata1, and auto.cnf from the data folder of MySQL; make sure to stop the MySQL service first before deleting these files.

Then after restarting the service, MySQL recreated these files and I was able to run a backup script were all my CREATEs were stored (a sqldump file).

Answer 2

Was trying to import a backup sql file but was getting the error; 1050 "Table already exists"

My setup was:

• Windows 7
• Mysql 5.5.16

Solution:

1. Changed the server engine from InnoDB to MyISAM
2. Using phpMyAdmin Deleted the database I was trying to import to
3. Restarted the mysql service
4. Tried the re-importation and it worked

Answer 3

Encountering the same problem (create InnoDB table) this is what finally worked for me:

DROP DATABASE `having_issues`;

I checked on a file basis, permissions, tried to REPAIR and FLUSH but nothing worked.

So if this is an option, move all working tables to another DATABASE, drop the old one (you might have to manually remove any files from the database folder before the drop to work), rename the new one, and you 'should' be back on your way. Apparently, whatever gets 'cached' using InnoDB is dropped along with the original database.

Answer 4

You won´t believe me!! I´ve just remove a comment block from my .sql file and now it works!!!!!!!

CREATE DATABASE  IF NOT EXISTS `issga` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `issga`;
--
-- Table structure for table `protocolo`
--

DROP TABLE IF EXISTS protocolo;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
 CREATE TABLE protocolo (
  `idProtocolo` int(11) NOT NULL AUTO_INCREMENT,
  `tipo` varchar(30) DEFAULT NULL,
  `estado` int(2) DEFAULT 0,
  PRIMARY KEY (`idProtocolo`)
 ) ENGINE=InnoDB AUTO_INCREMENT=142 DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `protocolo`
--

LOCK TABLES protocolo WRITE;
/*!40000 ALTER TABLE protocolo DISABLE KEYS */;
/* INSERT INTO `protocolo` VALUES () */
/*!40000 ALTER TABLE protocolo ENABLE KEYS */;
UNLOCK TABLES;

The deleted comment block was this:

--
-- Table structure for table `protocolo`
-- 

I´ve left the problematic table alone in the same .sql file. After that I´ve removed comments, only code was left, and the error dissapears.

Answer 5

I had the same problem at Mac OS X and MySQL 5.1.40. I used eclipse to edit my SQL script and than I tried MySQLWorkbench 5.2.28. Probably it converted newline characters to Mac format. I had no idea about what's wrong with my script until I commented out the first line in file. After this this script was interpreted by mysql as a one single comment. I used build-in TextEdit Mac application to fix this. After line-breaks was converted to the correct format, the error 1050 gone.

Update for Eclipse users:

To set up default ending for new files created, across the entire workspace:

Window -> Preferences -> General -> Workspace -> New text file line delimiter.

To convert existing files, open file for editing and for the currently edited file, go to the menu:

File -> Convert Line Delimiters To

Answer 6

I had this same case. The problem ended up being permissions on the parent directory.

I had been copying files in and out of mysql during testing.

drwx------   3 _mysql  wheel 

was not enough, needed to be:

-rw-rw----   3 _mysql  wheel 

Sorry to resurrect.