Note: I did look up other answers, but they all seem to be for MySQL 5.x. MySQL 8 has made huge strides in ease of altering tables so I am looking for mySQL 8 specific answers.
I'm using MySQL 8.0.36
I need to add a new column to a large table that must be unique (null OK) and that has an index on that column.
I thought, yeah!, MySQL 8 now has ALGORITHM=INSTANT for adding columns. However, ALGORITHM=INSTANT isn't accepted for making the column unique or adding an index. So I did:
ALTER TABLE mytable ADD COLUMN new_col CHAR(20), ALGORITHM=INSTANT;
As promised, it was instant. 0.97 seconds.
Then:
CREATE INDEX xx ON mytable(new_col) ALGORITHM=INPLACE LOCK=NONE;
That took 7 minutes, but didn't lock the database. So OK.
However, lock=None isn't accepted for unique constraint. It gives me a syntax error. I'd have to do:
ALTER TABLE mytable ADD UNIQUE (new_col), ALGORITHM=INPLACE;
I'm afraid to do that. I can't lock my production table for 7 minutes or however long it's going to take.
Am I missing something? MySQL 8 adding this powerful instant algorithm for adding columns, but I still can't do what I need.
My work-around, which I've used in the past, was to create a new table with the new column and join in my queries. That gets messy!
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html says:
When you create a UNIQUE or PRIMARY KEY index, MySQL must do some extra work. For UNIQUE indexes, MySQL checks that the table contains no duplicate values for the key. For a PRIMARY KEY index, MySQL also checks that none of the PRIMARY KEY columns contains a NULL.
I infer that this means that while it's checking that the table contains no duplicate values for the key, it must block DML changes, because that would create a race condition.
You could use one of the community online schema change tools like pt-online-schema-change or gh-ost to avoid the blocking. But this creates another risk, i.e. what happens if there are duplicates in the column as you're creating a unique index? Should the OSC fail immediately, or should it skip the row (this happens in pt-osc for example), or should it null out the duplicate value, or some other behavior?