MySQL >表不存在.但它确实(或应该)

问题描述

我更改了 MySQL 安装的数据目录，并且所有基础都正确移动，除了一个.我可以连接并USE 数据库.SHOW TABLES 也正确返回了我所有的表，每个表的文件都存在于 MySQL 数据目录中.

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.

但是，当我尝试从表中SELECT 某些内容时，我收到一条错误消息，指出该表不存在.然而，这没有意义，因为我能够通过 SHOW TABLES 语句显示同一个表.

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.

我的猜测是 SHOW TABLES 列出文件存在但不检查文件是否损坏.因此，我可以列出这些文件但不能访问它们.

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?

示例:

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

推荐答案

以防万一还有人关心:

我在直接使用命令复制数据库目录后遇到了同样的问题

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

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

如果您对使用 InnoDB 表的数据库执行此操作，您将收到上面提到的这个疯狂的表不存在"错误.

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

问题是您需要 MySQL 数据目录根目录中的 ib* 文件(例如 ibdata1、ib_logfile0 和 ib_logfile1).

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.

这篇关于MySQL >表不存在.但它确实(或应该)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！