MySQL can't select from existing table because it doesn't exist?

Question

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.

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.

A quick restart of MySQL has not fixed this.

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.

Answer 1

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

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;

You should check the structure using SHOW CREATE TABLE table name

Then use CREATE INDEX to recreate the missing indexes.