diff --git a/mysql-test/r/dd_upgrade_error.result b/mysql-test/r/dd_upgrade_error.result index f2043cff529..20fb62a4a5a 100644 --- a/mysql-test/r/dd_upgrade_error.result +++ b/mysql-test/r/dd_upgrade_error.result @@ -142,6 +142,32 @@ Pattern "Definition of view 'test\.v1' contains an invalid utf8mb3 character str # Cleanup. +# ------------------------------------------------------------------------------------------- +# Bug#116750 Bug#116751 Bug#116754 FAILURE TO UPGRADE FROM 5.7, INVALID UTF8 CHARACTER STRING +# ------------------------------------------------------------------------------------------- + +# Upgrade 5.7 data directory containing more than one procedure, +# function, event and trigger, each having an invalid character string +# in their definition. The upgrade process should report all failures +# at once. + +# Upgrade should fail with these errors in the log. +Pattern "Definition of trigger 'invalid_character_string\.tr1_t2' contains an invalid utf8mb3 character string: '\\xF0\\x9F\\x90'." found +Pattern "Definition of trigger 'invalid_character_string\.tr2_t2' contains an invalid utf8mb3 character string: '\\xF0\\x9F\\x90'." found +Pattern "Definition of trigger 'invalid_character_string\.tr1_t1' contains an invalid utf8mb3 character string: '\\xF0\\x9F\\x90'." found +Pattern "Definition of trigger 'invalid_character_string\.tr2_t1' contains an invalid utf8mb3 character string: '\\xF0\\x9F\\x90'." found +Pattern "Definition of event 'invalid_character_string\.e1' contains an invalid utf8mb3 character string: '\\xF0\\x9F\\x90'." found +Pattern "Definition of event 'invalid_character_string\.e2' contains an invalid utf8mb3 character string: '\\xF0\\x9F\\x90'." found +Pattern "Definition of event 'invalid_character_string\.e3' contains an invalid utf8mb3 character string: '\\xF0\\x9F\\x90'." found +Pattern "Definition of stored routine 'invalid_character_string\.f1' contains an invalid utf8mb3 character string: '\\xF0\\x9F\\x90'." found +Pattern "Definition of stored routine 'invalid_character_string\.f2' contains an invalid utf8mb3 character string: '\\xF0\\x9F\\x90'." found +Pattern "Definition of stored routine 'invalid_character_string\.f3' contains an invalid utf8mb3 character string: '\\xF0\\x9F\\x90'." found +Pattern "Definition of stored routine 'invalid_character_string\.p1' contains an invalid utf8mb3 character string: '\\xF0\\x9F\\x90'." found +Pattern "Definition of stored routine 'invalid_character_string\.p2' contains an invalid utf8mb3 character string: '\\xF0\\x9F\\x90'." found +Pattern "Definition of stored routine 'invalid_character_string\.p3' contains an invalid utf8mb3 character string: '\\xF0\\x9F\\x90'." found + +# Cleanup. + # ------------------------------------------------------------------ # End-of-test cleanup. # ------------------------------------------------------------------ diff --git a/mysql-test/r/invalid_body_events_triggers.result b/mysql-test/r/invalid_body_events_triggers.result new file mode 100644 index 00000000000..d906bcebf68 --- /dev/null +++ b/mysql-test/r/invalid_body_events_triggers.result @@ -0,0 +1,29 @@ + +# ------------------------------------------------------ +# Bug#116751 Do precheck before create event and trigger +# ------------------------------------------------------ +SET NAMES utf8mb3; +Warnings: +Warning 1287 'utf8mb3' is deprecated and will be removed in a future release. Please use utf8mb4 instead + +# Create or alter event with invalid string +CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT "event🐬"; +ERROR HY000: Definition of event 'test.e1' contains an invalid utf8mb3 character string: '\xF0\x9F\x90'. +CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT "event"; +ALTER EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT "event🐬"; +ERROR HY000: Definition of event 'test.e1' contains an invalid utf8mb3 character string: '\xF0\x9F\x90'. +CREATE DATABASE test_1; +ALTER EVENT e1 ON SCHEDULE EVERY 10 HOUR RENAME TO test_1.e2 do SELECT "event🐬"; +ERROR HY000: Definition of event 'test_1.e2' contains an invalid utf8mb3 character string: '\xF0\x9F\x90'. + +# Create trigger with invalid string +CREATE TABLE t1(a int); +CREATE TRIGGER tr1_t1 BEFORE INSERT ON t1 FOR EACH ROW +SET @x = (SELECT "trigger🐬"); +ERROR HY000: Definition of trigger 'test.tr1_t1' contains an invalid utf8mb3 character string: '\xF0\x9F\x90'. + +# Cleanup +DROP TABLE t1; +DROP DATABASE test_1; +DROP EVENT e1; +SET NAMES DEFAULT; diff --git a/mysql-test/std_data/upgrade/data57_invalid_string_routines_events_triggers.zip b/mysql-test/std_data/upgrade/data57_invalid_string_routines_events_triggers.zip new file mode 100644 index 00000000000..b3de13e6aa2 Binary files /dev/null and b/mysql-test/std_data/upgrade/data57_invalid_string_routines_events_triggers.zip differ diff --git a/mysql-test/t/dd_upgrade_error.test b/mysql-test/t/dd_upgrade_error.test index 6025fb72381..60cc7a44f09 100644 --- a/mysql-test/t/dd_upgrade_error.test +++ b/mysql-test/t/dd_upgrade_error.test @@ -390,6 +390,71 @@ let MYSQLD_LOG= $MYSQL_TMP_DIR/server.log; --remove_file $MYSQL_TMP_DIR/data_57_invalid_view_def_string.zip --force-rmdir $MYSQL_TMP_DIR/data_57_invalid_view_def_string +--echo +--echo # ------------------------------------------------------------------------------------------- +--echo # Bug#116750 Bug#116751 Bug#116754 FAILURE TO UPGRADE FROM 5.7, INVALID UTF8 CHARACTER STRING +--echo # ------------------------------------------------------------------------------------------- + +--echo +--echo # Upgrade 5.7 data directory containing more than one procedure, +--echo # function, event and trigger, each having an invalid character string +--echo # in their definition. The upgrade process should report all failures +--echo # at once. +--copy_file $MYSQLTEST_VARDIR/std_data/upgrade/data57_invalid_string_routines_events_triggers.zip $MYSQL_TMP_DIR/data57_invalid_string_routines_events_triggers.zip +--file_exists $MYSQL_TMP_DIR/data57_invalid_string_routines_events_triggers.zip +--exec unzip -qo $MYSQL_TMP_DIR/data57_invalid_string_routines_events_triggers.zip -d $MYSQL_TMP_DIR/data57_invalid_string_routines_events_triggers +--let $MYSQLD_DATADIR1= $MYSQL_TMP_DIR/data57_invalid_string_routines_events_triggers/data +--let $MYSQLD_LOG= $MYSQLTEST_VARDIR/log/data57_invalid_string_routines_events_triggers.log +--replace_result $MYSQLD MYSQLD $MYSQLD_DATADIR1 MYSQLD_DATADIR1 $MYSQLD_LOG MYSQLD_LOG +--error 1 +--exec $MYSQLD --no-defaults $extra_args --innodb_dedicated_server=OFF --secure-file-priv="" --log-error=$MYSQLD_LOG --datadir=$MYSQLD_DATADIR1 + +--echo +--echo # Upgrade should fail with these errors in the log. +--let SEARCH_FILE= $MYSQLD_LOG +--let SEARCH_PATTERN = Definition of trigger 'invalid_character_string\.tr1_t2' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'. +--source include/search_pattern.inc + +--let SEARCH_PATTERN = Definition of trigger 'invalid_character_string\.tr2_t2' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'. +--source include/search_pattern.inc + +--let SEARCH_PATTERN = Definition of trigger 'invalid_character_string\.tr1_t1' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'. +--source include/search_pattern.inc + +--let SEARCH_PATTERN = Definition of trigger 'invalid_character_string\.tr2_t1' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'. +--source include/search_pattern.inc + +--let SEARCH_PATTERN = Definition of event 'invalid_character_string\.e1' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'. +--source include/search_pattern.inc + +--let SEARCH_PATTERN = Definition of event 'invalid_character_string\.e2' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'. +--source include/search_pattern.inc + +--let SEARCH_PATTERN = Definition of event 'invalid_character_string\.e3' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'. +--source include/search_pattern.inc + +--let SEARCH_PATTERN = Definition of stored routine 'invalid_character_string\.f1' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'. +--source include/search_pattern.inc + +--let SEARCH_PATTERN = Definition of stored routine 'invalid_character_string\.f2' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'. +--source include/search_pattern.inc + +--let SEARCH_PATTERN = Definition of stored routine 'invalid_character_string\.f3' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'. +--source include/search_pattern.inc + +--let SEARCH_PATTERN = Definition of stored routine 'invalid_character_string\.p1' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'. +--source include/search_pattern.inc + +--let SEARCH_PATTERN = Definition of stored routine 'invalid_character_string\.p2' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'. +--source include/search_pattern.inc + +--let SEARCH_PATTERN = Definition of stored routine 'invalid_character_string\.p3' contains an invalid utf8mb3 character string: '\\\xF0\\\x9F\\\x90'. +--source include/search_pattern.inc + +--echo +--echo # Cleanup. +--remove_file $MYSQL_TMP_DIR/data57_invalid_string_routines_events_triggers.zip +--force-rmdir $MYSQL_TMP_DIR/data57_invalid_string_routines_events_triggers --echo --echo # ------------------------------------------------------------------ diff --git a/mysql-test/t/invalid_body_events_triggers.test b/mysql-test/t/invalid_body_events_triggers.test new file mode 100644 index 00000000000..122610da39c --- /dev/null +++ b/mysql-test/t/invalid_body_events_triggers.test @@ -0,0 +1,43 @@ +--echo +--echo # ------------------------------------------------------ +--echo # Bug#116751 Do precheck before create event and trigger +--echo # ------------------------------------------------------ + +# Set the client charset equal to the system charset. This is done to avoid the +# conversion of string literals by the parser when the charset differs. +SET NAMES utf8mb3; + +# To allow testing of invalid binary data in the comment strings +--character_set binary + +--echo +--echo # Create or alter event with invalid string +--error ER_DEFINITION_CONTAINS_INVALID_STRING +CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT "event🐬"; + +CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT "event"; + +--error ER_DEFINITION_CONTAINS_INVALID_STRING +ALTER EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT "event🐬"; + +CREATE DATABASE test_1; +--error ER_DEFINITION_CONTAINS_INVALID_STRING +ALTER EVENT e1 ON SCHEDULE EVERY 10 HOUR RENAME TO test_1.e2 do SELECT "event🐬"; + + +--echo +--echo # Create trigger with invalid string +CREATE TABLE t1(a int); + +--error ER_DEFINITION_CONTAINS_INVALID_STRING +CREATE TRIGGER tr1_t1 BEFORE INSERT ON t1 FOR EACH ROW +SET @x = (SELECT "trigger🐬"); + + +--echo +--echo # Cleanup +DROP TABLE t1; +DROP DATABASE test_1; +DROP EVENT e1; +--character_set utf8mb4 +SET NAMES DEFAULT; \ No newline at end of file diff --git a/sql/dd/upgrade_57/event.cc b/sql/dd/upgrade_57/event.cc index b635d2b0088..fa96d0597c4 100644 --- a/sql/dd/upgrade_57/event.cc +++ b/sql/dd/upgrade_57/event.cc @@ -432,7 +432,7 @@ static bool migrate_event_to_dd(THD *thd, TABLE *event_table) { MYSQL_TIME time; LEX_USER user_info; Event_parse_data et_parse_data; - LEX_STRING event_body, event_body_utf8; + LEX_CSTRING event_body, event_body_utf8; et_parse_data.interval = INTERVAL_LAST; et_parse_data.identifier = nullptr; @@ -529,6 +529,21 @@ static bool migrate_event_to_dd(THD *thd, TABLE *event_table) { return false; } + /* + Validate body definition to avoid invalid UTF8 characters. We do not need + to validate comment because in 5.7, it is not possible to create an event + containing invalid comments. + */ + std::string invalid_sub_str; + if (is_invalid_string(event_body_utf8, system_charset_info, + invalid_sub_str)) { + /* Provide contextual information */ + my_error(ER_DEFINITION_CONTAINS_INVALID_STRING, MYF(0), "event", + et_parse_data.dbname.str, et_parse_data.name.str, + system_charset_info->csname, invalid_sub_str.c_str()); + return true; + } + // Disable autocommit option in thd variable Disable_autocommit_guard autocommit_guard(thd); @@ -572,6 +587,7 @@ bool migrate_events_to_dd(THD *thd) { Table_ref tables("mysql", "event", TL_READ); auto table_list = &tables; + bool error_status = false; if (open_and_lock_tables(thd, table_list, flags, &prelocking_strategy)) { LogErr(ERROR_LEVEL, ER_EVENT_CANT_OPEN_TABLE_MYSQL_EVENT); @@ -610,14 +626,20 @@ bool migrate_events_to_dd(THD *thd) { goto err; } - if (migrate_event_to_dd(thd, event_table)) goto err; + /* + Set error status, but don't abort upgrade + as we want to process all events. + */ + error_status |= migrate_event_to_dd(thd, event_table); // Read the next row in 'event' table via index. while (!(error = event_table->file->ha_index_next(event_table->record[0])) && !dd::upgrade::Syntax_error_handler::has_too_many_errors()) { - if (migrate_event_to_dd(thd, event_table)) goto err; + error_status |= migrate_event_to_dd(thd, event_table); } + if (error_status) goto err; + if (error != HA_ERR_END_OF_FILE) { LogErr(ERROR_LEVEL, ER_EVENT_CANT_OPEN_TABLE_MYSQL_EVENT); goto err; diff --git a/sql/dd/upgrade_57/routine.cc b/sql/dd/upgrade_57/routine.cc index 8bc33de3d52..e0fbace0476 100644 --- a/sql/dd/upgrade_57/routine.cc +++ b/sql/dd/upgrade_57/routine.cc @@ -465,6 +465,7 @@ bool migrate_routines_to_dd(THD *thd) { Table_ref tables("mysql", "proc", TL_READ); auto table_list = &tables; + bool error_status = false; if (open_and_lock_tables(thd, table_list, flags, &prelocking_strategy)) { LogErr(ERROR_LEVEL, ER_CANT_OPEN_TABLE_MYSQL_PROC); @@ -496,16 +497,23 @@ bool migrate_routines_to_dd(THD *thd) { return true; } + /* + Set error status, but don't abort upgrade + as we want to process all routines. + */ + // Migrate first record read to dd routines table. - if (migrate_routine_to_dd(thd, proc_table)) return true; + error_status |= migrate_routine_to_dd(thd, proc_table); // Read one record from mysql.proc table and // migrate it until all records are finished while (!(error = proc_table->file->ha_index_next(proc_table->record[0])) && !dd::upgrade::Syntax_error_handler::has_too_many_errors()) { - if (migrate_routine_to_dd(thd, proc_table)) return true; + error_status |= migrate_routine_to_dd(thd, proc_table); } + if (error_status) return true; + if (error != HA_ERR_END_OF_FILE) { LogErr(ERROR_LEVEL, ER_CANT_READ_TABLE_MYSQL_PROC); return true; diff --git a/sql/dd/upgrade_57/table.cc b/sql/dd/upgrade_57/table.cc index bcba95ac771..77127d0a4a8 100644 --- a/sql/dd/upgrade_57/table.cc +++ b/sql/dd/upgrade_57/table.cc @@ -1144,6 +1144,46 @@ static bool add_triggers_to_table(THD *thd, TABLE *table, d->reorder_57_list(thd->mem_root, &m_triggers); List_iterator<::Trigger> it(m_triggers); + + /* Cleanup sp_head to avoid memory leak. */ + auto cleanup_sp = [&]() -> bool { + it.rewind(); + while (true) { + ::Trigger *t = it++; + if (!t) break; + sp_head::destroy(t->get_sp()); + } + return true; + }; + + /* + Set error status, but don't abort upgrade + as we want to process all triggers for the table. + */ + bool error_status = false; + while (true) { + ::Trigger *t = it++; + if (!t) break; + + /* Validate body definition to avoid invalid UTF8 characters. */ + std::string invalid_sub_str; + if (is_invalid_string(t->get_definition_utf8(), system_charset_info, + invalid_sub_str)) { + /* Provide contextual information */ + my_error(ER_DEFINITION_CONTAINS_INVALID_STRING, MYF(0), "trigger", + t->get_db_name().str, t->get_trigger_name().str, + system_charset_info->csname, invalid_sub_str.c_str()); + error_status = true; + } + } + + if (error_status) { + return cleanup_sp(); + } + + /* Set Iterator to the beginning */ + it.rewind(); + /* Fix the order column for the execution of Triggers with same action event and same action timing. .TRG filed used to handle @@ -1182,7 +1222,7 @@ static bool add_triggers_to_table(THD *thd, TABLE *table, LogErr(ERROR_LEVEL, ER_TRG_WRONG_ORDER, t->get_db_name().str, t->get_trigger_name().str, schema_name.c_str(), table_name.c_str()); - return true; + return cleanup_sp(); } // We found next trigger with same action event and same action time. @@ -1226,13 +1266,13 @@ static bool add_triggers_to_table(THD *thd, TABLE *table, trans_rollback_stmt(thd); // Full rollback in case we have THD::transaction_rollback_request. trans_rollback(thd); - return true; + return cleanup_sp(); } // dd::create_trigger() does not commit transaction if (trans_commit_stmt(thd) || trans_commit(thd)) { LogErr(ERROR_LEVEL, ER_DD_TRG_CANT_ADD, t->get_db_name().str, t->get_trigger_name().str); - return true; + return cleanup_sp(); } // Cleanup for Trigger diff --git a/sql/event_db_repository.cc b/sql/event_db_repository.cc index b964d0b960c..a7f01eef144 100644 --- a/sql/event_db_repository.cc +++ b/sql/event_db_repository.cc @@ -99,6 +99,17 @@ bool Event_db_repository::create_event(THD *thd, Event_parse_data *parse_data, return true; } + /* Validate body definition to avoid invalid UTF8 characters. */ + std::string invalid_sub_str; + if (is_invalid_string(sp->m_body_utf8, system_charset_info, + invalid_sub_str)) { + /* Provide contextual information */ + my_error(ER_DEFINITION_CONTAINS_INVALID_STRING, MYF(0), "event", + parse_data->dbname.str, parse_data->name.str, + system_charset_info->csname, invalid_sub_str.c_str()); + return true; + } + return dd::create_event(thd, *schema, parse_data->name.str, sp->m_body.str, sp->m_body_utf8.str, thd->lex->definer, parse_data); } @@ -177,6 +188,23 @@ bool Event_db_repository::update_event(THD *thd, Event_parse_data *parse_data, Auth_id definer(event->definer_user().c_str(), event->definer_host().c_str()); if (sctx->can_operate_with(definer, consts::system_user, true)) return true; + if (parse_data->body_changed) { + /* Validate body definition to avoid invalid UTF8 characters. */ + std::string invalid_sub_str; + if (is_invalid_string(sp->m_body_utf8, system_charset_info, + invalid_sub_str)) { + /* Provide contextual information */ + const char *db_name = + new_name != nullptr ? new_dbname->str : parse_data->dbname.str; + const char *event_name = + new_name != nullptr ? new_name->str : parse_data->name.str; + my_error(ER_DEFINITION_CONTAINS_INVALID_STRING, MYF(0), "event", db_name, + event_name, system_charset_info->csname, + invalid_sub_str.c_str()); + return true; + } + } + // Update Event in the data dictionary with altered event object attributes. bool ret = dd::update_event( thd, event, *schema, new_schema, new_name != nullptr ? new_name->str : "", diff --git a/sql/table_trigger_dispatcher.cc b/sql/table_trigger_dispatcher.cc index 314fd3bb941..3b2af962c00 100644 --- a/sql/table_trigger_dispatcher.cc +++ b/sql/table_trigger_dispatcher.cc @@ -268,6 +268,18 @@ bool Table_trigger_dispatcher::create_trigger( return true; } + /* Validate body definition to avoid invalid UTF8 characters. */ + std::string invalid_sub_str; + if (is_invalid_string(t->get_definition_utf8(), system_charset_info, + invalid_sub_str)) { + /* Provide contextual information */ + my_error(ER_DEFINITION_CONTAINS_INVALID_STRING, MYF(0), "trigger", + t->get_db_name().str, t->get_trigger_name().str, + system_charset_info->csname, invalid_sub_str.c_str()); + destroy(t); + return true; + } + // Add the newly created trigger to the chain. if (tc->add_trigger(&m_subject_table->mem_root, t,