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> 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> 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?

  • Meanwhile, file a bug report with bugs.mysql.com -- ALTER should be cleaner than that. – Rick James May 13 '15 at 20:34
  • @rick-james - does it make sense to do that when I'm having the problem with a Percona server? There's a deeper issue in that the server is crashing & restarting itself mid-process, but leaves no hint of what's wrong in the error log. I also found that I can 'see' the temporary table: show create table #sql-8a0_e6 actually shows the structure, but drop table returns ERROR 1051 (42S02): Unknown table 'serpsalpha.#sql-8a0_e6' – Eljuan May 13 '15 at 22:49
  • #sql* files are temps left behind by the crash. Did you run out of disk space? Manually go into the filesystem and delete the file. You could file a bug with Percona. Percona gets the bugs reports/fixes from Oracle (who owns the link I gave you). – Rick James May 13 '15 at 23:03
  • Thanks for the link & the explanation, @rick-james - I'll go ahead & file the bug. The total disk space used by the table I'm altering is ~60G and I have 800G free on the server where I'm doing it, so I don't think disk space is an issue. For now I'll get it done with mysqldump, sed, and rename table. – Eljuan May 13 '15 at 23:58
  • If tmpdir is a different 'filesystem', there could still be a disk space problem. – Rick James May 14 '15 at 0:53

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.