Why Does MySQL Report a Table as Non-Existent After Datadir Change, Even Though SHOW TABLES Lists It?

MySQL Table does not exist? But when I changed the MySQL datadir that (should) exist, all databases except one were successfully moved. I can connect to and use the database, the SHOW TABLES command returns all tables correctly, and the files for each table are present in the MySQL data directory.

But when I try to select something from the table I get an error message that the table does not exist. This doesn't make sense since I was able to display the same table using the SHOW TABLES statement.

I'm guessing that SHOW TABLES lists the existence of the file, but doesn't check if the file is corrupted. Therefore, although you can list those files, you cannot access them.

However, it is just a guess. I have never seen a case like this before. I am currently unable to restart the database and test it, but all other applications that use the database are working fine.

Does anyone know the reason for this phenomenon?

Example:

Solution:

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

Copy the database directory directly using the command below The same problem may occur if you do.

When I do this on a database that uses InnoDB tables, I get the weird "table does not exist" error mentioned above.

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

The problem is that I need ib* files (e.g. ibdata1, ib_logfile0, ib_logfile1) at the root of MySQL datadir.

I copied these files and it worked fine.

The above is the detailed content of Why Does MySQL Report a Table as Non-Existent After Datadir Change, Even Though SHOW TABLES Lists It?. For more information, please follow other related articles on the PHP Chinese website!