[MDEV-18127] Handling DuplicateKey error while doing Online DDL Created: 2019-01-03  Updated: 2020-02-20

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Minor
Reporter: Søren Kröger Assignee: Marko Mäkelä
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-16329 Engine-independent online ALTER TABLE Closed

 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.



 Comments   
Comment by Marko Mäkelä [ 2019-01-17 ]

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-16329 design (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-16329 in 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.)

Comment by Søren Kröger [ 2019-01-23 ]

Thanks a lot for taking this up! Good to know you are working on it . Kind regards, Søren

Generated at Thu Feb 08 08:41:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.