MySQL deadlock - Adding columns

1

I had a nasty MySQL problem arise the other day. I added two new columns to table 'Promotions' (13 existing columns, ~750 records) using the PHPMyAdmin interface. This query ("ALTER TABLE.....") never fully executed and locked the table, so that each subsequent query to 'Promotions' would hang and queue up. It took me about an hour to realize this was happening, and the problem was resolved by me rebooting my database.

Are there standard precautions that need to be taken and known issues that arise from altering database schemas? I have never seen this kind of locking happen before.

TomBomb

Posted 2012-11-09T00:04:42.717

Reputation:

Answers

0

Consider following scenario to ALTER a mysql table more smoothly especially on production environment:

1. CREATE tmp_table LIKE original_table
2. ALTER tmp_table..
3. INSERT INTO tmp_table SELECT * FROM original_table
4. RENAME original_table to orginal_table_old
5. RENAME tmp_table to original_table
6. DROP original_table_old

Also you may interested in FLUSH TABLES and UNLOCK TABLES on documentation.

Edit: There are alternative solutions exists to solve this problem like pt-online-schema-change tool by Percona.

edigu

Posted 2012-11-09T00:04:42.717

Reputation: 223

How would you stop the tables become desync'd between steps 3 and 5? – eggyal – 2012-11-09T00:39:59.497

There is not a perfect solution for situations like this. Altering a live table requires a little downtime but if huge concurrency and business-critical data are not in stage, this method will be useful. Also facebook engineers spent lot of time to solve this problem too : https://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932

– None – 2012-11-09T00:58:41.647

Yes, this definitely makes sense. In this case there wasn't "a little downtime" though, it was completely locked out for at least one hour. I wish I had had some tools in place to run analysis on what exactly was happening before I rebooted the instance. – None – 2012-11-09T02:04:52.227

Asked: 2012-11-09T00:04:42.717

Viewed: 803 times

Active: 2012-11-10T16:13:21.230