Today I ran into a strange MySql (MariaDB) error. Strange because I’m working with Oracle databases since 1991, so I’m used to drop indexes since years (moslty temporary to speed up huge bulk inserts, e.g. on a predefinded set of full load inserts, to create the index afterwards again), so ….
… I dropped an FK index on my MySql table (note: not dropping the FK constraint).
No problem on Oracle, but MySql seems to have a problem on FK constraints without index. Now, EVERY access to this table shows:
#1932 – Table ‘schema.importanttable’ doesn’t exist in engine
The same error on all sql clients as PhpMyAdmin or even with a Java JDBC class.
Check mysql_error.log: 2019-12-22 17:24:54 19652 [Warning] InnoDB: Load table ‘schema/importanttable’ failed, the table has missing foreign key indexes. Turn off ‘foreign_key_checks’ and try again. –> ok here I found out the problem with the dropped FK index!
2019-12-22 17:25:00 20348 [Warning] InnoDB: Load table ‘schema/importanttable’ failed, the table has missing foreign key indexes. Turn off ‘foreign_key_checks’ and try again.
2019-12-22 17:25:00 20348 [Warning] InnoDB: Cannot open table schema/importanttable from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem
Ok, I simply try to create the FK index again, but I still get error #1932
ALTER TABLE `importanttable` ADD KEY `othertable_id` (`othertable_id`)
2019-12-22 17:24:54 19652 [Warning] InnoDB: Load table ‘schema/importanttable’ failed, the table has missing foreign key indexes. Turn off ‘foreign_key_checks’ and try again.
Google search: MYSQL “the table has missing foreign key indexes”, I found this solution
— Do not check foreign key constraints
SET FOREIGN_KEY_CHECKS = 0;
SET GLOBAL FOREIGN_KEY_CHECKS = 0;
— create the FK index again (now this works! may have to try twice….)
ALTER TABLE `importanttable` ADD KEY `othertable_id` (`othertable_id`)
— Specify to check foreign key constraints (this is the default)
SET FOREIGN_KEY_CHECKS = 1;
SET GLOBAL FOREIGN_KEY_CHECKS = 1;
Problem solved!
Hope this might help other people with the same MySql (MariaDB) Problem