Cleaning up orphaned metadata in information_schema OK - so the server (Percona 5.6 in this case) 'went away' in the middle of declaring a constraint like so:

alter table mytable
add constraint fk_mytable_myothertable
foreign key (myothertable_id)
references myothertable(id)
on delete restrict
on update restrict;


Now, the constraint does not show in

show create table mytable;


nor in

show keys from mytable;


yet when I try to re-run the creation of the constraint, I get

ERROR 1022 (23000): Can't write; duplicate key in table '#sql-115b_290'


--so, I searched through information_schema and found this:

mysql&gt; select * from INNODB_SYS_FOREIGN where id like '%fk_mytable_myothertable%';
+------------------------------+------------------+------------------+--------+------+
| ID                           | FOR_NAME         | REF_NAME         | N_COLS | TYPE |
+------------------------------+------------------+------------------+--------+------+
| mydb/fk_mytable_myothertable | mydb/#sql-8a0_e6 | mydb/myothertable|      1 |    0 |
+------------------------------+------------------+------------------+--------+------+
1 row in set (0.00 sec)

mysql&gt; select * from INNODB_SYS_FOREIGN_COLS where id like '%fk_mytable_myothertable%';
+------------------------------+-----------------+--------------+-----+
| ID                           | FOR_COL_NAME    | REF_COL_NAME | POS |
+------------------------------+-----------------+--------------+-----+
| mydb/fk_mytable_myothertable | myothertable_id | id           |   0 |
+------------------------------+-----------------+--------------+-----+
1 row in set (0.00 sec)


question is: can I just delete these orphaned references to the non-existent constraint, or is there more to cleaning up?
The way that I found to "solve" this problem is fairly annoying, but there is a script that handles it.

Essentially, you need the ibdata1 and ib_logfile* files to go away (they contain the mappings of foreign keys, among other things). The only safe way to do this is to export all of your databases, stop mysql, remove the files, start mysql, and then import the files.

The script that helps solve this problem is https://github.com/uberhacker/shrink-ibdata1, even though the stated purpose of this script is different, it does solve the problem.