Table Missing. .. or Not (Mysql)?
Suddenly I've a Strange Problem with Mysql: in the Navigator I See the "Company" Table (Even After Refresh), but If I Do Select * from Company; Says That the...
Suddenly I've a strange problem with Mysql:
In the navigator I see the "company" table (even after refresh), but if I do SELECT * FROM company; says that the table does not exist.
With the command SHOW TABLES FROM smartex_develop; the table "company" is present, but if I use the command SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'smartex_develop'; the table is missing. It's very strange also considering that there are a lot of table with a foreign key of that table.
Someone know how to resolve it?
[SELECT * FROM company] 1
[SHOW TABLES FROM smartex_develop] 2
[SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'smartex_develop'] 3
1 Answer
We had a similar problem - a table suddenly went missing. In the logs we had:
Load table <name-of-missing-table> failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
InnoDB: Foreign Key referenced table <name-of-missing-table> not found for foreign table <some-other-table>
This happened upon mysqld startup, so the root cause might have been much older than that. In our case, the root cause was charset conversions. We converted a few tables, and ended up with foreign keys where the column in one table and the column in the referenced table had different character sets.
How we solved:
Disable foreign key checks
Restart mysql -- the missing table will now reappear
Convert all the columns that reference each other to have the same character set. You can use this query:
select table_name,column_name,CHARACTER_SET_NAME from INFORMATION_SCHEMA.COLUMNS where table_schema = 'myschema' and data_type='varchar';Re-enable foreign key checks
Restart mysql -- all should be fine now
Robert Thorne covers electric vehicle innovations, autonomous driving systems, global mobility trends, and automotive engineering developments.
