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

Atomic ALTER TABLE

    XMLWordPrintable

    Details

      Description

      The purpose of this task is to ensure that when one executes an ALTER TABLE and there is crash (mariadbd or server) during the ALTER TABLE operation, either the new table will be deleted or the original restored. Any temporary tables or files left over from the ALTER TABLE should be deleted. This should work with all storage engines.
      This task will also ensure that if the new table is used, triggers are updated (in case of RENAME) and the binary log will contain the ALTER TABLE statement.

      Before this task, what may happen the mariadbd server would be happen during the ALTER TABLE operation:

      • Left over #sql-alter or #sql-backup files that needs to be deleted manually.
        (InnoDB does on startup delete all #sql-... InnoDB files, but this is not true for other storage engines)
      • There is a tiny window at the last phase of the ALTER TABLE, when a crash can have more severe consequences:
      • The .frm table may not match the installed table. (This can be fixed by a manual rename of
        a #sql- file to the original file).
      • The original table is (partly) renamed to a backup table or the temporary table is partly renamed to the original table and one has to manually fix it after a crash.
      • The table was altered but the binary log was not updated.

      This task is more complex than the other Atomic DDL operations as there is many different internal ALTER TABLE operations that uses different code paths:

      • ALTER TABLE that does not affect storage engines (like adding a comment)
      • ALTER TABLE that can be done with copy, be online or instant
      • ALTER TABLE ... ENGINE=...
      • ALTER TABLE ... RENAME (to be tested with all the above combinations). In this case we must
        also ensure that triggers are properly renamed if the new table is being used.
        The code must also be tested with different storage engines, as different storage engines can
        cause different code sequences to be executed.

      Some challenges with this task:

      • Testing all ALTER TABLE combinations
      • The DDL log consists of blocks of 4K and can thus not store a query > 4K.
        This should be fixed by splitting long queries over multiple blocks.
      • Rename of table name trigger can fail at the very end of alter table, which causes problems for inplace alter table as the table is already changed. This is fixed by adding Table_triggers_list::prepare_for_rename() to check if triggers can be renamed before we start the main alter table.

      Some things to be done later:

      • Check that alter of partitioned tables (both alter table and alter ...partition) is atomic.
      • At least ALTER ... PARTITION is not atomic when it comes to update binary log, if there
        is crash between the commit of ALTER and writing to binary log.
      • Ensure that ddl crash recovery works if we get at crash in middle of Table_triggers_list::change_table_name()
      • S3 engine needs to have ha_s3->rename_table() atomic or at least recoverable & repeatable. This means that
        if we have a crash in middle of rename_table, executing rename_table again during recovery should be able to
        either repeat or continue from where the previous stopped.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              monty Michael Widenius
              Reporter:
              monty Michael Widenius
              Votes:
              3 Vote for this issue
              Watchers:
              10 Start watching this issue

                Dates

                Due:
                Created:
                Updated:
                Resolved: