[MDEV-25180] Atomic ALTER TABLE Created: 2021-03-17  Updated: 2023-09-07  Due: 2021-04-29  Resolved: 2021-05-20

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Fix Version/s: 10.6.1

Type: Task Priority: Critical
Reporter: Michael Widenius Assignee: Michael Widenius
Resolution: Fixed Votes: 3
Labels: None

Attachments: Text File mariadb_crash_april.txt    
Issue Links:
Blocks
blocks MDEV-16417 Store Foreign Key metadata outside of... In Review
blocks MDEV-21652 FK migration from old version Stalled
blocks MDEV-25525 AddressSanitizer: use-after-poison in... Closed
is blocked by MDEV-24589 DROP TABLE is not crash-safe Closed
is blocked by MDEV-25312 Remove fil_space_t::name and related ... Closed
PartOf
is part of MDEV-17567 Atomic DDL Closed
Problem/Incident
causes MDEV-25854 Restoring a backup may result in garb... Closed
causes MDEV-26936 Recovery crash on rolling back DELETE... Closed
Relates
relates to MDEV-25854 Restoring a backup may result in garb... Closed
relates to MDEV-25886 CHECK TABLE crashes due to DB_MISSING... Closed
relates to MDEV-25910 Incorrect crash recovery of ALTER TAB... Closed
relates to MDEV-28864 Assertion `trx_id <= create_id' faile... Closed
relates to MDEV-29230 atomic.alter_table fails with "file n... Closed
relates to MDEV-31371 Table is lost after the server was ki... Confirmed
relates to MDEV-32103 InnoDB ALTER TABLE is not crash safe Closed
relates to MDEV-4259 transactional DDL Open
relates to MDEV-25304 Atomic ALTER TABLE: MyRocks support Closed
relates to MDEV-27618 Atomic DDL is not very atomic on part... Open

 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.

 Comments   
Comment by Michael Widenius [ 2021-03-23 ]

Current state is that sql level atomic alter table code is more or less complete.
Now waiting for updates to InnoDB and MyRocks to support handlerton->check_version() so that we have inplace alter table covered for these engines.

Comment by Marko Mäkelä [ 2021-03-24 ]

The InnoDB part is almost there, but we have some test failures, and the whole branch needs to be rebased to 10.6 for extensive stress testing.

Comment by Marko Mäkelä [ 2021-03-24 ]

The InnoDB part depends on MDEV-24589, which will require some further work, because some failures were found during stress testing.

Comment by Marko Mäkelä [ 2021-04-14 ]

There are problems with the InnoDB dict_table_t::def_trx_id logic.

Comment by Marko Mäkelä [ 2021-04-20 ]

I think that the InnoDB part is completed now.

Comment by Elena Stepanova [ 2021-04-23 ]

Issue 1: Assertion `query_arg' failed

