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 secur...@mariadb.org _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
