CODEWITHSUNDEEP
mysql
AndySavage
AndySavage

Reputation: 1769

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

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.

Upvotes: 4

Views: 5962

Answers (2)

gview
gview

Reputation: 15371

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.

Upvotes: 12

Bill Karwin
Bill Karwin

Reputation: 562438

This error is usually caused by moving files around at the filesystem level.

Keep in mind that SHOW TABLES just reads the .frm file, but once you query the table, MySQL invokes the storage engine. InnoDB has its own internal way of managing metadata, in a "data dictionary" which is always stored in ibdata1.

So if you moved the datadir but forgot the ibdata1 file (or copied an ibdata1 from another instance), then the InnoDB data dictionary wouldn't know about the table, even though SHOW TABLES does.

Another possibility is that you copied data files around, and now they don't have the write ownership or file permissions. So for example the .frm file is readable but the .ibd is not. They should be owned and writeable by mysql:mysql.

If your apt upgrade changed file locations or file permissions, that could cause it too. I would advise using ls -l to verify the permissions on the files.

Upvotes: 2

Related Questions