4

I have a very large table, lets call it example, and I try to execute a little alter command on that table:

ALTER TABLE `example` ADD `example_column` TINYINT(1) NOT NULL DEFAULT 0;

Checking the progress of alter command:

mysql -e 'show engine innodb status \G' | grep 'undo \| log \| entries'

Give me nice info, about the time - more or less it will take 17 days to complete ...

Alter block the table, so blocking production table for 17 days is not a good option. ;)

I try to investigate some great tools on the net, like:

  1. Pt-online-schema-change - Percona
  2. gh-ost - GitHub's Online Schema Migrations for MySQL

I have also read the documentation, and the limitation section of the above tools:

gh-ost limitations

  • does not support triggers at all
  • does not support foreign keys at all

pt-online-schema-change limitations

  • The use of triggers means that the tool will not work if any triggers are already defined on the table.

  • Does not provide any good way of altering tables which contain foreign keys

My example table have triggers and foreign keys...

Could you give me some advice, how to deal with this alter?

I have MySQL 5.6. I use GTID (row-based) replication.

I will be very grateful for advice!

3

You are much better off running the ALTER TABLE like this

ALTER TABLE `example` ADD `example_column` TINYINT(1) DEFAULT NULL,
ALGORITHM=INPLACE, LOCK=NONE;

What could be the benefit ? Defining a column whose default value is NULL will not attempt to increase the size of the row. This is true for VARCHAR. Honestly, I'm not sure this is true for numeric. (INT, TINYINT, etc). You need to test this (I mentioned testing this 2 years ago (Does InnoDB allow adding columns to a table with concurrent READ/WRITE of rows?)). Consequently, there should be no downtime.

The downside to this would be changing your software to deal with a NULL numeric column.

If you concerned about foreign keys, turn them off in your session beforehand

SET foreign_key_checks = 0;
ALTER TABLE `example` ADD `example_column` TINYINT(1) DEFAULT NULL,
ALGORITHM=INPLACE, LOCK=NONE;

If you have triggers and can live without it for the duration of the load, drop the trigger, do the ALTER TABLE, and recreate the trigger.

CAVEAT: Make sure you are using the latest version of MySQL 5.6 (at least 5.6.23) to avoid foreign key issues with ALTER TABLE.

Please note that I did not mention using third party tools. This is just straight up MySQL.

GIVE IT A TRY !!!

  • I really appreciate your answer, I can try to execute the alter command that you provided in maintenance window on stage machine (for start). To examine the result. Should I stop the replication before executing the alter command? – crashoverbike Aug 1 '17 at 14:19
  • I did, and the result on stage is: (8 hours 3 min 3.33 sec). Thank you! :) I wanted to mark you answer as good, but I don't have enough points here to do this. And I'm still using "NOT NULL DEFAULT 0" in the alter, because I don't want to have 'DEFAULT NULL'. – crashoverbike Aug 4 '17 at 9:03
0

How about zero downtime, no issues with triggers or FKs or replication, etc?

OK, there is a catch. You would be creating another table to contain the PK and the new column(s). And queries that need the new column(s) would need to do a JOIN. But, in some cases, this is not a serious drawback.

And, the JOIN would usually need to be LEFT JOIN so that you get NULL when there is (not yet) a value for the new column(s).

Create the new table with the same PK column(s) as the original table, but without AUTO_INCREMENT. Have the new column(s) in the new table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.