commit b3047609f497c477aec04c166ac577d631c116f7 Author: songhuaxiong.shx Date: Thu Feb 23 13:52:24 2023 +0800 Background ========== In MySQL, the progress of upgrading from 5.7 to 8.0 will fail due to the existence of invalid string in comment of table, field and index. In most case, invalid string in comment is allowed to be cleared, which means that the upgrade process should continue insensitively. User Interface ============== upgrade_clear_invalid_comment This variable is used to control if invalid character strings in comment of table, index and field are cleared during upgrading from 5.7 to 8.0. If true, the process will not fail for the invalid character. - Scope: GLOABL, READ_ONLY - Dynamic: NO - Type: Bool - Default: FALSE Implementation ============== When encountering invalid string in comments during upgrading from 5.7 to 8.0, if the variable "upgrade_clear_invalid_comment" is ON, the upgrade process will continue insensitively and the invalid string will be cleared automatically. The messages of invalid string and cleared action will be recorded in error log. diff --git a/mysql-test/r/bugfix_upgrade_57_to_80_clear_invalid_comment.result b/mysql-test/r/bugfix_upgrade_57_to_80_clear_invalid_comment.result new file mode 100644 index 00000000000..b52dc07d3f9 --- /dev/null +++ b/mysql-test/r/bugfix_upgrade_57_to_80_clear_invalid_comment.result @@ -0,0 +1,88 @@ +# --------------------------------------------------------- +# [Opt] Clear invalid comment string when upgrade 57 to 80 +# --------------------------------------------------------- + +# Stop DB server which was created by MTR default + +# Upgrade 5.7 data directory containing a table, index and column, and +# each having an invalid character string in their comments. +# - t1 has invalid comment in table. +# - t2 has invalid comment in field. +# - t3 has invalid comment in index. +# - t4 has invalid comments in table, field and index. +# Invalid comment also exist in t5(partition) and t6(subpartition), which +# has not effect on the upgrade process currently. Additional checks may +# be added in future. + + +# Upgrade fail and the messages of invalid comment will be recorded in MYSQLD_LOG_FALSE. +include/assert_grep.inc [Found invalid comment in table, field and index for 't4'.] +include/assert_grep.inc [Found invalid comment in table for 't1'.] +include/assert_grep.inc [Found invalid comment in field for 't2'.] +include/assert_grep.inc [Found invalid comment in index for 't3'.] +include/assert_grep.inc [Not found clear invalid comment in table for 't4'.] +include/assert_grep.inc [Not found clear invalid comment in field for 't4'.] +include/assert_grep.inc [Not found clear invalid comment in index for 't4'.] +include/assert_grep.inc [Not found clear invalid comment in table for 't1'.] +include/assert_grep.inc [Not found clear invalid comment in field for 't2'.] +include/assert_grep.inc [Not found clear invalid comment in index for 't3'.] +include/assert_grep.inc [Found suggest to set 'upgrade_clear_invalid_comment' to ON.] + +# Upgrade pass and the messages of clear operations will be recorded in MYSQLD_LOG_TRUE. +include/assert_grep.inc [Not found invalid comment in table, field and index for 't4'.] +include/assert_grep.inc [Not found invalid comment in table for 't1'.] +include/assert_grep.inc [Not found invalid comment in field for 't2'.] +include/assert_grep.inc [Not found invalid comment in index for 't3'.] +include/assert_grep.inc [Found clear invalid comment in table for 't4'.] +include/assert_grep.inc [Found clear invalid comment in field for 't4'.] +include/assert_grep.inc [Found clear invalid comment in index for 't4'.] +include/assert_grep.inc [Found clear invalid comment in table for 't1'.] +include/assert_grep.inc [Found clear invalid comment in field for 't2'.] +include/assert_grep.inc [Found clear invalid comment in index for 't3'.] +include/assert_grep.inc [Not found suggest to set 'upgrade_clear_invalid_comment' to ON.] + +# Show variable "upgrade_clear_invalid_comment". +SELECT @@GLOBAL.upgrade_clear_invalid_comment; +@@GLOBAL.upgrade_clear_invalid_comment +1 + +# Stop the server. + +# Start again by default, check if comments were cleared. + +USE test; +# The comment of t4 should have be cleared. +SHOW CREATE TABLE t4; +Table Create Table +t4 CREATE TABLE `t4` ( + `a` int DEFAULT NULL, + KEY `idx1` (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 + +# The comment of t1'table should have be cleared. +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 + +# The comment of t2'field should have be cleared. +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 + +# The comment of t3'index should have be cleared. +SHOW CREATE TABLE t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `a` int DEFAULT NULL, + KEY `idx1` (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 + +# Stop the server. + +# Cleanup. +# Restart the server with default options. +# restart diff --git a/mysql-test/std_data/upgrade/data_57_clear_invalid_comment_string.zip b/mysql-test/std_data/upgrade/data_57_clear_invalid_comment_string.zip new file mode 100644 index 00000000000..11bf7e72e00 Binary files /dev/null and b/mysql-test/std_data/upgrade/data_57_clear_invalid_comment_string.zip differ diff --git a/mysql-test/t/bugfix_upgrade_57_to_80_clear_invalid_comment.test b/mysql-test/t/bugfix_upgrade_57_to_80_clear_invalid_comment.test new file mode 100644 index 00000000000..667e654e5e7 --- /dev/null +++ b/mysql-test/t/bugfix_upgrade_57_to_80_clear_invalid_comment.test @@ -0,0 +1,198 @@ +--echo # --------------------------------------------------------- +--echo # [Opt] Clear invalid comment string when upgrade 57 to 80 +--echo # --------------------------------------------------------- + + +# 1) Show variable "upgrade_clear_invalid_comment". +if (`SELECT @@GLOBAL.upgrade_clear_invalid_comment != FALSE`) { + skip upgrade_clear_invalid_comment should be OFF be default.; +} + + +# 2) Prepare for upgrade. +--echo +--echo # Stop DB server which was created by MTR default +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--source include/shutdown_mysqld.inc + +--echo +--echo # Upgrade 5.7 data directory containing a table, index and column, and +--echo # each having an invalid character string in their comments. +--echo # - t1 has invalid comment in table. +--echo # - t2 has invalid comment in field. +--echo # - t3 has invalid comment in index. +--echo # - t4 has invalid comments in table, field and index. +--echo # Invalid comment also exist in t5(partition) and t6(subpartition), which +--echo # has not effect on the upgrade process currently. Additional checks may +--echo # be added in future. +--copy_file $MYSQLTEST_VARDIR/std_data/upgrade/data_57_clear_invalid_comment_string.zip $MYSQL_TMP_DIR/data_57_clear_invalid_comment_string.zip +--file_exists $MYSQL_TMP_DIR/data_57_clear_invalid_comment_string.zip +--exec unzip -qo $MYSQL_TMP_DIR/data_57_clear_invalid_comment_string.zip -d $MYSQL_TMP_DIR/data_57_clear_invalid_comment_string +--let $MYSQLD_DATADIR1= $MYSQL_TMP_DIR/data_57_clear_invalid_comment_string/db +--let $MYSQLD_LOG_UPGRADE_FALSE= $MYSQLTEST_VARDIR/log/data_57_clear_invalid_comment_string_1.log +--let $MYSQLD_LOG_UPGRADE_TRUE= $MYSQLTEST_VARDIR/log/data_57_clear_invalid_comment_string_2.log +--replace_result $MYSQLD MYSQLD $MYSQLD_DATADIR1 MYSQLD_DATADIR1 $MYSQLD_LOG_UPGRADE_FALSE MYSQLD_LOG + + +# 3) Start with upgrade_clear_invalid_comment = OFF, upgrade should fail. +--echo +--error 1 +--exec $MYSQLD --no-defaults $extra_args --innodb_dedicated_server=OFF --secure-file-priv="" --log-error=$MYSQLD_LOG_UPGRADE_FALSE --datadir=$MYSQLD_DATADIR1 + + +# 4) Start with upgrade_clear_invalid_comment = ON, upgrade should pass. +--replace_result $MYSQLD MYSQLD $MYSQLD_DATADIR1 MYSQLD_DATADIR1 $MYSQLD_LOG_UPGRADE_TRUE MYSQLD_LOG +--enable_reconnect +--exec echo "restart: --log-error=$MYSQLD_LOG_UPGRADE_TRUE --datadir=$MYSQLD_DATADIR1 --upgrade_clear_invalid_comment=ON" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--source include/wait_until_connected_again.inc + +# 5) Check messages in error-log. +--echo +--echo # Upgrade fail and the messages of invalid comment will be recorded in MYSQLD_LOG_FALSE. +--let $assert_file = $MYSQLD_LOG_UPGRADE_FALSE +--let $assert_count = 1 +--let $assert_select = Comment for table 'test\.t4' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'\. +--let $assert_text = Found invalid comment in table, field and index for 't4'. +--source include/assert_grep.inc + +--let $assert_select = Comment for table 'test\.t1' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'\. +--let $assert_text = Found invalid comment in table for 't1'. +--source include/assert_grep.inc + +--let $assert_select = Comment for field 'test\.t2\.a' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'\. +--let $assert_text = Found invalid comment in field for 't2'. +--source include/assert_grep.inc + +--let $assert_select = Comment for index 'test\.t3\.idx1' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'\. +--let $assert_text = Found invalid comment in index for 't3'. +--source include/assert_grep.inc + +--let $assert_count = 0 +--let $assert_select = Clear utf8mb3 character strings: '\\\xF0\\\x9F\\\x90' in comment of table 'test\.t4'\. +--let $assert_text = Not found clear invalid comment in table for 't4'. +--source include/assert_grep.inc + +--let $assert_select = Clear utf8mb3 character strings: '\\\xF0\\\x9F\\\x90' in comment of field 'test\.t4\.a'\. +--let $assert_text = Not found clear invalid comment in field for 't4'. +--source include/assert_grep.inc + +--let $assert_select = Clear utf8mb3 character strings: '\\\xF0\\\x9F\\\x90' in comment of index 'test\.t4\.idx1'\. +--let $assert_text = Not found clear invalid comment in index for 't4'. +--source include/assert_grep.inc + +--let $assert_select = Clear utf8mb3 character strings: '\\\xF0\\\x9F\\\x90' in comment of table 'test\.t1'\. +--let $assert_text = Not found clear invalid comment in table for 't1'. +--source include/assert_grep.inc + +--let $assert_select = Clear utf8mb3 character strings: '\\\xF0\\\x9F\\\x90' in comment of field 'test\.t2\.a'\. +--let $assert_text = Not found clear invalid comment in field for 't2'. +--source include/assert_grep.inc + +--let $assert_select = Clear utf8mb3 character strings: '\\\xF0\\\x9F\\\x90' in comment of index 'test\.t3\.idx1'\. +--let $assert_text = Not found clear invalid comment in index for 't3'. +--source include/assert_grep.inc + +--let $assert_count = 10 +--let assert_select= It will clear the invalid character strings in comment automatically\. +--let $assert_text = Found suggest to set 'upgrade_clear_invalid_comment' to ON. +--source include/assert_grep.inc + +--echo +--echo # Upgrade pass and the messages of clear operations will be recorded in MYSQLD_LOG_TRUE. +--let $assert_file = $MYSQLD_LOG_UPGRADE_TRUE +--let $assert_count = 0 +--let $assert_select = Comment for table 'test\.t4' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'\. +--let $assert_text = Not found invalid comment in table, field and index for 't4'. +--source include/assert_grep.inc + +--let $assert_select = Comment for table 'test\.t1' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'\. +--let $assert_text = Not found invalid comment in table for 't1'. +--source include/assert_grep.inc + +--let $assert_select = Comment for field 'test\.t2\.a' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'\. +--let $assert_text = Not found invalid comment in field for 't2'. +--source include/assert_grep.inc + +--let $assert_select = Comment for index 'test\.t3\.idx1' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'\. +--let $assert_text = Not found invalid comment in index for 't3'. +--source include/assert_grep.inc + +--let $assert_count = 1 +--let $assert_select = Clear utf8mb3 character strings: '\\\xF0\\\x9F\\\x90' in comment of table 'test\.t4'\. +--let $assert_text = Found clear invalid comment in table for 't4'. +--source include/assert_grep.inc + +--let $assert_select = Clear utf8mb3 character strings: '\\\xF0\\\x9F\\\x90' in comment of field 'test\.t4\.a'\. +--let $assert_text = Found clear invalid comment in field for 't4'. +--source include/assert_grep.inc + +--let $assert_select = Clear utf8mb3 character strings: '\\\xF0\\\x9F\\\x90' in comment of index 'test\.t4\.idx1'\. +--let $assert_text = Found clear invalid comment in index for 't4'. +--source include/assert_grep.inc + +--let $assert_select = Clear utf8mb3 character strings: '\\\xF0\\\x9F\\\x90' in comment of table 'test\.t1'\. +--let $assert_text = Found clear invalid comment in table for 't1'. +--source include/assert_grep.inc + +--let $assert_select = Clear utf8mb3 character strings: '\\\xF0\\\x9F\\\x90' in comment of field 'test\.t2\.a'\. +--let $assert_text = Found clear invalid comment in field for 't2'. +--source include/assert_grep.inc + +--let $assert_select = Clear utf8mb3 character strings: '\\\xF0\\\x9F\\\x90' in comment of index 'test\.t3\.idx1'\. +--let $assert_text = Found clear invalid comment in index for 't3'. +--source include/assert_grep.inc + +--let $assert_count = 0 +--let assert_select= It will clear the invalid character strings in comment automatically\. +--let $assert_text = Not found suggest to set 'upgrade_clear_invalid_comment' to ON. +--source include/assert_grep.inc + +# 5) Show variable and shutdown. +--echo +--echo # Show variable "upgrade_clear_invalid_comment". +--connection default +SELECT @@GLOBAL.upgrade_clear_invalid_comment; + +--echo +--echo # Stop the server. +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--source include/shutdown_mysqld.inc + + +# 6) Start again by default, check if comments were cleared. +--echo +--echo # Start again by default, check if comments were cleared. +--enable_reconnect +--exec echo 'restart:--log-error=$MYSQLD_LOG_TRUE --datadir=$MYSQLD_DATADIR1' > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--source include/wait_until_connected_again.inc + +--echo +--connection default +USE test; +--echo # The comment of t4 should have be cleared. +SHOW CREATE TABLE t4; +--echo +--echo # The comment of t1'table should have be cleared. +SHOW CREATE TABLE t1; +--echo +--echo # The comment of t2'field should have be cleared. +SHOW CREATE TABLE t2; +--echo +--echo # The comment of t3'index should have be cleared. +SHOW CREATE TABLE t3; + + +# 7) Shutdown and restart mtr server. +--echo +--echo # Stop the server. +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--source include/shutdown_mysqld.inc +--echo +--echo # Cleanup. +--remove_file $MYSQL_TMP_DIR/data_57_clear_invalid_comment_string.zip +--remove_file $MYSQLD_LOG_UPGRADE_FALSE +--remove_file $MYSQLD_LOG_UPGRADE_TRUE +--force-rmdir $MYSQL_TMP_DIR/data_57_clear_invalid_comment_string +--echo # Restart the server with default options. +--source include/start_mysqld.inc + diff --git a/share/messages_to_error_log.txt b/share/messages_to_error_log.txt index c593f51ca01..ad8022bd19b 100644 --- a/share/messages_to_error_log.txt +++ b/share/messages_to_error_log.txt @@ -3110,6 +3110,12 @@ ER_DD_UPGRADE_SE_LOGS_FAILED ER_DD_UPGRADE_SDI_INFO_UPDATE_FAILED eng "Error in updating SDI information." +ER_DD_UPGRADE_CLEAR_INVALID_STRING_IN_COMMENT + eng "Clear %s character strings: \'%.64s\' in comment of %s \'%s\'." + +ER_DD_UPGRADE_FAILED_FOR_INVALID_STRRING_IN_COMMENT + eng "Upgrade may fail for invalid comment in table, field and index. If failed, you can set 'upgrade_clear_invalid_comment' to ON and try again. It will clear the invalid character strings in comment automatically." + ER_SKIP_UPDATING_METADATA_IN_SE_RO_MODE eng "Skip updating %s metadata in InnoDB read-only mode." diff --git a/sql/dd/upgrade_57/table.cc b/sql/dd/upgrade_57/table.cc index 95be4bb3e95..cdbad834bcc 100644 --- a/sql/dd/upgrade_57/table.cc +++ b/sql/dd/upgrade_57/table.cc @@ -1480,6 +1480,52 @@ static bool fix_fk_parent_key_names(THD *thd, const String_type &schema_name, return trans_commit_stmt(thd) || trans_commit(thd); } +static void clear_table_invalid_comment(const char *schema_name, + const char *table_name, + HA_CREATE_INFO *create_info, + Alter_info *alter_info) { + // Clear invalid comment of table, field and index. + auto clear_invalid_comment = [&schema_name, &table_name] ( + LEX_CSTRING *comment, + const char *object_type, + const char *object_name) { + std::string invalid_sub_str; + if (is_invalid_string(*comment, system_charset_info, invalid_sub_str)) { + std::string qualified_name = std::string(schema_name) + "." + + std::string(table_name); + + if (strcmp(object_type, "field") == 0 || + strcmp(object_type, "index") == 0) + qualified_name += "." + std::string(object_name); + + /* + The process of upgrading from 5.7 to 8.0 will not fail because invalid + character strings in comment is cleared. + */ + *comment = EMPTY_CSTR; + LogErr(WARNING_LEVEL, ER_DD_UPGRADE_CLEAR_INVALID_STRING_IN_COMMENT, + system_charset_info->csname, invalid_sub_str.c_str(), + object_type, qualified_name.c_str()); + } + }; + + // Check and clear invalid comment of table. + LEX_CSTRING *table_comment = + reinterpret_cast(&(create_info->comment)); + clear_invalid_comment(table_comment, "table", ""); + + // Check and clear invalid comments of fields. + for (auto &sql_field : alter_info->create_list) { + clear_invalid_comment(&(sql_field.comment), "field", sql_field.field_name); + } + + // Check and clear invalid comments of indexes. + for (auto key : alter_info->key_list) { + clear_invalid_comment(const_cast(&(key->key_create_info.comment)), + "index", key->name.str); + } +} + /** Read .frm files and enter metadata for tables/views. */ @@ -1663,6 +1709,13 @@ static bool migrate_table_to_dd(THD *thd, const String_type &schema_name, if (is_whitelisted_table) thd->push_internal_handler(&error_handler); + if (opt_upgrade_clear_invalid_comment) { + clear_table_invalid_comment(schema_name.c_str(), + table_name.c_str(), + &create_info, + &alter_info); + } + bool prepare_error = mysql_prepare_create_table( thd, schema_name.c_str(), table_name.c_str(), &create_info, &alter_info, file, (share.partition_info_str_len != 0), &key_info_buffer, &key_count, @@ -1893,6 +1946,11 @@ bool migrate_all_frm_to_dd(THD *thd, const char *dbname, return true; } + // Warning about clear invalid comment. + if (!opt_upgrade_clear_invalid_comment) { + LogErr(WARNING_LEVEL, ER_DD_UPGRADE_FAILED_FOR_INVALID_STRRING_IN_COMMENT); + } + for (unsigned int idx = a->number_off_files; idx > 0 && !dd::upgrade::Syntax_error_handler::has_too_many_errors(); idx--) { diff --git a/sql/dd/upgrade_57/upgrade.cc b/sql/dd/upgrade_57/upgrade.cc index 243ffdf5b5a..ded92ff7c6d 100644 --- a/sql/dd/upgrade_57/upgrade.cc +++ b/sql/dd/upgrade_57/upgrade.cc @@ -118,6 +118,8 @@ bool allow_sdi_creation() { return create_sdi_flag; } void set_allow_sdi_creation(bool param) { create_sdi_flag = param; } +bool opt_upgrade_clear_invalid_comment = false; + /** Check if it is a file extension which should be moved to backup_metadata_57 folder upgrade upgrade is successful. diff --git a/sql/dd/upgrade_57/upgrade.h b/sql/dd/upgrade_57/upgrade.h index 4f59d363eb6..25c416fdd17 100644 --- a/sql/dd/upgrade_57/upgrade.h +++ b/sql/dd/upgrade_57/upgrade.h @@ -90,6 +90,14 @@ bool in_progress(); */ bool allow_sdi_creation(); +/** + This variable is used to control if invalid character strings in + comment of table, field and index will be cleared when upgrade + from 5.7 to 8.0. If true, the process will not fail for the + invalid character. +*/ +extern bool opt_upgrade_clear_invalid_comment; + /** Class to manage a temporary file to maintain the progress of the upgrade. This file will help in error handling for crashes diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 79b4ae93f9d..c2ea24bd8a4 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -152,6 +152,8 @@ #include "storage/perfschema/terminology_use_previous.h" #endif /* WITH_PERFSCHEMA_STORAGE_ENGINE */ +#include "sql/dd/upgrade_57/upgrade.h" // dd::upgrade_57 + static constexpr const unsigned long DEFAULT_ERROR_COUNT{1024}; static constexpr const unsigned long DEFAULT_SORT_MEMORY{256UL * 1024UL}; static constexpr const unsigned HOST_CACHE_SIZE{128}; @@ -7687,3 +7689,12 @@ static Sys_var_enum Sys_explain_format( SESSION_VAR(explain_format), CMD_LINE(OPT_ARG), explain_format_names, DEFAULT(static_cast(Explain_format_type::TRADITIONAL)), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(nullptr), ON_UPDATE(nullptr)); + +static Sys_var_bool Sys_upgrade_clear_invalid_comment( + "upgrade_clear_invalid_comment", + "Whether clear the invalid character strings in comment of table, " + "index, and column automatically during upgrading from 5.7 to 8.0. " + "If true, the process will not fail for the invalid character.", + READ_ONLY GLOBAL_VAR(dd::upgrade_57::opt_upgrade_clear_invalid_comment), + CMD_LINE(OPT_ARG), DEFAULT(false), NO_MUTEX_GUARD, NOT_IN_BINLOG, + ON_CHECK(0), ON_UPDATE(0));