Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Nov 9 '12 at 3:32

This question came from our site for professional and enthusiast programmers.

1 Answer 1

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.

share|improve this answer
    
How would you stop the tables become desync'd between steps 3 and 5? –  eggyal Nov 9 '12 at 0:39
    
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 : facebook.com/notes/mysql-at-facebook/… –  foozy Nov 9 '12 at 0:58
    
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. –  TomBomb Nov 9 '12 at 2:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.