MySql Error Code 1050 – Table ‘tablename’ already exists

avinashsing 4 Comments

I was playing around with some table creation and deletion and I noticed that after issuing a DROP command for a particular table, I was not able to recreate the table. I’ve installed MySQL on my Windows 7 laptop and by default tables are created with the InnoDB engine. This is how the problem occurs – you create a table (testtable) and immediately after that you issue the following command:

DROP TABLE testtable;

You will get a command executed successfully but when you try to create a table with the same name, you will get the following error:

MySql Error Code 1050 – Table ‘testtable’ already exists

Now if you look at your database schema, you will not find the table name and the same applies if you issue a “SHOW TABLES” command. If you look at the directory where MySQL data are stored, you will not find the .frm file for that specific table. The location is C:\ProgramData\MySQL\MySQL Server 5.1\data (not that it’s not Program Files or Program Files (x86) but ProgramData on Windows 7.

To solve the problem, you will need to restart the MySQL service. Once MySQL is restarted, you can create a table with the same name as the table you dropped earlier. If you drop another table and want to recreate a table with the same name, you will have to stop/start MySQL service again. I think MySQL stores a dictionary of the tables created and only checks whether they are still valid at a regular interval or when the service is restarted.

4 Responses

  1. Matthew November 10, 2010 / 7:39 pm

    I ran into a similar error, and I think it is because I am using it on a case-insensitve platform (MacOS X, which is case-preserving case-insensitive). I suspect that I called drop tablename, and it successfully dropped tableName, but as you suggest did not delete tableName from its dictionary of tables. This might be a bug worth reporting.

  2. John March 30, 2011 / 3:27 pm

    I am having the same issue with dropping and creating a table. I am on a Windows server with “Make table names case insensitive” set to 2. I wonder if you have found out anything else on this issue. I am still having to restart the service to correct.

  3. avinashsing March 30, 2011 / 4:05 pm

    As far as I can tell, a restart is required after you have dropped a table and immediately want to create another table with the same name. I only had to do this when I was creating the database schema for my web app, so it was a do-once thing.

Comments are closed.

comments powered by Disqus