MySQL>表格不存在.但这确实(或应该如此) [英] MySQL > Table doesn't exist. But it does (or it should)

问题描述

我更改了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

推荐答案

以防万一仍然有人在乎:

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

如果对使用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>表格不存在.但这确实(或应该如此)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！