MySQL Bugs: #116751: Incomplete error of event/trigger during upgrade from 5.7 to 8.0

Bug #116751	Incomplete error of event/trigger during upgrade from 5.7 to 8.0
Submitted:	21 Nov 21:43	Modified:	22 Nov 7:47
Reporter:	Huaxiong Song (OCA)	Email Updates:
Status:	Analyzing	Impact on me:	None
Category:	MySQL Server: Data Dictionary	Severity:	S3 (Non-critical)
Version:	8.0.40	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
When an event/trigger contains invalid characters, the upgrade from 5.7 to 8.0 will fail, but we don't know what event/trigger caused the error.

How to repeat:
Before upgrade, we create some events or triggers which contain invalid characters. For 5.7, the variables like:
MySQL [(none)]> show variables like "%char%";
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | latin1                           |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | latin1                           |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

# For event

# Step 1. Create events which contain invalid characters.
CREATE DATABASE invalid_character_string;
USE invalid_character_string;

CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT "event🐬";
CREATE EVENT e2 ON SCHEDULE EVERY 10 HOUR DO SELECT "event🐬";
CREATE EVENT e3 ON SCHEDULE EVERY 10 HOUR DO SELECT "event🐬";

# Step 2. Shutdown 5.7 server and restart with 8.0(MySQL version is 8.0.40)
# You will see that upgrade is failed, but we can not know which event(s) lead to it...
```part of error log
...
2024-11-21T20:54:54.534724Z 1 [Note] [MY-010337] [Server] Created Data Dictionary for upgrade
2024-11-21T20:54:54.781992Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2024-11-21T20:54:54.782030Z 0 [ERROR] [MY-010119] [Server] Aborting
...
```

#############################################

# For trigger

# Step 1. Create triggers which contain invalid characters.
CREATE DATABASE invalid_character_string;
USE invalid_character_string;

CREATE TABLE t1(a int);

CREATE TRIGGER trigge1 BEFORE INSERT ON t1 FOR EACH ROW
SET @x = (SELECT "trigger🐬");

CREATE TRIGGER trigge2 BEFORE INSERT ON t1 FOR EACH ROW
SET @x = (SELECT "trigger🐬");

CREATE TRIGGER trigge3 BEFORE INSERT ON t1 FOR EACH ROW
SET @x = (SELECT "trigger🐬");

# Step 2. Shutdown 5.7 server and restart with 8.0(MySQL version is 8.0.40)
# You will see that upgrade is failed, but we can not know which trigger(s) lead to it...
```part of error log
...
2024-11-21T20:52:36.832915Z 1 [Note] [MY-010337] [Server] Created Data Dictionary for upgrade
2024-11-21T20:52:36.973706Z 2 [ERROR] [MY-013140] [Server] Failed to update triggers dictionary object.
2024-11-21T20:52:37.080705Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2024-11-21T20:52:37.080747Z 0 [ERROR] [MY-010119] [Server] Aborting
...
```

