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.