bb-10.6-monty 8a94dabc9c

 
mysqld: /home/mdbe/atomic_ddl/bb-10.6-monty/sql/sql_class.cc:7417: int THD::binlog_query(THD::enum_binlog_query_type, const char*, ulong, bool, bool, bool, int): Assertion `query_arg' failed.
 
210423  0:17:57 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007f776dbea502 in __GI___assert_fail (assertion=0x5652b9fa7b0c "query_arg", file=0x5652b9fa5ee8 "/home/mdbe/atomic_ddl/bb-10.6-monty/sql/sql_class.cc", line=7417, function=0x5652b9fa9f00 <THD::binlog_query(THD::enum_binlog_query_type, char const*, unsigned long, bool, bool, bool, int)::__PRETTY_FUNCTION__> "int THD::binlog_query(THD::enum_binlog_query_type, const char*, ulong, bool, bool, bool, int)") at assert.c:101
 
#8  0x00005652b91a7faf in THD::binlog_query (this=0x7f771c000d78, qtype=THD::STMT_QUERY_TYPE, query_arg=0x0, query_len=0, is_trans=false, direct=false, suppress_use=false, errcode=0) at /home/mdbe/atomic_ddl/bb-10.6-monty/sql/sql_class.cc:7417
 
#9  0x00005652b9304f05 in write_bin_log (thd=0x7f771c000d78, clear_error=true, query=0x0, query_length=0, is_trans=false) at /home/mdbe/atomic_ddl/bb-10.6-monty/sql/sql_table.cc:893
 
#10 0x00005652b932b9dc in mysql_create_or_drop_trigger (thd=0x7f771c000d78, tables=0x7f771c017f28, create=false) at /home/mdbe/atomic_ddl/bb-10.6-monty/sql/sql_trigger.cc:666
 
#11 0x00005652b921fca0 in mysql_execute_command (thd=0x7f771c000d78) at /home/mdbe/atomic_ddl/bb-10.6-monty/sql/sql_parse.cc:5819
 
#12 0x00005652b9226785 in mysql_parse (thd=0x7f771c000d78, rawbuf=0x7f771c017db0 "DROP /* QNO 140 CON_ID 24 */ TRIGGER IF EXISTS trg5", length=51, parser_state=0x7f77680fc510) at /home/mdbe/atomic_ddl/bb-10.6-monty/sql/sql_parse.cc:8019
 
#13 0x00005652b9212b93 in dispatch_command (command=COM_QUERY, thd=0x7f771c000d78, packet=0x7f771c0782d9 "DROP /* QNO 140 CON_ID 24 */ TRIGGER IF EXISTS trg5", packet_length=51, blocking=true) at /home/mdbe/atomic_ddl/bb-10.6-monty/sql/sql_parse.cc:1897
 
#14 0x00005652b9211469 in do_command (thd=0x7f771c000d78, blocking=true) at /home/mdbe/atomic_ddl/bb-10.6-monty/sql/sql_parse.cc:1406
 
#15 0x00005652b93c7bd4 in do_handle_one_connection (connect=0x5652bbf98cb8, put_in_cache=true) at /home/mdbe/atomic_ddl/bb-10.6-monty/sql/sql_connect.cc:1410
 
#16 0x00005652b93c7936 in handle_one_connection (arg=0x5652bc28d118) at /home/mdbe/atomic_ddl/bb-10.6-monty/sql/sql_connect.cc:1312
 
#17 0x00005652b991ab87 in pfs_spawn_thread (arg=0x5652bc2e2d28) at /home/mdbe/atomic_ddl/bb-10.6-monty/storage/perfschema/pfs.cc:2201
 
#18 0x00007f776eaf56db in start_thread (arg=0x7f77680fd700) at pthread_create.c:463
 
#19 0x00007f776dcdb71f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Comment by Marko Mäkelä [ 2021-05-06 ]

With 10.6 2ceadb390309f9c29786bb5ec2a6459dfe88344e merged, and some workarounds removed, we seem to have a race condition outside InnoDB:

bb-10.6-monty-innodb f179fdefd1d8b3c8b3d4e7961dca0144fb608c4e

 
innodb_fts.crash_recovery 'innodb,release' w13 [ 10 fail ]
 
        Test ended at 2021-05-06 16:59:34
 
 
 
CURRENT_TEST: innodb_fts.crash_recovery
 
--- /mariadb/bb-10.6-monty/mysql-test/suite/innodb_fts/r/crash_recovery.result	2021-05-06 16:46:03.178294359 +0300
 
+++ /mariadb/bb-10.6-monty/mysql-test/suite/innodb_fts/r/crash_recovery.reject	2021-05-06 16:59:33.960017763 +0300
 
@@ -154,3 +154,15 @@
 
 SELECT * FROM information_schema.innodb_sys_tables
 
 WHERE name LIKE 'test/%' AND name NOT LIKE 'test/#sql-ib%';
 
 TABLE_ID	NAME	FLAG	N_COLS	SPACE	ROW_FORMAT	ZIP_PAGE_SIZE	SPACE_TYPE
 
+72	test/#sql-alter-1227ba-6	33	6	59	Dynamic	0	Single
 
+73	test/FTS_0000000000000048_0000000000000057_INDEX_1	33	8	60	Dynamic	0	Single
 
+74	test/FTS_0000000000000048_0000000000000057_INDEX_2	33	8	61	Dynamic	0	Single
 
+75	test/FTS_0000000000000048_0000000000000057_INDEX_3	33	8	62	Dynamic	0	Single
 
+76	test/FTS_0000000000000048_0000000000000057_INDEX_4	33	8	63	Dynamic	0	Single
 
+77	test/FTS_0000000000000048_0000000000000057_INDEX_5	33	8	64	Dynamic	0	Single
 
+78	test/FTS_0000000000000048_0000000000000057_INDEX_6	33	8	65	Dynamic	0	Single
 
+79	test/FTS_0000000000000048_BEING_DELETED	33	4	66	Dynamic0Single
 
+80	test/FTS_0000000000000048_BEING_DELETED_CACHE	33	4	67	Dynamic	0	Single
 
+81	test/FTS_0000000000000048_CONFIG	33	5	68	Dynamic0Single
 
+82	test/FTS_0000000000000048_DELETED	33	4	69	Dynamic0Single
 
+83	test/FTS_0000000000000048_DELETED_CACHE	33	4	70	Dynamic0Single
 
 
 
mysqltest: Result length mismatch

Note: In 10.6, the test contains additional work-arounds, such as this one:

# The server may be killed at any time of executing ALTER TABLE on t1,t2,t3.
 
# Remove possible #sql- tables left behind by ALTER TABLE t2 or ALTER TABLE t3.
 
DELIMITER $$;
 
BEGIN NOT ATOMIC
 
  DECLARE c TEXT DEFAULT
 
  (SELECT GROUP_CONCAT(CONCAT('DROP TABLE `#mysql50#',SUBSTR(name,6),'`;'))
 
   FROM information_schema.innodb_sys_tables
 
   WHERE name LIKE 'test/#sql-alter-%' OR name LIKE 'test/#sql-backup-%');
 
  IF c IS NOT NULL THEN EXECUTE IMMEDIATE c; END IF;
 
