Bug #77034 Server crashes mid constraint-definition, then gets false 'duplicate key' error.
Submitted: 14 May 2015 3:52 Modified: 10 Apr 16:06
Reporter: Sterling Cox Email Updates:
Status: Unsupported Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:Percona 5.6.23-72.1 R 0503478 OS:Linux (Ubuntu 14.04LTS)
Assigned to: CPU Architecture:Any
Tags: cleanup, constraints, crash, DDL, logging, restart, temp tables

[14 May 2015 3:52] Sterling Cox
The server '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;

ERROR 2013 (HY000): Lost connection to MySQL server during query
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)

How to repeat:
try adding a foreign key to a 50G table using a Fabric-friendly my.cnf (GTID replication slave) - details at stack exchange: http://v.gd/zqkcdw , my.cnf at http://pastebin.com/07YqA7fn

Percona Server (GPL), Release 72.1, Revision 0503478

mysql  Ver 14.14 Distrib 5.6.23-72.1, for debian-linux-gnu (x86_64) using  EditLine wrapper

This on a 48GB RAM 1.1T disk Linode VM

Suggested fix:
Remove all temp tables on startup;
[10 Apr 16:06] Godofredo Miguel Solorzano
Sorry for the delay. We process bugs only for MySQL products, please report the bugs for Percona bugdb system. Thanks.