Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-18127

Handling DuplicateKey error while doing Online DDL

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

          Activity

            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.)

            marko Marko Mäkelä added a comment - 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.)

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

            sbktrifork Søren Kröger added a comment - Thanks a lot for taking this up! Good to know you are working on it . Kind regards, Søren

            People

              marko Marko Mäkelä
              sbktrifork Søren Kröger
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.