MDEV-24122: on previously MySQL-5.7 datadirs, adjust mysql.user column order

MDEV-23201 correctly reordered columns in mysql.user table when upgrading
from MySQL-5.7

Here we also correctly reorder columns in mysql.user table from an invalid order
caused by an upgrade from MySQL-5.7 to MariaDB-before-MDEV-23201.

1 files changed, 30 insertions, 0 deletions

diff --git a/mysql-test/t/mysql_upgrade.test b/mysql-test/t/mysql_upgrade.test
index a6702f91aa6..c116af860a6 100644
--- a/mysql-test/t/mysql_upgrade.test
+++ b/mysql-test/t/mysql_upgrade.test
@@ -246,6 +246,36 @@ FLUSH PRIVILEGES;
 SET ROLE `aRole`;
 SELECT `User`, `is_role` FROM `mysql`.`user`;
 
+DROP ROLE aRole;
+
+--echo #
+--echo # MDEV-24122: Fix previously MySQL-5.7 data directories that upgraded prior to MDEV-23201
+--echo #
+--echo #
+
+# For 10.4 merge - dropping the view.
+# DROP VIEW IF EXISTS mysql.user;
+DROP TABLE IF EXISTS mysql.user;
+--copy_file std_data/mysql57user.frm $MYSQLD_DATADIR/mysql/user.frm
+--copy_file std_data/mysql57user.MYI $MYSQLD_DATADIR/mysql/user.MYI
+--copy_file std_data/mysql57user.MYD $MYSQLD_DATADIR/mysql/user.MYD
+FLUSH TABLES mysql.user;
+
+# What prior to MDEV-23201 would of done:
+ALTER TABLE mysql.user ADD is_role enum('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL;
+ALTER TABLE mysql.user ADD default_role char(80) binary DEFAULT '' NOT NULL;
+ALTER TABLE mysql.user ADD max_statement_time decimal(12,6) DEFAULT 0 NOT NULL;
+FLUSH PRIVILEGES;
+
+--exec $MYSQL_UPGRADE --force 2>&1
+SHOW CREATE TABLE mysql.user;
+
+CREATE ROLE `aRole`;
+SET DEFAULT ROLE aRole;
+SHOW GRANTS;
+SET DEFAULT ROLE NONE;
+SHOW GRANTS;
+
 DROP ROLE `aRole`;
 --exec $MYSQL mysql < $MYSQLTEST_VARDIR/tmp/user.sql
 FLUSH PRIVILEGES;