CODEWITHSUNDEEP
mysqlexistsnot-exists
Mark Johnson
Mark Johnson

Reputation: 39

Mysql: Table exists and doesn't exist

I had a table that got deleted ("filescan"). When I go to the data directory and list the files using ls, the name is not there - so it's really gone. But in mysql, I get this:

mysql> select * from filescan;
ERROR 1146 (42S02): Table 'db1.filescan' doesn't exist
mysql> create table filescan (id int);
ERROR 1813 (HY000): Tablespace '`db1`.`filescan`' exists.
mysql>

I assume that somewhere there's a table that is telling mysql that this file exists, when in fact it doesn't. Any suggestions for dealing with this example of Schödinger's mysql table?

Upvotes: 1

Views: 4671

Answers (1)

spencer7593
spencer7593

Reputation: 108400

If the table that was deleted was using InnoDB storage engine (and engine isn't specified in the CREATE TABLE statement shown in the question, so tables are using the default ,,, )

 SHOW VARIABLES LIKE 'default_storage_engine'

and if innodb_file_per_table was enabled when the table was originally created, and is still enabled...

 SHOW VARIABLES LIKE 'innodb_file_per_table'

I'd suggest that maybe there's an orphan .ibd file in the database directory 

 SHOW VARIABLES LIKE 'datadir'

From the OS command line, change directory to the database directory, and check for files that match the table name 

 # cd /var/lib/mysql
 # cd db1
 # ls -l filescan*

Assuming that the table was not partitioned (again assuming InnoDB), a table should have a filescan.frm file and and a filescan.ibd file.

It sounds like the filescan.ibd file exists, and filescan.frm is missing.

If this is the case, I would consider the table to be removed. I would relocate (move) the filescan.ibd file (to another directory, somewhere safe, as a backup e.g) 

 # mv filescan.ibd /backup/

And then try running the CREATE TABLE statement again.

It's not at all clear how MySQL would have allowed the .frm file to be removed, and leave the .ibd file. Most plausible explanations would be that the .frm file was removed from the filesystem by something other than MySQL. Or the .frm and .ibd files were removed, and the .ibd file was later restored.)

For a storage engines other than InnoDB, the problem might be something similar. (e.g. for MyISAM, the .MYD and/or .MYI files exist without a .frm file.)

Upvotes: 1

Related Questions