Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
None
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
- blocks
-
MDEV-16417 Store Foreign Key metadata outside of InnoDB
- In Review
-
MDEV-21652 FK migration from old version
- In Review
-
MDEV-25525 AddressSanitizer: use-after-poison in row_sel_convert_mysql_key_to_innobase
- Closed
- causes
-
MDEV-25854 Restoring a backup may result in garbage intermediate tables from DDL
- Closed
-
MDEV-26936 Recovery crash on rolling back DELETE FROM SYS_INDEXES
- Closed
- is blocked by
-
MDEV-24589 DROP TABLE is not crash-safe
- Closed
-
MDEV-25312 Remove fil_space_t::name and related code
- Closed
- is part of
-
MDEV-17567 Atomic DDL
- Closed
- relates to
-
MDEV-25854 Restoring a backup may result in garbage intermediate tables from DDL
- Closed
-
MDEV-25886 CHECK TABLE crashes due to DB_MISSING_HISTORY in innodb_read_only mode
- Closed
-
MDEV-25910 Incorrect crash recovery of ALTER TABLE...ALGORITHM=COPY
- Closed
-
MDEV-28864 Assertion `trx_id <= create_id' failed in innodb_check_version()
- Closed
-
MDEV-29230 atomic.alter_table fails with "file not found" warnings on delete
- Closed
-
MDEV-31371 Table is lost after the server was killed during the cleanup of failed ALTER TABLE
- Confirmed
-
MDEV-32103 InnoDB ALTER TABLE is not crash safe
- Closed
-
MDEV-33489 The test atomic.alter_table occasionally hangs
- Open
-
MDEV-4259 transactional DDL
- Open
-
MDEV-25304 Atomic ALTER TABLE: MyRocks support
- Closed
-
MDEV-27618 Atomic DDL is not very atomic on partitioned tables
- Confirmed