Hi, Rucha, On Dec 28, Rucha Deodhar wrote:

revision-id: 6a8268b7893 (mariadb-10.6.11-62-g6a8268b7893) parent(s): 4ca5a0ec98d author: Rucha Deodhar committer: Rucha Deodhar timestamp: 2022-12-15 17:45:25 +0530 message:

MDEV-28915: mysql_upgrade fails due to old_mode="", with "Cannot load from mysql.proc. The table is probably corrupted"

Analysis: When mysql_upgrade runs statements for upgrade, characterset is converted to utf8mb4 because server starts with old_mode that interprets utf8 to utf8mb4, but mysql.proc table has "utf8mb3" as hardcoded, so it crashes with corrupted table.

Fix: Changed Table_check_intact::check() definition to allow both utf8mb3 and utf8mb4 by checking prefix and changing the upgrade scripts to explicitly use utf8mb3

diff --git a/mysql-test/main/mysql_upgrade-28915.opt b/mysql-test/main/mysql_upgrade-28915.opt new file mode 100644 index 00000000000..0be5567a64d --- /dev/null +++ b/mysql-test/main/mysql_upgrade-28915.opt @@ -0,0 +1 @@ +--old-mode=NO_DUP_KEY_WARNINGS_WITH_IGNORE

it's a bit confusing, try to use --old-mode= that is, empty old_mode, otherwise one starts thinking what this bug has to do with NO_DUP_KEY_WARNINGS_WITH_IGNORE.

diff --git a/mysql-test/main/mysql_upgrade-28915.test b/mysql-test/main/mysql_upgrade-28915.test new file mode 100644 index 00000000000..61b1fc263cb --- /dev/null +++ b/mysql-test/main/mysql_upgrade-28915.test @@ -0,0 +1,78 @@ +--source include/mysql_upgrade_preparation.inc + +let $MYSQLD_DATADIR= `select @@datadir`; + +--echo # +--echo # Beginning of 10.6 test +--echo # +--echo # MDEV-28915: mysql_upgrade fails due to old_mode="", with "Cannot load +--echo # from mysql.proc. The table is probably corrupted" + +--echo # Show that tables are created with utf8mb3 even without UTF8_IS_UTF8MB3 (see the .opt file)

I don't think you've shown it. mtr does not recreate system tables for every test. You need to force it to re-bootstrap with one of the options that do it, for example, --innodb-page-size=16k (search mtr for "rebootstrap" for details). But even that won't help, because it'll only add "rebootstrap" options to the server's command line when bootstrapping. May be a combination of .cnf and .opt can do what you want, I didn't try.

+ +SHOW CREATE TABLE mysql.proc; +SHOW CREATE TABLE mysql.event; + +--exec $MYSQL_UPGRADE --force 2>&1 + +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info + +SHOW CREATE TABLE mysql.proc; +SHOW CREATE TABLE mysql.event; + +--echo # Emulate that tables were created with utf8mb4 by an older version + +ALTER TABLE mysql.proc MODIFY db CHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, + MODIFY name CHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, + MODIFY specific_name CHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, + MODIFY definer VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, + MODIFY comment TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, + MODIFY character_set_client CHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, + MODIFY collation_connection CHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, + MODIFY db_collation CHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL; + +ALTER TABLE mysql.event MODIFY db CHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '', + MODIFY name CHAR(64) CHARACTER SET utf8mb4 NOT NULL DEFAULT '', + MODIFY definer VARCHAR(384) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '', + MODIFY comment CHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '', + MODIFY character_set_client CHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, + MODIFY collation_connection CHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, + MODIFY db_collation CHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; + +--echo # mysql_upgrade changes columns from utf8mb4 to utf8mb3 + +--exec $MYSQL_UPGRADE --force 2>&1 +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info + +--vertical_results + +SHOW CREATE TABLE mysql.proc; +SHOW CREATE TABLE mysql.event; + +CREATE TABLE t1 (id1 INT, val1 VARCHAR(5)); + +DELIMITER |; + +CREATE PROCEDURE sp1 () + BEGIN + SELECT val1 FROM t1; + END|

You've made changes in sp.cc and event_db_repository.cc to ensure that these tables are usable even with utf8mb4 charset. But you don't test it if it works. You create a procedure and an event here, *after* tables were converted back to utf8mb3. you likely want to do it earlier, before mysql_upgrade, just after ALTER TABLE.

+ +DELIMITER ;| + +SELECT name, body_utf8, body FROM mysql.proc WHERE name like 'sp1'; +CALL sp1(); +SELECT name, body_utf8, body FROM mysql.proc WHERE name like 'sp1'; + +SET GLOBAL event_scheduler=ON; + +SELECT name, body_utf8, body FROM mysql.event; +CREATE EVENT ev1 ON SCHEDULE EVERY 1 SECOND DO INSERT INTO t1 VALUES (1, 'abc'); +SELECT name, body_utf8, body FROM mysql.event; + +SET GLOBAL event_scheduler=OFF; +DROP EVENT ev1; +DROP PROCEDURE sp1; +DROP TABLE t1; + +--echo # end of 10.6 test

Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org