maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

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@xxxxxxxxxxx