commit a367551d53105b497c53d75ddd70919247857bc1 Author: songhuaxiong.shx Date: Thu Apr 11 12:33:19 2024 +0800 [Bugfix] to #49393741 Skip orphan tmp table when upgrade 57 to 80 Background ========== In the instance of version 5.6, the frm file and ibd file prefixed with #sql will be generated in the tmp directory due to operations such as ddl residue, and this part of information will be recorded in the system table. If the instance is upgraded to version 5.7 with residual #sql files, 5.7 will delete the ibd and frm files prefixed with #sql, but the records in the system table are not deleted at this time, and the 5.7 instance can run normally. The remnants of the #sql table could be found in information_schema. During the upgrade process from 5.7 to 8.0, the records in the system table will be checked one by one, and it is found that the corresponding temporary tablespace in the system table does not exist, an error is reported, which eventually leads to the upgrade failure. Implementation ============== If table is orphan temporary table from 5.6, don't add it to new DD, just skip so that upgrade will not fail for it. diff --git a/mysql-test/r/bugfix_upgrade_rds_57_to_80_skip_orphan_tmp_table.result b/mysql-test/r/bugfix_upgrade_rds_57_to_80_skip_orphan_tmp_table.result new file mode 100644 index 00000000000..b4939b1a1cb --- /dev/null +++ b/mysql-test/r/bugfix_upgrade_rds_57_to_80_skip_orphan_tmp_table.result @@ -0,0 +1,51 @@ +# -------------------------------------------------------------------- +# [Bugfix] Skip orphan tmp table when upgrade 57 to 80 +# -------------------------------------------------------------------- + +# In datadir of 5.7, there are an orphan tmp table from 5.6, a normal +# table from 5.6 which named '#sql_not_tmp_from_56' and a table from 5.7 +# named '#sql_not_tmp_from_57'. We just skip the tmp table. + + +# 1) Parepre datadir for upgrading. + + +# 2) Stop DB server which was created by MTR default and restart server with datadir = data57. + + +# 3) Verify the orphan tmp table has been handled. +Pattern "Skip orphaned tablespace during upgrade" found + +# 4) Check information_schema. + +# 1. tables +select COUNT(*) from information_schema.innodb_tables where name like "%#sql%"; +COUNT(*) +0 + +# 2. tablespaces +select name from information_schema.innodb_tablespaces where name like "%#sql%"; +name +test/#sql_not_tmp_from_56 +test/#sql_not_tmp_from_57 + +# 3. datafiles +select space, path from information_schema.innodb_datafiles where path like "%#sql%"; +SPACE PATH + +# 4. indexes +select name, space from information_schema.innodb_indexes where (table_id in (select table_id from information_schema.innodb_tables where name like "%#sql%")) or (space in (select space from information_schema.innodb_tablespaces where name like "%#sql%")); +name space +GEN_CLUST_INDEX 31 +GEN_CLUST_INDEX 8 + +# 5. columns +select table_id, name from information_schema.innodb_columns where table_id in (select table_id from information_schema.innodb_tables where name like "%#sql%"); +table_id name + +# 6. fields +select index_id, name from information_schema.innodb_fields where index_id in (select index_id from information_schema.innodb_indexes where (table_id in (select table_id from information_schema.innodb_tables where name like "%#sql%")) or (space in (select space from information_schema.innodb_tablespaces where name like "%#sql%"))); +INDEX_ID NAME + +# 5) Transfer the current server to MTR server, remove data57. + diff --git a/mysql-test/std_data/data_57_skip_orphan_tmp_table.zip b/mysql-test/std_data/data_57_skip_orphan_tmp_table.zip new file mode 100644 index 00000000000..3d4c9f2c6f3 Binary files /dev/null and b/mysql-test/std_data/data_57_skip_orphan_tmp_table.zip differ diff --git a/mysql-test/t/bugfix_upgrade_rds_57_to_80_skip_orphan_tmp_table.test b/mysql-test/t/bugfix_upgrade_rds_57_to_80_skip_orphan_tmp_table.test new file mode 100644 index 00000000000..c8417d2a6ce --- /dev/null +++ b/mysql-test/t/bugfix_upgrade_rds_57_to_80_skip_orphan_tmp_table.test @@ -0,0 +1,67 @@ +--echo # -------------------------------------------------------------------- +--echo # [Bugfix] Skip orphan tmp table when upgrade 57 to 80 +--echo # -------------------------------------------------------------------- + +--echo +--echo # In datadir of 5.7, there are an orphan tmp table from 5.6, a normal +--echo # table from 5.6 which named '#sql_not_tmp_from_56' and a table from 5.7 +--echo # named '#sql_not_tmp_from_57'. We just skip the tmp table. +--echo + +--echo +--echo # 1) Parepre datadir for upgrading. +--echo +--let $MYSQLD_DATADIR1 = $MYSQL_TMP_DIR/data57/data +--file_exists $MYSQLTEST_VARDIR/std_data/data_57_skip_orphan_tmp_table.zip +--exec unzip -qo $MYSQLTEST_VARDIR/std_data/data_57_skip_orphan_tmp_table.zip -d $MYSQL_TMP_DIR/data57 + + +--echo +--echo # 2) Stop DB server which was created by MTR default and restart server with datadir = data57. +--echo +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--source include/shutdown_mysqld.inc +--let MYSQLD_LOG= $MYSQL_TMP_DIR/data57/error.log +--exec echo "restart: --datadir=$MYSQLD_DATADIR1 --log-error=$MYSQLD_LOG" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--enable_reconnect +--source include/wait_until_connected_again.inc + +--echo +--echo # 3) Verify the orphan tmp table has been handled. +# "the latter will be consistent with the former" should be in the error log. +--let SEARCH_FILE= $MYSQLD_LOG +--let SEARCH_PATTERN= Skip orphaned tablespace during upgrade +--source include/search_pattern.inc + +--echo +--echo # 4) Check information_schema. +--echo +--connection default +--echo # 1. tables +select COUNT(*) from information_schema.innodb_tables where name like "%#sql%"; +--echo +--echo # 2. tablespaces +select name from information_schema.innodb_tablespaces where name like "%#sql%"; +--echo +--echo # 3. datafiles +select space, path from information_schema.innodb_datafiles where path like "%#sql%"; +--echo +--echo # 4. indexes +select name, space from information_schema.innodb_indexes where (table_id in (select table_id from information_schema.innodb_tables where name like "%#sql%")) or (space in (select space from information_schema.innodb_tablespaces where name like "%#sql%")); +--echo +--echo # 5. columns +select table_id, name from information_schema.innodb_columns where table_id in (select table_id from information_schema.innodb_tables where name like "%#sql%"); +--echo +--echo # 6. fields +select index_id, name from information_schema.innodb_fields where index_id in (select index_id from information_schema.innodb_indexes where (table_id in (select table_id from information_schema.innodb_tables where name like "%#sql%")) or (space in (select space from information_schema.innodb_tablespaces where name like "%#sql%"))); + +--echo +--echo # 5) Transfer the current server to MTR server, remove data57. +--echo +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--shutdown_server +--source include/wait_until_disconnected.inc +--force-rmdir $MYSQL_TMP_DIR/data57 +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--enable_reconnect +--source include/wait_until_connected_again.inc diff --git a/share/messages_to_error_log.txt b/share/messages_to_error_log.txt index 591cfa912c1..b023a1ec30b 100644 --- a/share/messages_to_error_log.txt +++ b/share/messages_to_error_log.txt @@ -12288,6 +12288,9 @@ ER_GRP_RPL_MEMBER_INFO_DOES_NOT_EXIST ER_USAGE_DEPRECATION_COUNTER eng "Deprecated '%s' used %s times, last time used '%s'." +ER_DD_UPGRADE_SKIP_ORPHAN_TMP_TABLE + eng "Skip orphaned tablespace during upgrade, space_id: %u, filepath: %s." + # DO NOT add server-to-client messages here; # they go in messages_to_clients.txt # in the same directory as this file. diff --git a/storage/innobase/dict/dict0upgrade.cc b/storage/innobase/dict/dict0upgrade.cc index d18566c5d74..50ec612a11f 100644 --- a/storage/innobase/dict/dict0upgrade.cc +++ b/storage/innobase/dict/dict0upgrade.cc @@ -1187,6 +1187,19 @@ int dd_upgrade_tablespace(THD *thd) { ut::free(filename); filename = nullptr; + /* If table is orphan temporary table from 5.6, don't add it to new DD, just + skip so that upgrade will not fail for it . */ + if (orig_name.find("/#sql") != std::string::npos) { + ib::warn(ER_DD_UPGRADE_SKIP_ORPHAN_TMP_TABLE, space, orig_name.c_str()); + + mem_heap_empty(heap); + + /* Get the next record */ + dict_sys_mutex_enter(); + mtr_start(&mtr); + continue; + } + /* To migrate statistics from 57 satistics tables, we rename the 5.7 statistics tables/tablespaces so that it doesn't conflict with statistics table names in 8.0 */