MySQL无法从现有表中选择，因为它不存在? [英] MySQL can't select from existing table because it doesn't exist?

问题描述

我不知道发生了什么.我有一个名为project_share_invite的表.几个小时前(在我们的生产环境中)，我不能再对该表发出SELECTs了. MySQL声称该表不存在，尽管它显示在show tables上.今天，机器上唯一值得注意的事件是例行程序包升级(通过apt).

I have no idea what is going on. I have a table called project_share_invite. A few hours ago (in our production environment) I could no longer issue SELECTs against this table. MySQL claims the table does not exist, though it shows on show tables. The only noteworthy event that has happened on the machine today is a routine package upgrade (via apt).

mysql> use analytics;
Database changed

mysql> show tables like 'project_share_invite';
+--------------------------------------------+
| Tables_in_analytics (project_share_invite) |
+--------------------------------------------+
| project_share_invite                       |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from project_share_invite;
ERROR 1146 (42S02): Table 'analytics.project_share_invite' doesn't exist

想法?这对我来说没有任何意义.

Ideas? This doesn't make any sense to me.

更新:该表的文件仍然存在于磁盘上(分别为project_share_invite.frm和project_share_invite.idb)并在其中包含内容.

Update: The files for the table are still present on disk (project_share_invite.frm and project_share_invite.idb respectively) and have content in them.

快速重启MySQL并不能解决此问题.

A quick restart of MySQL has not fixed this.

更新:使用root帐户而不是特定用户帐户时，结果相同.

Update: Same results when using root account instead of specific user account.

更新:我也无法重新创建表.

Update: I am unable to recreate the tables either.

CREATE TABLE `analytics`.`project_share_invite` ( ... )
ERROR 1146 (42S02): Table 'analytics.project_share_invite' doesn't exist

更新:应该先检查错误日志:

Update: Should have checked the error logs first:

InnoDB: Load table 'analytics/project_share_invite' failed, the table has missing foreign key indexes. 

尽管我不知道这种状态如何.

Though I've no idea how it's got in this state.

推荐答案

看起来像您在MySQL中遇到了一个已知错误，该错误中存在外键约束，但关联的索引已删除.请参阅: http://bugs.mysql.com/bug.php?id=68148

Looks like you hit a known bug in MySQL where a foreign key constraint exists, but the associated index was dropped. See: http://bugs.mysql.com/bug.php?id=68148

根据MySQL的版本(似乎需要5.6或>)，可以通过关闭外键检查然后重新创建缺少的索引来解决此问题.

Depending on the version of MySQL (Seems like you need 5.6 or >) you can fix this problem by turning off foreign key checking and then recreating the missing index(es).

SET FOREIGN_KEY_CHECKS=0;

您应该使用SHOW CREATE TABLE table name

You should check the structure using SHOW CREATE TABLE table name

然后使用创建索引来重新创建丢失的索引.

Then use CREATE INDEX to recreate the missing indexes.

这篇关于MySQL无法从现有表中选择，因为它不存在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！