MySQL > 表不存在。但它确实（或应该）

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

提问by johnsmith

I changed the datadir of a MySQL installation and all the bases moved correctly except for one. I can connect and USEthe database. SHOW TABLESalso returns me all the tables correctly, and the files of each table exists on the MySQL data directory.

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

However, when I try to SELECTsomething 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 TABLESstatement.

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

My guess is that SHOW TABLESlists file existence but does not check whether a file is corrupted or not. Consequently, I can list those files but not access them.

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

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?

有谁知道为什么会这样？

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

回答by Mike Dacre

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 InnoDBtables, you will get this crazy 'table does not exist' error mentioned above.

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

The issue is that you need the ib*files in the root of the MySQL datadir (e.g. ibdata1, ib_logfile0and ib_logfile1).

问题是您需要ib*MySQL datadir 根目录中的文件（例如ibdata1，ib_logfile0和ib_logfile1）。

When I copied those it worked for me.

当我复制它们时，它对我有用。

回答by Martin

For me on Mac OS (MySQL DMG Installation) a simple restart of the MySQL server solved the problem. I am guessing the hibernation caused it.

对于我在 Mac OS（MySQL DMG 安装）上，简单地重启 MySQL 服务器就解决了这个问题。我猜是冬眠造成的。

回答by dkinzer

I get this issue when the case for the table name I'm using is off. So table is called 'db' but I used 'DB' in select statement. Make sure the case is the same.

当我使用的表名的大小写关闭时，我会遇到这个问题。所以表被称为'db'，但我在select语句中使用了'DB'。确保情况相同。

回答by golimar

This error can also occur when setting lower_case_table_namesto 1, and then trying to access tables that were created with the default value for that variable. In that case you can revert it to the previous value and you will be able to read the table.

当设置lower_case_table_names为1，然后尝试访问使用该变量的默认值创建的表时，也会发生此错误。在这种情况下，您可以将其恢复为以前的值，并且您将能够读取该表。

回答by user1772382

1. stop mysqld
2. backup mysql folder: cp -a /var/lib/mysql /var/lib/mysql-backup
3. copy database folder from old machine to /var/lib/mysql
4. override ib* (ib_logfile* , ibdata ) from old database
5. start mysqld
6. dump dabase
7. mysqldump >dbase.mysql
8. stop mysql service
9. remove /var/lib/mysql
10. rename /var/lib/mysql-backupto /var/lib/mysql
11. start mysqld
12. create the database
13. mysqldump < dbase.mysql

1. 停止mysqld
2. 备份mysql文件夹： cp -a /var/lib/mysql /var/lib/mysql-backup
3. 将旧机器上的数据库文件夹复制到 /var/lib/mysql
4. 覆盖旧数据库中的 ib* (ib_logfile* , ibdata )
5. 启动mysqld
6. 转储数据库
7. mysqldump >dbase.mysql
8. 停止mysql服务
9. 消除 /var/lib/mysql
10. 重命名/var/lib/mysql-backup为/var/lib/mysql
11. 启动mysqld
12. 创建数据库
13. mysqldump < dbase.mysql

回答by dev-null-dweller

Please run the query:

请运行查询：

SELECT 
    i.TABLE_NAME AS table_name, 
    LENGTH(i.TABLE_NAME) AS table_name_length,
    IF(i.TABLE_NAME RLIKE '^[A-Za-z0-9_]+$','YES','NO') AS table_name_is_ascii
FROM
    information_schema.`TABLES` i
WHERE
    i.TABLE_SCHEMA = 'database'

Unfortunately MySQL allows unicode and non-printable characters to be used in table name. If you created your tables by copying create code from some document/website, there is a chance that it has zero-width-space somewhere.

不幸的是，MySQL 允许在表名中使用 unicode 和不可打印的字符。如果您通过从某个文档/网站复制创建代码来创建表格，则它可能在某处具有零宽度空间。

回答by Siraj Alam

I had the same problem and I searched for 2-3 days, but the solution for me was really stupid.

我遇到了同样的问题，我搜索了 2-3 天，但对我来说解决方案真的很愚蠢。

Restart the mysql

重启mysql

$ sudo service mysql restart

$ sudo service mysql restart

Now tables become accessible.

现在表格变得可访问。

回答by Andy

I have just spend three days on this nightmare. Ideally, you should have a backup that you can restore, then simply drop the damaged table. These sorts of errors can cause your ibdata1 to grow huge(100GB+ in size for modest tables)

我刚刚在这个噩梦中度过了三天。理想情况下，您应该有一个可以恢复的备份，然后只需删除损坏的表即可。这些类型的错误可能会导致您的 ibdata1 变得巨大（中等表的大小为 100GB+）

If you don't have a recent backup, such as if you relied on mySqlDump, then your backups probably silently broke at some point in the past. You will need to export the databases, which of course you cant do, because you will get lock errors while running mySqlDump.

如果您没有最近的备份，例如如果您依赖 mySqlDump，那么您的备份可能在过去的某个时间点悄悄地中断了。您将需要导出数据库，当然您不能这样做，因为在运行 mySqlDump 时会出现锁定错误。

So, as a workaround, go to /var/log/mysql/database_name/and remove the table_name.*

因此，作为一种解决方法，转到/var/log/mysql/database_name/并删除 table_name.*

Then immediately try to dump the table; doing this should now work. Now restore the database to a new database and rebuild the missing table(s). Then dump the broken database.

然后立即尝试转储该表；这样做现在应该可以工作了。现在将数据库恢复到新数据库并重建丢失的表。然后转储损坏的数据库。

In our case we were also constantly getting mysql has gone awaymessages at random intervals on all databases; once the damaged database were removed everything went back to normal.

在我们的例子中，我们还不断地mysql has gone away在所有数据库上以随机间隔获取消息；一旦损坏的数据库被删除，一切都恢复正常。

回答by Bruno Caponi

I don't know the reason but in my case I solved just disabling and enabling the foreign keys check

我不知道原因，但就我而言，我解决了禁用和启用外键检查的问题

SET FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHECKS=1;

回答by ykay says Reinstate Monica

I had this problem after upgrading WAMP but having no database backup.

升级 WAMP 但没有数据库备份后，我遇到了这个问题。

This worked for me:

这对我有用：

1. Stop new WAMP

2. Copy over database directories you need and ibdata1 file from old WAMP installation

3. Delete ib_logfile0and ib_logfile1

4. Start WAMP

1. 停止新的 WAMP

2. 复制您需要的数据库目录和旧 WAMP 安装中的 ibdata1 文件

3. 删除ib_logfile0和ib_logfile1

4. 启动 WAMP

You should now be able to make backups of your databases. However after your server restarts again you will still have problems. So now reinstall WAMP and import your databases.

您现在应该能够备份您的数据库。但是，在您的服务器再次重新启动后，您仍然会遇到问题。所以现在重新安装 WAMP 并导入您的数据库。