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
-
- Stalled
-
-
MDEV-25525 AddressSanitizer: use-after-poison in row_sel_convert_mysql_key_to_innobase
-
- Closed
-
- causes
-
-
- Closed
-
-
-
- Closed
-
- is blocked by
-
-
- Closed
-
-
-
- Closed
-
- is part of
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Confirmed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Confirmed
-
(2 is blocked by, 1 is part of, 11 relates to)
Activity
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.
The InnoDB part depends on MDEV-24589, which will require some further work, because some failures were found during stress testing.
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
|
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
|
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.