MDEV-5275 Problems upgrading from MySQL 5.1 to MariaDB

correct bugs in mysql_system_tables_fix.sql.
Update system_mysql_db_fix* tests

1 files changed, 16 insertions, 20 deletions

diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql
index ad7122d9b9b..768d60f9af9 100644
--- a/scripts/mysql_system_tables_fix.sql
+++ b/scripts/mysql_system_tables_fix.sql
@@ -54,11 +54,6 @@ ADD x509_subject BLOB NOT NULL;
 ALTER TABLE user MODIFY ssl_type enum('','ANY','X509', 'SPECIFIED') NOT NULL;
 
 #
-# Add roles to the user table
-ALTER TABLE user
-ADD is_role enum('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL;
-
-#
 # tables_priv
 #
 ALTER TABLE tables_priv
@@ -191,8 +186,7 @@ ALTER TABLE user
   MODIFY Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
   MODIFY Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
   MODIFY Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
-  MODIFY ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL,
-  MODIFY is_role enum('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL;
+  MODIFY ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL;
 
 ALTER TABLE db
   MODIFY Host char(60) NOT NULL default '',
@@ -636,10 +630,12 @@ UPDATE user SET Create_tablespace_priv = Super_priv WHERE @hadCreateTablespacePr
 
 ALTER TABLE user ADD plugin char(64) DEFAULT '',  ADD authentication_string TEXT;
 ALTER TABLE user ADD password_expired ENUM('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL;
+ALTER TABLE user ADD is_role enum('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL;
 ALTER TABLE user MODIFY plugin char(64) CHARACTER SET latin1 DEFAULT '' NOT NULL, MODIFY authentication_string TEXT NOT NULL;
 -- Somewhere above, we ran ALTER TABLE user .... CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin.
 --  we want password_expired column to have collation utf8_general_ci.
 ALTER TABLE user MODIFY password_expired ENUM('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL;
+ALTER TABLE user MODIFY is_role enum('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL;
 
 -- Need to pre-fill mysql.proxies_priv with access for root even when upgrading from
 -- older versions
@@ -650,19 +646,19 @@ INSERT INTO proxies_priv SELECT * FROM tmp_proxies_priv WHERE @had_proxies_priv_
 DROP TABLE tmp_proxies_priv;
 
 # MDEV-4332 longer user names
-alter table mysql.user         modify User         char(80)  binary not null default '';
-alter table mysql.db           modify User         char(80)  binary not null default '';
-alter table mysql.tables_priv  modify User         char(80)  binary not null default '';
-alter table mysql.columns_priv modify User         char(80)  binary not null default '';
-alter table mysql.procs_priv   modify User         char(80)  binary not null default '';
-alter table mysql.proc         modify definer      char(141) collate utf8_bin not null default '';
-alter table mysql.event        modify definer      char(141) collate utf8_bin not null default '';
-alter table mysql.proxies_priv modify User         char(80)  COLLATE utf8_bin not null default '';
-alter table mysql.proxies_priv modify Proxied_user char(80)  COLLATE utf8_bin not null default '';
-alter table mysql.proxies_priv modify Grantor      char(141) COLLATE utf8_bin not null default '';
-alter table mysql.servers      modify Username     char(80)                   not null default '';
-alter table mysql.procs_priv   modify Grantor      char(141) COLLATE utf8_bin not null default '';
-alter table mysql.tables_priv  modify Grantor      char(141) COLLATE utf8_bin not null default '';
+alter table user         modify User         char(80)  binary not null default '';
+alter table db           modify User         char(80)  binary not null default '';
+alter table tables_priv  modify User         char(80)  binary not null default '';
+alter table columns_priv modify User         char(80)  binary not null default '';
+alter table procs_priv   modify User         char(80)  binary not null default '';
+alter table proc         modify definer      char(141) collate utf8_bin not null default '';
+alter table event        modify definer      char(141) collate utf8_bin not null default '';
+alter table proxies_priv modify User         char(80)  COLLATE utf8_bin not null default '';
+alter table proxies_priv modify Proxied_user char(80)  COLLATE utf8_bin not null default '';
+alter table proxies_priv modify Grantor      char(141) COLLATE utf8_bin not null default '';
+alter table servers      modify Username     char(80)                   not null default '';
+alter table procs_priv   modify Grantor      char(141) COLLATE utf8_bin not null default '';
+alter table tables_priv  modify Grantor      char(141) COLLATE utf8_bin not null default '';
 
 # Activate the new, possible modified privilege tables
 # This should not be needed, but gives us some extra testing that the above