| Bug #111888 | 'alter table xxx import tablespace' lead server crash | ||
|---|---|---|---|
| Submitted: | 26 Jul 2023 1:55 | Modified: | 26 Jul 2023 9:58 |
| Reporter: | gang liu | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S1 (Critical) |
| Version: | 8.0.32, 8.0.34 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution, IMPORT TABLESPACE | ||
[26 Jul 2023 9:58]
MySQL Verification Team
Hello gang liu, Thank you for the report and test case. Verified as described. regards, Umesh
[26 Jul 2023 10:00]
MySQL Verification Team
- 8.0.34 ( apply patch on top of 8.0.34 src). scl enable gcc-toolset-12 bash MYSQL_VERSION="Bug111888" TARGET=/export/home/tmp/ushastry/src/$MYSQL_VERSION rm -rf /export/home/tmp/ushastry/src/$MYSQL_VERSION rm -rf bld/ mkdir bld && cd bld rm -rf CMakeCache.txt cmake .. -DBUILD_CONFIG=mysql_release \ -DCMAKE_INSTALL_PREFIX=$TARGET \ -DWITH_BOOST=../boost \ -DCOMPILATION_COMMENT=`date +"%m-%d-%Y"` \ -DCMAKE_C_COMPILER=/opt/rh/gcc-toolset-12/root/usr/bin/gcc \ -DCMAKE_CXX_COMPILER=/opt/rh/gcc-toolset-12/root/usr/bin/g++ \ -DWITH_DEBUG=1 make -j128 make install cd $TARGET ./mtr bug111888 Logging: ./mtr bug111888 MySQL Version 8.0.34 Checking supported features - Binaries are debug compiled Using 'all' suites Collecting tests Removing old var directory Creating var directory '/export/home/tmp/ushastry/src/Bug111888/mysql-test/var' Installing system database Using parallel: 1 ============================================================================== TEST NAME RESULT TIME (ms) COMMENT ------------------------------------------------------------------------------ [ 50%] main.bug111888 [ fail ] . 2023-07-26T09:56:40.783814Z 8 [Note] [MY-012773] [InnoDB] Importing tablespace for table 'test/t1' that was exported from host 'ellex07' 2023-07-26T09:56:40.784018Z 8 [ERROR] [MY-012095] [InnoDB] [FATAL] Tablespace id is 7 in the data dictionary but in file ./test/t2.ibd it is 6! 2023-07-26T09:56:40.784058Z 8 [ERROR] [MY-013183] [InnoDB] Assertion failure: fil0fil.cc:2535:ib::fatal triggered thread 281466860072640 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 2023-07-26T09:56:40Z UTC - mysqld got signal 6 ; Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware. BuildID[sha1]=ce1a2157f52b1455b8c36632be0c4b39599aaf36 Thread pointer: 0xfffd70001040 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = fffe1c35e5c0 thread_stack 0x100000 /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2c) [0x49b2d74] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(print_fatal_signal(int)+0x3b4) [0x36888ec] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(my_server_abort()+0x64) [0x3688b74] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(my_abort()+0x18) [0x49a875c] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x1b4) [0x4db734c] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(ib::fatal::~fatal()+0) [0x4dc3cb4] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(Fil_shard::get_file_size(fil_node_t*, bool)+0x638) [0x4fe53a8] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(Fil_shard::open_file(fil_node_t*)+0x7f4) [0x4fe667c] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(Fil_shard::prepare_file_for_io(fil_node_t*)+0xd8) [0x4ff10ac] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(Fil_shard::space_load(unsigned int)+0x160) [0x4fe7e40] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(fil_space_get_flags(unsigned int)+0x3c) [0x4fe8124] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(dict_sdi_create_idx_in_mem(unsigned int, bool, unsigned int, bool)+0x44) [0x4f2d09c] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(dd_table_open_on_id(unsigned long, THD*, MDL_ticket**, bool, bool)+0x198) [0x4f53dc4] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(dict_sdi_get_index(unsigned int)+0x28) [0x4f462f4] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(row_import::match_schema(THD*, dd::Table const*)+0x344) [0x4c52ef8] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(row_import_for_mysql(dict_table_t*, dd::Table*, row_prebuilt_t*)+0x50c) [0x4c5b410] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(ha_innobase::discard_or_import_tablespace(bool, dd::Table*)+0x498) [0x4a309fc] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(handler::ha_discard_or_import_tablespace(bool, dd::Table*)+0x80) [0x3822694] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(Sql_cmd_discard_import_tablespace::mysql_discard_or_import_tablespace(THD*, Table_ref*)+0x3c0) [0x353710c] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(Sql_cmd_discard_import_tablespace::execute(THD*)+0x1b4) [0x3b542a8] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(mysql_execute_command(THD*, bool)+0x4a28) [0x3469e30] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x6a8) [0x346bf70] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x122c) [0x3462f18] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld(do_command(THD*)+0x514) [0x3461304] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld() [0x3675010] /export/home/tmp/ushastry/src/Bug111888/bin/mysqld() [0x5216e68] /lib64/libpthread.so.0(+0x7908) [0xfffe2ecd7908] /lib64/libc.so.6(+0x2429c) [0xfffe2e34429c] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (fffd700bf7d0): ALTER TABLE t2 IMPORT TABLESPACE Connection ID (thread ID): 8 Status: NOT_KILLED
[26 Jul 2023 10:01]
MySQL Verification Team
--bt #0 0x0000fffe2ecdf17c in pthread_kill () from /lib64/libpthread.so.0 #1 0x00000000049b2e2c in my_write_core (sig=6) at /export/home/tmp/ushastry/src/mysql-8.0.34/mysys/stacktrace.cc:295 #2 0x0000000003688b08 in handle_fatal_signal (sig=6) at /export/home/tmp/ushastry/src/mysql-8.0.34/sql/signal_handler.cc:230 #3 <signal handler called> #4 0x0000fffe2e356a34 in raise () from /lib64/libc.so.6 #5 0x0000fffe2e340a2c in abort () from /lib64/libc.so.6 #6 0x0000000003688cbc in my_server_abort () at /export/home/tmp/ushastry/src/mysql-8.0.34/sql/signal_handler.cc:286 #7 0x00000000049a875c in my_abort () at /export/home/tmp/ushastry/src/mysql-8.0.34/mysys/my_init.cc:258 #8 0x0000000004db734c in ut_dbg_assertion_failed (expr=0x6e13d48 "ib::fatal triggered", file=0x6ee3928 "/export/home/tmp/ushastry/src/mysql-8.0.34/storage/innobase/fil/fil0fil.cc", line=2535) at /export/home/tmp/ushastry/src/mysql-8.0.34/storage/innobase/ut/ut0dbg.cc:99 #9 0x0000000004dc3cb4 in ib::fatal::~fatal (this=0xfffe1c358780, __in_chrg=<optimized out>) at /export/home/tmp/ushastry/src/mysql-8.0.34/storage/innobase/ut/ut0ut.cc:522 #10 0x0000000004fe53a8 in Fil_shard::get_file_size (this=0xfffe202c7230, file=0x46ce9990, read_only_mode=false) at /export/home/tmp/ushastry/src/mysql-8.0.34/storage/innobase/fil/fil0fil.cc:2535 #11 0x0000000004fe667c in Fil_shard::open_file (this=0xfffe202c7230, file=0x46ce9990) at /export/home/tmp/ushastry/src/mysql-8.0.34/storage/innobase/fil/fil0fil.cc:2944 #12 0x0000000004ff10ac in Fil_shard::prepare_file_for_io (this=0xfffe202c7230, file=0x46ce9990) at /export/home/tmp/ushastry/src/mysql-8.0.34/storage/innobase/fil/fil0fil.cc:7410 #13 0x0000000004fe7e40 in Fil_shard::space_load (this=0xfffe202c7230, space_id=7) at /export/home/tmp/ushastry/src/mysql-8.0.34/storage/innobase/fil/fil0fil.cc:3428 #14 0x0000000004fe8124 in fil_space_get_flags (space_id=7) at /export/home/tmp/ushastry/src/mysql-8.0.34/storage/innobase/fil/fil0fil.cc:3527 #15 0x0000000004f2d09c in dict_sdi_create_idx_in_mem (space=7, space_discarded=false, in_flags=0, is_create=false) at /export/home/tmp/ushastry/src/mysql-8.0.34/storage/innobase/dict/dict0crea.cc:653 #16 0x0000000004f53dc4 in dd_table_open_on_id (table_id=18446744069414584327, thd=0x0, mdl=0x0, dict_locked=true, check_corruption=true) at /export/home/tmp/ushastry/src/mysql-8.0.34/storage/innobase/dict/dict0dd.cc:714 #17 0x0000000004f462f4 in dict_sdi_get_index (tablespace_id=7) at /export/home/tmp/ushastry/src/mysql-8.0.34/storage/innobase/dict/dict0dict.cc:5774 #18 0x0000000004c52ef8 in row_import::match_schema (this=0xfffe1c35af98, thd=0xfffd70001040, dd_table=0xfffd709f1358) at /export/home/tmp/ushastry/src/mysql-8.0.34/storage/innobase/row/row0import.cc:1474 #19 0x0000000004c5b410 in row_import_for_mysql (table=0xfffd700252c8, table_def=0xfffd709f1358, prebuilt=0xfffd700dfc08) at /export/home/tmp/ushastry/src/mysql-8.0.34/storage/innobase/row/row0import.cc:4440 #20 0x0000000004a309fc in ha_innobase::discard_or_import_tablespace (this=0xfffd700c1810, discard=false, table_def=0xfffd709f1358) at /export/home/tmp/ushastry/src/mysql-8.0.34/storage/innobase/handler/ha_innodb.cc:15178 #21 0x0000000003822694 in handler::ha_discard_or_import_tablespace (this=0xfffd700c1810, discard=false, table_def=0xfffd709f1358) at /export/home/tmp/ushastry/src/mysql-8.0.34/sql/handler.cc:4900 #22 0x000000000353710c in Sql_cmd_discard_import_tablespace::mysql_discard_or_import_tablespace (this=0xfffd700c0a30, thd=0xfffd70001040, table_list=0xfffd700c03d8) at /export/home/tmp/ushastry/src/mysql-8.0.34/sql/sql_table.cc:11315 #23 0x0000000003b542a8 in Sql_cmd_discard_import_tablespace::execute (this=0xfffd700c0a30, thd=0xfffd70001040) at /export/home/tmp/ushastry/src/mysql-8.0.34/sql/sql_alter.cc:409 #24 0x0000000003469e30 in mysql_execute_command (thd=0xfffd70001040, first_level=true) at /export/home/tmp/ushastry/src/mysql-8.0.34/sql/sql_parse.cc:4719 #25 0x000000000346bf70 in dispatch_sql_command (thd=0xfffd70001040, parser_state=0xfffe1c35d4e0) at /export/home/tmp/ushastry/src/mysql-8.0.34/sql/sql_parse.cc:5368 #26 0x0000000003462f18 in dispatch_command (thd=0xfffd70001040, com_data=0xfffe1c35e590, command=COM_QUERY) at /export/home/tmp/ushastry/src/mysql-8.0.34/sql/sql_parse.cc:2054 #27 0x0000000003461304 in do_command (thd=0xfffd70001040) at /export/home/tmp/ushastry/src/mysql-8.0.34/sql/sql_parse.cc:1439 #28 0x0000000003675010 in handle_connection (arg=0x46efab10) at /export/home/tmp/ushastry/src/mysql-8.0.34/sql/conn_handler/connection_handler_per_thread.cc:302 #29 0x0000000005216e68 in pfs_spawn_thread (arg=0x46ff2460) at /export/home/tmp/ushastry/src/mysql-8.0.34/storage/perfschema/pfs.cc:3042 #30 0x0000fffe2ecd7908 in start_thread () from /lib64/libpthread.so.0 #31 0x0000fffe2e34429c in thread_start () from /lib64/libc.so.6
[25 Sep 2023 3:23]
phoenix Zhang
One fix is skip check for discard table when startup (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: discard_tablespace_core_at_startup.diff (text/x-patch), 3.67 KiB.
[25 Sep 2023 3:51]
MySQL Verification Team
Thank you for the Contribution. regards, Umesh
Description: alter table import tablespaces lead server crash. How to repeat: 1.I add debug code: ``` diff --git a/storage/innobase/trx/trx0trx.cc b/storage/innobase/trx/trx0trx.cc index 5655a5d9228..4994fb3f909 100644 --- a/storage/innobase/trx/trx0trx.cc +++ b/storage/innobase/trx/trx0trx.cc @@ -3068,6 +3068,9 @@ static void trx_prepare(trx_t *trx) { if (lsn > 0) { trx_flush_logs(trx, lsn); } + if (DBUG_EVALUATE_IF("insert_finish_prepare", true, false)) { + sleep(3600); + } } ``` 2.the mtr script: ``` let $MYSQLD_DATADIR = `SELECT @@datadir`; CREATE TABLE t1 (c1 VARCHAR(32), c2 VARCHAR(32), c3 VARCHAR(32)); CREATE TABLE t2 (c1 VARCHAR(32), c2 VARCHAR(32), c3 VARCHAR(32)); CREATE TABLE t3 (c1 VARCHAR(32), c2 VARCHAR(32), c3 VARCHAR(32)); ALTER table t2 discard tablespace ; FLUSH table t1 for export; --copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/test/t2.cfg --copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t2.ibd unlock tables; DROP TABLE t1; set debug='+d,insert_finish_prepare'; send INSERT INTO t3 VALUES ('Test Data -1', 'Test Data -2', 'Test Data -3'); --sleep 5 --echo # kill mysqld and start mysqld connect (con1,localhost,root,,); --let $_server_id= `SELECT @@server_id` --let $expect_file_name_s3= $MYSQLTEST_VARDIR/tmp/mysqld.$_server_id.expect --let $mysqld_pid_file=`SELECT @@GLOBAL.pid_file` --source include/expect_crash.inc exec kill -9 `cat $mysqld_pid_file`; --source include/start_mysqld.inc set debug='-d,insert_finish_prepare'; # mysqld instance core ALTER TABLE t2 IMPORT TABLESPACE; DROP TABLE t2; DROP TABLE t3; ``` Suggested fix: diff --git a/storage/innobase/dict/dict0dd.cc b/storage/innobase/dict/dict0dd.cc index 1a932b7b74a..a89a60a5c1d 100644 --- a/storage/innobase/dict/dict0dd.cc +++ b/storage/innobase/dict/dict0dd.cc @@ -7068,6 +7068,11 @@ bool dd_tablespace_update_cache(THD *thd) { break; } + dd_space_states state = dd_tablespace_get_state_enum(&p, id); + if (state == DD_SPACE_STATE_DISCARDED) { + continue; + } + const char *space_name = t->name().c_str(); fil_space_t *space = fil_space_get(id);