Blog

Tuesday, 19 June 2018 21:25

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

Written by 

I did change the datadir of a MySQL installation and following some steps it worked fine. Every base I had was moved correctly but one.

I can connect and USE the database, even SHOW TABLES returns me all the tables correctly and the files of each table exists on the mysql data directory. But when I try to SELECT something there, it says the table doesn't exists. But the table does exists, it even shows at SHOW TABLES statement!

My guess is that the SHOW TABLES lists the files existence somehow that the files are corrupt or something like that but it doesn't check it. So I can list them but not access them.

But that's just a guess, I've never seen this before. Can't restart the database now for testing, every other application which uses it is running fine.

Does anyone knows what is it?

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

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

Read 5953 times Last modified on Tuesday, 19 June 2018 21:35