CODEWITHSUNDEEP
mysqlmysql-error-2013
PeirHwa.Soo
PeirHwa.Soo

Reputation: 89

MySQL How to avoid connection lost error when updating a cascading key value?

I am using MySQL shell client from my Apps server connecting to the remote Database server.

I am trying to update the user_id column of my Member table from 'ABC' to 'CDE', I have about 40-50 tables with [FK Member.user_id cascade update] and some of the table is huge, with millions of records of the same user_id ('ABC'). Ps. I knew this db design isn't optimal.

UPDATE `member` SET `user_id` = 'CDE' WHERE `user_id` = 'ABC';

I am expecting the command above runs for 30-40 minutes but it just failed after 60+- seconds with the following error:

Error Code: 2013. Lost connection to MySQL server during query

Searched the similar issues from SO but the following suggestions just not working for my case:

max_allowed_packet=1073741824
wait_timeout=31536000
interactive_timeout=9999999
net_read_timeout=9999999
net_write_timeout=9999999
innodb_lock_wait_timeout=9999999

Note: I did use 'show variables xxx' to verify the above variable after setting them up.

Edit 1: Using MySQL 2nd Gen 5.7 from Google Cloud Platform

Upvotes: 0

Views: 37

Answers (0)

Related Questions