MySQL Data Directory Moved: Why Does SHOW TABLES Show a Table That Doesn't Exist?

MySQL Datadir Change: Table Exists but Inaccessible

In response to the perplexing issue of a table appearing in SHOW TABLES but returning an existence error when accessed, a solution has been discovered.

After relocating the MySQL data directory, one specific database exhibited this anomaly. Despite successfully connecting and listing the tables in the database using SHOW TABLES, attempts to retrieve data resulted in the error message "Table 'database.TABLE_ONE' doesn't exist."

The initial speculation attributed the behavior to SHOW TABLES only verifying file existence without validating file integrity. However, a different cause has been identified.

When copying a database directory directly using the command "cp -r /path/to/my/database /var/lib/mysql/new_database," this issue arises. If the database employs InnoDB tables, this specific error occurs.

The solution lies in ensuring that the ib* files (e.g., ibdata1, ib_logfile0, ib_logfile1) are present in the root directory of the MySQL data directory. After copying these crucial files, the inaccessibility issue is resolved, and table access is restored.

The above is the detailed content of MySQL Data Directory Moved: Why Does SHOW TABLES Show a Table That Doesn't Exist?. For more information, please follow other related articles on the PHP Chinese website!