Description:
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;
