MySQL > Table doesn't exist. But it does (or it should)

I changed the datadir of a MySQL installation and all the bases moved correctly except for one. I can connect and USE the database. SHOW TABLES also returns me all the tables correctly, and the files of each table exists on the MySQL data directory.

However, when I try to SELECT something from the table, I get an error message that the table does not exist. Yet, this does not make sense since I was able to show the same table through SHOW TABLES statement.

My guess is that SHOW TABLES lists file existence but does not check whether a file is corrupted or not. Consequently, I can list those files but not access them.

Nevertheless, it is merely a guess. I have never seen this before. Now, I cannot restart the database for testing, but every other application that uses it is running fine. But that's just a guess, I've never seen this before.

Does anyone know why this is happening?

Example:

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_database    |
+-----------------------+
| TABLE_ONE             |
| TABLE_TWO             |
| TABLE_THREE           |
+-----------------------+
mysql> SELECT * FROM TABLE_ONE;
ERROR 1146 (42S02): Table 'database.TABLE_ONE' doesn't exist

Solution 1:

Just in case anyone still cares:

I had the same issue after copying a database directory directly using command

cp -r /path/to/my/database /var/lib/mysql/new_database

If you do this with a database that uses InnoDB tables, you will get this crazy 'table does not exist' error mentioned above.

The issue is that you need the ib* files in the root of the MySQL datadir (e.g. ibdata1, ib_logfile0 and ib_logfile1).

When I copied those it worked for me.

Solution 2:

For me on Mac OS (MySQL DMG Installation) a simple restart of the MySQL server solved the problem. I am guessing the hibernation caused it.