And if we run MySQL-8.0 with debug, crash will happen because Data Dictionary refuse to store invalid characters.
```Crash stack of event
mysqld: /mysql-server/sql/dd/impl/raw/raw_record.cc:159: bool dd::Raw_record::store(int, const String_type&, bool): Assertion `rc == TYPE_OK' failed.
2024-11-21T12:51:09Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=80f71e5b61df305e658aa24f76f8a6ce83d539c1
Thread pointer: 0x627000165100
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 = 7fffe4119990 thread_stack 0x100000
/flash3/huaxiong.shx/gcc-10.2.0/lib64/libasan.so.6(+0x433a1) [0x7ffff72563a1]
/mysql-server/runtime_output_directory/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0xbd) [0x5a5db0e]
/mysql-server/runtime_output_directory/mysqld(print_fatal_signal(int)+0x697) [0x378e4ca]
/mysql-server/runtime_output_directory/mysqld(handle_fatal_signal+0xa8) [0x378e775]
/lib64/libpthread.so.0(+0xf630) [0x7ffff7006630]
/lib64/libc.so.6(gsignal+0x37) [0x7ffff528e387]
/lib64/libc.so.6(abort+0x148) [0x7ffff528fa78]
/lib64/libc.so.6(+0x2f1a6) [0x7ffff52871a6]
/lib64/libc.so.6(+0x2f252) [0x7ffff5287252]
/mysql-server/runtime_output_directory/mysqld(dd::Raw_record::store(int, std::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const&, bool)+0xe2) [0x5823fa8]
/mysql-server/runtime_output_directory/mysqld(dd::Event_impl::store_attributes(dd::Raw_record*)+0x707) [0x5933c0f]
/mysql-server/runtime_output_directory/mysqld(dd::Weak_object_impl_<true>::store(dd::Open_dictionary_tables_ctx*)+0x622) [0x59a1326]
/mysql-server/runtime_output_directory/mysqld(bool dd::cache::Storage_adapter::store<dd::Event>(THD*, dd::Event*)+0x3d5) [0x5813c39]
/mysql-server/runtime_output_directory/mysqld(bool dd::cache::Dictionary_client::store<dd::Event>(dd::Event*)+0x350) [0x56c627c]
/mysql-server/runtime_output_directory/mysqld(dd::create_event(THD*, dd::Schema const&, std::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const&, std::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const&, std::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const&, LEX_USER const*, Event_par/mysql-server/runtime_output_directory/mysqld() [0x59f263b]
/mysql-server/runtime_output_directory/mysqld(dd::upgrade_57::migrate_events_to_dd(THD*)+0x59f) [0x59f302d]
/mysql-server/runtime_output_directory/mysqld(dd::upgrade_57::fill_dd_and_finalize(THD*)+0x491) [0x59eaa93]
/mysql-server/runtime_output_directory/mysqld() [0x397ea87]
/mysql-server/runtime_output_directory/mysqld() [0x6d95d6f]
/lib64/libpthread.so.0(+0x7ea5) [0x7ffff6ffeea5]
/lib64/libc.so.6(clone+0x6d) [0x7ffff5356b0d]
```

```Crash stack of trigger
mysqld: /mysql-server/sql/dd/impl/raw/raw_record.cc:159: bool dd::Raw_record::store(int, const String_type&, bool): Assertion `rc == TYPE_OK' failed.
2024-11-21T13:00:54Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=80f71e5b61df305e658aa24f76f8a6ce83d539c1
Thread pointer: 0x627000165100
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 = 7f703c85f990 thread_stack 0x100000
/flash3/huaxiong.shx/gcc-10.2.0/lib64/libasan.so.6(+0x433a1) [0x7f704f9a13a1]
/mysql-server/runtime_output_directory/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0xbd) [0x5a5db0e]
/mysql-server/runtime_output_directory/mysqld(print_fatal_signal(int)+0x697) [0x378e4ca]
/mysql-server/runtime_output_directory/mysqld(handle_fatal_signal+0xa8) [0x378e775]
/lib64/libpthread.so.0(+0xf630) [0x7f704f751630]
/lib64/libc.so.6(gsignal+0x37) [0x7f704d9d9387]
/lib64/libc.so.6(abort+0x148) [0x7f704d9daa78]
/lib64/libc.so.6(+0x2f1a6) [0x7f704d9d21a6]
/lib64/libc.so.6(+0x2f252) [0x7f704d9d2252]
/mysql-server/runtime_output_directory/mysqld(dd::Raw_record::store(int, std::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const&, bool)+0xe2) [0x5823fa8]
/mysql-server/runtime_output_directory/mysqld(dd::Trigger_impl::store_attributes(dd::Raw_record*)+0x45d) [0x59960d5]
/mysql-server/runtime_output_directory/mysqld(dd::Weak_object_impl_<true>::store(dd::Open_dictionary_tables_ctx*)+0x622) [0x59a1326]
/mysql-server/runtime_output_directory/mysqld(dd::Collection<dd::Trigger*>::store_items(dd::Open_dictionary_tables_ctx*)+0x413) [0x54f67b9]
/mysql-server/runtime_output_directory/mysqld(dd::Table_impl::store_triggers(dd::Open_dictionary_tables_ctx*)+0x359) [0x59742bd]
/mysql-server/runtime_output_directory/mysqld(dd::Table_impl::store_children(dd::Open_dictionary_tables_ctx*)+0x86) [0x59743fc]
/mysql-server/runtime_output_directory/mysqld(dd::Weak_object_impl_<true>::store(dd::Open_dictionary_tables_ctx*)+0x4bd) [0x59a11c1]
/mysql-server/runtime_output_directory/mysqld(bool dd::cache::Storage_adapter::store<dd::Table>(THD*, dd::Table*)+0x3d5) [0x581bfa7]
/mysql-server/runtime_output_directory/mysqld(bool dd::cache::Dictionary_client::update<dd::Table>(dd::Table*)+0x748) [0x56b5940]
/mysql-server/runtime_output_directory/mysqld(dd::create_trigger(THD*, Trigger const*, enum_trigger_order_type, MYSQL_LEX_CSTRING const&)+0x5a9) [0x559b97e]
/mysql-server/runtime_output_directory/mysqld() [0x59fc884]
/mysql-server/runtime_output_directory/mysqld() [0x5a03f21]
/mysql-server/runtime_output_directory/mysqld(dd::upgrade_57::migrate_all_frm_to_dd(THD*, char const*, bool)+0xb6c) [0x5a05112]
/mysql-server/runtime_output_directory/mysqld(dd::upgrade_57::fill_dd_and_finalize(THD*)+0x313) [0x59ea915]
/mysql-server/runtime_output_directory/mysqld() [0x397ea87]
/mysql-server/runtime_output_directory/mysqld() [0x6d95d6f]
/lib64/libpthread.so.0(+0x7ea5) [0x7f704f749ea5]
/lib64/libc.so.6(clone+0x6d) [0x7f704daa1b0d]
```

Suggested fix:
Do some prechecks before create new DD object. Like function `create_routine_precheck` of migrate routines or `is_invalid_string` of migrate tables.

There is another problem with events(Bug#116750), which also needs to be solved.

Triggers do not have the problem in Bug#116750, because the creation of triggers is included in the process of table migration, and the errors that occur in triggers are mapped to errors that occur in the table, and table migration can report all errors.

In debug mode,creating a trigger or event containing invalid characters in 8.0.40 will cause the instance to crash.
For routines, when we try to create sp/func with invalid string, error will be report: RROR 4089 (HY000): Definition of stored routine 'test.f1' contains an invalid utf8mb3 character string: '\xF0\x9F\x90'.
Therefore, I think the process of creating triggers and events in 8.0 also needs to check for invalid characters.

Execute the SQL in 8.0 debug:
CREATE TABLE t1(a int);
CREATE TRIGGER trigge1 BEFORE INSERT ON t1 FOR EACH ROW
SET @x = (SELECT "trigger🐬");

or
CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT "event🐬";

crash will happen.

```Crash stack
mysqld: mysql-server/sql/dd/impl/raw/raw_record.cc:159: bool dd::Raw_record::store(int, const String_type&, bool): Assertion `rc == TYPE_OK' failed.
2024-11-22T07:40:49Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=5329f91fa8b98b2ce26df9cb3cf07c7fbb558214
Thread pointer: 0x62700040f900
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 = 7fcf7a72a940 thread_stack 0x100000
/flash3/huaxiong.shx/gcc-10.2.0/lib64/libasan.so.6(+0x433a1) [0x7fcf9fbf73a1]
/mysql-server/runtime_output_directory/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0xbd) [0x5a5dd3a]
/mysql-server/runtime_output_directory/mysqld(print_fatal_signal(int)+0x697) [0x378e4ca]
/mysql-server/runtime_output_directory/mysqld(handle_fatal_signal+0xa8) [0x378e775]
/lib64/libpthread.so.0(+0xf630) [0x7fcf9f9a7630]
/lib64/libc.so.6(gsignal+0x37) [0x7fcf9dc2f387]
/lib64/libc.so.6(abort+0x148) [0x7fcf9dc30a78]
/lib64/libc.so.6(+0x2f1a6) [0x7fcf9dc281a6]
/lib64/libc.so.6(+0x2f252) [0x7fcf9dc28252]
/mysql-server/runtime_output_directory/mysqld(dd::Raw_record::store(int, std::basic_string<char, std::char_traits<char>, Stateless_allocator<char, dd::String_type_alloc, My_free_functor> > const&, bool)+0xe2) [0x5823fa8]
/mysql-server/runtime_output_directory/mysqld(dd::Trigger_impl::store_attributes(dd::Raw_record*)+0x45d) [0x59960d5]
/mysql-server/runtime_output_directory/mysqld(dd::Weak_object_impl_<true>::store(dd::Open_dictionary_tables_ctx*)+0x622) [0x59a1326]
/mysql-server/runtime_output_directory/mysqld(dd::Collection<dd::Trigger*>::store_items(dd::Open_dictionary_tables_ctx*)+0x413) [0x54f67b9]
/mysql-server/runtime_output_directory/mysqld(dd::Table_impl::store_triggers(dd::Open_dictionary_tables_ctx*)+0x359) [0x59742bd]
/mysql-server/runtime_output_directory/mysqld(dd::Table_impl::store_children(dd::Open_dictionary_tables_ctx*)+0x86) [0x59743fc]
/mysql-server/runtime_output_directory/mysqld(dd::Weak_object_impl_<true>::store(dd::Open_dictionary_tables_ctx*)+0x4bd) [0x59a11c1]
/mysql-server/runtime_output_directory/mysqld(bool dd::cache::Storage_adapter::store<dd::Table>(THD*, dd::Table*)+0x3d5) [0x581bfa7]
/mysql-server/runtime_output_directory/mysqld(bool dd::cache::Dictionary_client::update<dd::Table>(dd::Table*)+0x748) [0x56b5940]
/mysql-server/runtime_output_directory/mysqld(dd::create_trigger(THD*, Trigger const*, enum_trigger_order_type, MYSQL_LEX_CSTRING const&)+0x5a9) [0x559b97e]
/mysql-server/runtime_output_directory/mysqld(Table_trigger_dispatcher::create_trigger(THD*, String*, bool, bool&)+0x1342) [0x36ae960]
/mysql-server/runtime_output_directory/mysqld(Sql_cmd_create_trigger::execute(THD*)+0x9dc) [0x35e25ba]
/mysql-server/runtime_output_directory/mysqld(mysql_execute_command(THD*, bool)+0x73c4) [0x33bfc63]
/mysql-server/runtime_output_directory/mysqld(dispatch_sql_command(THD*, Parser_state*)+0xb2c) [0x33c31df]
/mysql-server/runtime_output_directory/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x2291) [0x33c5dff]
/mysql-server/runtime_output_directory/mysqld(do_command(THD*)+0xa91) [0x33c97ab]
/mysql-server/runtime_output_directory/mysqld() [0x376c602]
/mysql-server/runtime_output_directory/mysqld() [0x6d95f81]
/lib64/libpthread.so.0(+0x7ea5) [0x7fcf9f99fea5]
/lib64/libc.so.6(clone+0x6d) [0x7fcf9dcf7b0d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (60d000392580): CREATE TRIGGER trigge1 BEFORE INSERT ON t1 FOR EACH ROW SET @x = (SELECT "trigger    ")
Connection ID (thread ID): 8
Status: NOT_KILLED
```