MySQL Bugs: #77937: Table lock problem while adding new foreign key constraints

Bug #77937	Table lock problem while adding new foreign key constraints
Submitted:	4 Aug 2015 21:07	Modified:	30 Dec 2017 15:54
Reporter:	Anil Jindal	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S5 (Performance)
Version:	5.6.25	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,

I am using MySQL 5.6.25 and got an issue while using it. There are 2 big tables in my schema say table A and table B. I wanted to add 2 new columns in table B which should refer the primary key of table A. It took around 7 mins to add new columns in table B and when it starts creating foreign key constraint, it took around 5 mins and during that time it took lock on to the tables. And due to that all the web requests got rejected due to SocketTimeOutException.

Kindly advise. 

PS:- I am using liquibase for DDL.

How to repeat:
1. Create a table A and insert as much data as you can. In my case it has around 40,00,000 records.
2. Create a table B and insert as much data as you can. In my case it has around 40,00,000 records.
3. An application which should be using table A and table B and it should be up.
4. Create a DDL to alter table and add 1 or 2 columns in table B.
5. Now add foreign key constraint on newly added columns. 
6. Now see the application logs, it should start complaining SocketTimeOutException now.

Suggested fix:
No idea...

Please try latest of the released version preferably of 5.7. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".