gh-ost does not support foreign key

gh-ost was released in August, it might be the best tool to upgrade MySQL table structure online so far. But haven’t gotten a chance to try.

This week, I was preparing release of one of our big sites, which has three big tables need to be upgraded (structure change), they have more than 70 million rows in total, data and index occupy more than 30G space on disk. I tried the normal structure change in MySQL, it took me around 5 hours to finish all changes on all of the three tables. That means we have to close the production site for 5 hours, sounds crazy, but what we did always.

Then, I thought maybe I can try this new tool, gh-ost, which created and tested by GitHub. But, finally, I found it does not support foreign key!

user1@db1:~/gh-ost$ ./gh-ost --max-load=Threads_running=25 --critical-load=Threads_running=1000 --chunk-size=1000 --max-lag-millis=1500 --user="user" --password="******" --host="127.0.0.1" --allow-on-master --database="database1" --table="TRANSACTION2" --verbose --alter="ALTER TABLE TRANSACTION2 MODIFY COLUMN TEMP_GH_OST_TEST_FIELD_1 INT NULL" --switch-to-rbr --cut-over=default --exact-rowcount --concurrent-rowcount --default-retries=60 --nice-ratio=0.5 --serve-socket-file=/home/user1/gh-ost/game_tx2/sock.gh-ost.database1.TRANSACTION2 --throttle-flag-file=/home/user1/gh-ost/game_tx2/flag.gh-ost.database1.TRANSACTION2.throttle --panic-flag-file=/home/user1/gh-ost/game_tx2/flag.gh-ost.database1.TRANSACTION2.panic.flag --postpone-cut-over-flag-file=/home/user1/gh-ost/game_tx2/flag.gh-ost.database1.TRANSACTION2.postpone.flag
2016-10-06 03:54:25 INFO starting gh-ost 1.0.20
2016-10-06 03:54:25 INFO Migrating `database1`.`TRANSACTION2`
2016-10-06 03:54:25 INFO connection validated on 127.0.0.1:3306
2016-10-06 03:54:25 INFO User has ALL privileges
2016-10-06 03:54:25 INFO binary logs validated on 127.0.0.1:3306
2016-10-06 03:54:25 INFO Restarting replication on 127.0.0.1:3306 to make sure binlog settings apply to replication thread
2016-10-06 03:54:26 INFO Table found. Engine=InnoDB
2016-10-06 03:54:54 INFO Found foreign key on `database1`.`ALARM_LOG` related to `database1`.`TRANSACTION2`
2016-10-06 03:54:54 INFO Found foreign key on `database1`.`TRANSACTION2` related to `database1`.`TRANSACTION2`
2016-10-06 03:54:54 INFO Found foreign key on `database1`.`TRANSACTION2` related to `database1`.`TRANSACTION2`
2016-10-06 03:54:54 INFO Found foreign key on `database1`.`TRANSACTION2` related to `database1`.`TRANSACTION2`
2016-10-06 03:54:54 INFO Found foreign key on `database1`.`UNKNOWN_WIN` related to `database1`.`TRANSACTION2`
2016-10-06 03:54:54 ERROR Found 5 foreign keys related to `database1`.`TRANSACTION2`. Foreign keys are not supported. Bailing out
2016-10-06 03:54:54 FATAL 2016-10-06 03:54:54 ERROR Found 5 foreign keys related to `database1`.`TRANSACTION2`. Foreign keys are not supported. Bailing out

发表评论

电子邮件地址不会被公开。 必填项已用*标注