END;
 
$$
 
DELIMITER ;$$

The problem ought to be that the recovery of ALTER TABLE…ALGORITHM=COPY fails to delete a table that had been created inside InnoDB. The InnoDB internal FTS_ tables are used by the FULLTEXT INDEX in that table. This failure is somewhat difficult to repeat, and the server must be built with cmake -DCMAKE_BUILD_TYPE=RelWithDebInfo so that the server would be killed in the middle of the operation. Here is a simpler version of the test:

--source include/have_innodb.inc
 
FLUSH TABLES;
 
# Work around the lack of MDEV-24626.
 
call mtr.add_suppression("InnoDB: Cannot (read first page of|open datafile for read-only:) '\\./test/(FTS_|#sql-(alter|backup)-).*\\.ibd'");
 
call mtr.add_suppression("InnoDB: Datafile '\\./test/(FTS_|#sql-(alter|backup)-).*\\.ibd' is corrupted");
 
call mtr.add_suppression("InnoDB: (The error means|Operating system error)");
 
call mtr.add_suppression("InnoDB: If you are installing InnoDB");
 
call mtr.add_suppression("InnoDB: Ignoring tablespace for `test`\\.`(FTS_|#sql-(backup|alter)-).*` because it could not be opened\\.");
 
call mtr.add_suppression("InnoDB: Tablespace [1-9][0-9]* was not found at ./test/(FTS_|#sql-(alter|backup)-).*\\.ibd, and innodb_force_recovery was set");
 
call mtr.add_suppression("InnoDB: Corrupted page \\[page id: space=[1-9][0-9]*, page number=0\\] of datafile './test/(FTS_|#sql-(alter|backup)-).*\\.ibd' could not be found in the doublewrite buffer\\.");
 
 
 
--connect(ddl, localhost, root,,)
 
CREATE TABLE t3(a TEXT,b TEXT,FULLTEXT INDEX(a)) ENGINE=InnoDB;
 
send ALTER TABLE t3 DROP INDEX a, ADD FULLTEXT INDEX(b), ALGORITHM=COPY;
 
--connection default
 
--let $shutdown_timeout=0
 
--let $restart_parameters=--innodb-force-recovery=1
 
--source include/restart_mysqld.inc
 
--disconnect ddl
 
DROP TABLE t3;

Run the test like this:

./mtr --parallel=auto --repeat=10 test_name{,,,,,,,,,,,,,,,,,,,,,,,,,}

Failures will look like this:

mysqltest: ... Done processing test commands.
 
--- /dev/shm/bb-10.6-monty-32/mysql-test/var/4/tmp/check-mysqld_1.result	2021-05-06 17:23:38.686110870 +0300
 
+++ /dev/shm/bb-10.6-monty-32/mysql-test/var/4/tmp/check-mysqld_1.reject	2021-05-06 17:23:39.686123892 +0300
 
@@ -976,3 +976,4 @@
 
 THREAD_POOL_WAITS	1.0	DISABLED	INFORMATION SCHEMA	100601.0NULL	NULL	Vladislav Vaintroub	Provides wait counters for threadpool.	GPL	OFF	Stable	1.0
 
 partition	1.0	DISABLED	STORAGE ENGINE	100601.0	NULL	NULL	Mikael Ronstrom, MySQL AB	Partition Storage Engine Helper	GPL	OFF	Stable	1.0
 
 VARIABLE_NAME	VARIABLE_VALUE
 
+#sql-alter-2ea069-8.ibd
 
 
 
mysqltest: Result length mismatch
 
 
 
not ok

Generated at Thu Feb 08 09:35:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.