4

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.

AndySavage
  • 1,729
  • 1
  • 20
  • 34
  • Dumb questions first: Does the table have a space in the name at the end? Did any of the `project_share_invite` files get deleted from the MySQL data directory? – tadman Sep 25 '14 at 17:50
  • Try running this as well just as an additional check.. `SELECT table_name, table_type, engine FROM information_schema.tables WHERE table_schema = 'analytics';` – John Ruddell Sep 25 '14 at 17:53
  • Thanks guys. The files are still present on disk (added that info). The table shows correctly in `information_schema.tables` too. – AndySavage Sep 25 '14 at 17:57
  • The only thing that makes plausable sense here to me is that a grant for select access allowing the logged in user to select against this schema's table no longer exists. – xQbert Sep 25 '14 at 17:57
  • Unfortunately the same behavior is exhibited when logged in as the mysql root user too. – AndySavage Sep 25 '14 at 17:59
  • Has the table itself somehow lost all grants? (no public acccess no schema access, no 3rd party user access?) My logic is the db has the appropriate meta data to show that the table exists in the catalog. This implies the RDBMS knows about the table, thus the error logically resides in the users ability to access the table. If all permissions to the table have been revoked(I'm not sure you can do that for mysqlroot user but it's where I'd start to look first) – xQbert Sep 25 '14 at 18:01
  • Did it move? http://stackoverflow.com/questions/7759170/mysql-table-doesnt-exist-but-it-does-or-it-should or perhaps: http://forum.wampserver.com/read.php?2,45919,47144 need more info about context to isolate further. – xQbert Sep 25 '14 at 18:06
  • Grants look good, though as you say, I don't think you can revoke for root anyway. I recreated for a test user, and still no joy. It also hasn't moved. Config paths match the config in Chef (which haven't been updated for > 6 months). – AndySavage Sep 25 '14 at 18:11
  • Did something in the /etc/my.cnf change? Did the package install a new my.cnf? Since this is an innodb table, I wonder if the innodb_file_per_table=1 setting got lost? – gview Sep 25 '14 at 18:28

2 Answers2

12

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.

gview
  • 14,876
  • 3
  • 46
  • 51
  • Awsome response and help – xQbert Sep 25 '14 at 18:48
  • 2
    NOTE: The bug is marked is fixed, but the fix only prevents you from removing the index. You can still get into this situation if you are making changes with `FOREIGN_KEY_CHECKS=0` set. We managed to get into this on MySQL 5.7.28 after changing character sets on one database and forgetting that there were cross-database foreign keys. – SystemParadox Dec 13 '19 at 09:37
  • 3
    Even after setting the foreign_key_checks to 0, mysql does not allow me to execute the `SHOW CREATE TABLE` query. The query ends up with following error, "The table does not exist in engine.". Please help me resolve this. – Hitesh Jan 31 '20 at 11:41
2

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828