MySQL Bugs: #116750: Not all errors were thrown during the upgrade from 5.7 to 8.0

Bug #116750	Not all errors were thrown during the upgrade from 5.7 to 8.0
Submitted:	21 Nov 21:21	Modified:	25 Nov 7:26
Reporter:	Huaxiong Song (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Dictionary	Severity:	S3 (Non-critical)
Version:	8.0.40	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
When routines contain invalid characters, MySQL upgrade from 5.7 to 8.x process will fail and the routine information that caused the failure will be recorded in the error log.

However, when there are more than one routines containing invalid characters in the system before the upgrade, only one error will be recorded in the log.

This makes it impossible for us to know all the errors at once and modify them. We can only correct them one at a time and try to start again and again until they succeed, which is not user-friendly.

How to repeat:
Before upgrade, we create some store procedures and functions 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/ |
+--------------------------+----------------------------------+

# Step 1. create some store procedures and functions which contain invalid characters. 
CREATE DATABASE invalid_character_string;
USE invalid_character_string;

DELIMITER //
CREATE PROCEDURE p1()
BEGIN
  select 'proc🐬';
END//

CREATE PROCEDURE p2()
BEGIN
  select 'proc🐬';
END//

CREATE PROCEDURE p3()
BEGIN
  select 'proc🐬';
END//

DELIMITER ;

CREATE FUNCTION f1() RETURNS VARCHAR(64) RETURN "func🐬";
CREATE FUNCTION f2() RETURNS VARCHAR(64) RETURN "func🐬";
CREATE FUNCTION f3() RETURNS VARCHAR(64) RETURN "func🐬";

# Step 2. Shutdown 5.7 server and restart with 8.0(MySQL version is 8.0.40)
# You will see that upgrade is failed, and we can find only on error record about routine which has invalid chars. Nothing about f2, f3 ,p1, p2 and p3.
```part of error log
...
2024-11-21T21:02:15.221917Z 2 [ERROR] [MY-013140] [Server] Definition of stored routine 'invalid_character_string.f1' contains an invalid utf8mb3 character string: '\xF0\x9F\x90'.
2024-11-21T21:02:15.221955Z 2 [ERROR] [MY-010768] [Server] Error in creating stored program 'invalid_character_string.f1'
2024-11-21T21:02:15.297629Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2024-11-21T21:02:15.297670Z 0 [ERROR] [MY-010119] [Server] Aborting
...
```

Suggested fix:
Throw all errors at once instead of just one.

In fact, for table migration, errors are thrown at once. For routine migration, I think similar processing is OK, and I may submit a fix later.

By the way, event migration also has similar problems. From the code logic analysis, MYSQL cannot throw all errors generated by event migration at once. But I will talk about it in another report, because event migration has other problems.

Another report with event migration: Bug#116751

Fix of bug#116750, bug#116751 and bug#116754 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: fix_upgrade.patch (application/octet-stream, text), 20.01 KiB.

Datadir from MySQL 5.7, for testcase. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: data57_invalid_string_routines_events_triggers.zip (application/zip, text), 1.40 MiB.

Datadir from MySQL 5.7, for testcase.

Attachment: data57_invalid_string_routines_events_triggers.zip (application/zip, text), 1.40 MiB.

Hello Huaxiong Song,

Thank you for the report and contribution.

regards,
Umesh