Details
-
Task
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
None
-
None
-
None
Description
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.
Attachments
Issue Links
- relates to
-
MDEV-16329 Engine-independent online ALTER TABLE
-
- Closed
-
I designed and implemented the online table rebuild and online index creation in InnoDB, originally in MySQL 5.6, available in MariaDB since version 10.0.
I think that this is something that should be addressed in the
MDEV-16329design (when implementing ALTER ONLINE TABLE at the SQL layer). This would make the InnoDB online table rebuild code redundant. This is a strong reason for implementing the logging in such a way that only committed changes will be logged.With
MDEV-16329in place, the problem would still remain for ALTER TABLE…ADD UNIQUE INDEX except when , FORCE is added to request the table to be rebuilt. (It should still be more efficient to create indexes natively in the storage engine than to have the whole table rebuilt.)