Mysql 1050 Error Table already exists when in fact it does not

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

User · Answer

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

User · Answer

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

User · Answer

Your disk also might just be full   just had that

User · Answer

I got this same error  and REPAIR TABLE  from  NullUserException s answer  didn t help   I eventually found this solution   sudo mysqladmin flush-tables   For me  without the sudo  I got the following error   mysqladmin  refresh failed  error   Access denied  you need the RELOAD privilege for this operation     Running on OS X 10 6

User · Answer

I ve been fighting with this all day   I have a Perl script that builds a set of tables by first doing a DROP IF EXISTS     on them and then CREATEing them   The DROP succeeded  but on CREATE I got this error message   table already exists  I finally got to the bottom of it   The new version of MySQL that I m using has a default engine of InnoDB   show engine  G     I changed it in the my cnf file to default to MyISAM  re-started MySQL  and now I no longer get the  table already exists  error

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    Changed the server engine from InnoDB to MyISAM Using phpMyAdmin Deleted the database I was trying to import to Restarted the mysql service Tried the re-importation and it worked

User · Answer

I ve just had the same error but I knew the table already existed and wanted to add to it  I m adding my answer as this question comes up as no 1 for me on google when looking for the same error but for a slightly different scenario  Basically I needed to tick   Add DROP TABLE   VIEW   PROCEDURE   FUNCTION   EVENT   TRIGGER statement   And this solved the error for me

User · Answer

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    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  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

User · Answer

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

User · Answer

I also encountered this problem where trying to Create a table said it already exists and Dropping the table said it didn t exist   I did  FLUSH TABLES  and it cleared the problem

User · Answer

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    Go to the MySQL folder where you have installed it Go to the data folder inside it  Choose your database and go inside it  Data base creates   frm  format files  delete the particular table s file  Now create the table again    It will create the table successfully

User · Answer

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

User · Answer

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

User · Answer

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     gt  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

User · Answer

You won  t believe me  I  ve just removed 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  the only code was left  and the error disappears

User · Answer

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

User · Answer

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

User · Answer

For me the problem was caused when using a filesystem copy of the mysql database directory instead of mysqldump   I have some very large tables  mostly MyISAM and a few InnoDB cache tables and it is not practical to mysqldump the data   Since we are still running MyISAM  XtraBackup is not an option    The same symptoms as above happened to me   The table is not there  there are no files in the directory that pertain to the table  yet it cannot be created because MySQL thinks its there   Drop table says it s not there  create table says it is     The problem occurred on two machines  both were fixed by copying backups   However  I noticed that in my backup that there was a  MYD and  MYI file  even though I was under the impression that these files are not used for InnoDB   The  MYD and  MYI files had an owner of root  while the  frm was owned by mysql    If you copy from backup  check the file permissions   Flush tables might work  but I opted to shut down and restart the database     Good luck

User · Answer

I had this problem on Win7 in Sql Maestro for MySql 12 3  Enormously irritating  a show stopper in fact  Nothing helped  not even dropping and recreating the database  I have this same setup on XP and it works there  so after reading your answers about permissions I realized that it must be Win7 permissions related  So I ran MySql as administrator and even though Sql Maestro was run normally  the error disappeared  So it must have been a permissions issue between Win7 and MySql

User · Answer

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

User · Answer

I was having huge issues with Error 1050 and 150   The problem  for me was that I was trying to add a constraint with ON DELETE SET NULL as one of the conditions    Changing to ON DELETE NO ACTION allowed me to add the required FK constraints   Unfortunately the MySql error messages are utterly unhelpful so I had to find this solution iteratively and with the help of the answers to the question above

User · Answer

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

[mysql] Mysql 1050 Error "Table already exists" when in fact, it does not

Examples related to mysql

Examples related to sql

Examples related to mysql-error-1146

Examples related to mysql-error-1050