Status: Open (View Workflow)
Fix Version/s: None
I'm sorry - I know this limitation has been discussed several times before - but I simply don't see the point of letting a online DDL fail when some client is generating a duplicate key error during a online DDL operation?
The limitation documented on https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-limitations.html
When running an online DDL operation, the thread that runs the ALTER TABLE statement applies an online log of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the online log. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction.
As I understand, the online DDL process is applying the data from the online log on the temporary table ... obviously the online log currently contains all transactions no matter if they were applied to the original tabel successfully or not, which means if the transaction fails for a client during the DDL operation, it will also fail on the temporary table resulting the DDL operation to fail completely.
Couldn't the mysql code be changed only to keep successfully completed transactions in the online log? Kind of like the binary replication log for instance?
Our sitation is like this:
We have a "big" table, which contains about 80GB of data. When we do a online DDL, the process takes about 4-5 hours - during the 4 hours there is a fair chance that some client somehow hits a duplicate key error on that table. At the end, the online DDL fails after 4 hours and we have to try again.
I fail to see the point of failing the entire alter table operation because of a transaction which wasn't committed successfully on the original table in the first place.