Running migration which adds column to a table which has a couple million of records may be problematic, because of lock of this table during migration. Let’s say it not only may be problematic – it was problematic, because of this problem our server was down for a couple of minutes and SQL server was restarted, so to solve this problem we using ‘LHM’ gem – Large Hadron Migrator
Shortly describing how lhm works
- Creates new table called lhmn_posts (I am altering posts table in my example) with new column you adding in migration
- Copies all data from posts table to lhmn_posts.
- Rename of lhmn_posts to posts and old posts table becomes lhmn[date]posts table
Migration which adds ‘version’ column to posts table which have 6 millions of records
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Problem during migration on production you may encounter
- ‘You do not have the SUPER privilege and binary logging is enabled’ exception during migration to production, to solve this problem you need to set specific flags to your SQL db(Mysql in my case): mysql -u USERNAME -p set global log_bin_trust_function_creators=1;
- After running you migration and getting exception you may need clean temp tables lhm already created like lhmn_posts – to solve this issue just add Lhm.cleanup(:run) before migration:
1 2 3 4 5 6 7 8 9 10
3. After migration is finished lhm keeps old table as lhmn‘date’posts and it’s up to you decide when to delete this table.