MDEV-20228 `mysql_upgrade` fails on every version upgrade: "ERROR 1267 (HY000) at line 7: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like'"

3 files changed, 133 insertions, 9 deletions

diff --git a/mysql-test/main/mysql_upgrade-20228.result b/mysql-test/main/mysql_upgrade-20228.result
new file mode 100644
index 00000000000..30ded626f5a
--- /dev/null
+++ b/mysql-test/main/mysql_upgrade-20228.result
@@ -0,0 +1,69 @@
+#
+# MDEV-20228 `mysql_upgrade` fails on every version upgrade: "ERROR 1267 (HY000) at line 7: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like'"
+#
+SET sql_mode="";
+SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
+#
+# Changing character_set_client and collation_connection
+# for the VIEW mysql.user to utf8mb4/utf8mb4_unicode_ci,
+# to emulate that mysql.user was created by 'mysqld --bootstrap'
+# using mysqld compiled with
+# -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci
+#
+SELECT CHARACTER_SET_CLIENT, COLLATION_CONNECTION
+FROM INFORMATION_SCHEMA.VIEWS
+WHERE TABLE_SCHEMA='mysql' AND TABLE_NAME='user';
+CHARACTER_SET_CLIENT	COLLATION_CONNECTION
+utf8mb4	utf8mb4_unicode_ci
+# Running mysql_upgrade
+Phase 1/7: Checking and upgrading mysql database
+Processing databases
+mysql
+mysql.column_stats                                 OK
+mysql.columns_priv                                 OK
+mysql.db                                           OK
+mysql.event                                        OK
+mysql.func                                         OK
+mysql.global_priv                                  OK
+mysql.gtid_slave_pos                               OK
+mysql.help_category                                OK
+mysql.help_keyword                                 OK
+mysql.help_relation                                OK
+mysql.help_topic                                   OK
+mysql.index_stats                                  OK
+mysql.innodb_index_stats                           OK
+mysql.innodb_table_stats                           OK
+mysql.plugin                                       OK
+mysql.proc                                         OK
+mysql.procs_priv                                   OK
+mysql.proxies_priv                                 OK
+mysql.roles_mapping                                OK
+mysql.servers                                      OK
+mysql.table_stats                                  OK
+mysql.tables_priv                                  OK
+mysql.time_zone                                    OK
+mysql.time_zone_leap_second                        OK
+mysql.time_zone_name                               OK
+mysql.time_zone_transition                         OK
+mysql.time_zone_transition_type                    OK
+mysql.transaction_registry                         OK
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views
+mysql.user                                         OK
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
+Processing databases
+information_schema
+mtr
+mtr.global_suppressions                            OK
+mtr.test_suppressions                              OK
+performance_schema
+test
+Phase 7/7: Running 'FLUSH PRIVILEGES'
+OK
+#
+# Restoring character_set_client and collation_connection back
+# so post-check returns the expected check-mysqld_1.result
+#
+SET NAMES latin1;
diff --git a/mysql-test/main/mysql_upgrade-20228.test b/mysql-test/main/mysql_upgrade-20228.test
new file mode 100644
index 00000000000..32c05fd1245
--- /dev/null
+++ b/mysql-test/main/mysql_upgrade-20228.test
@@ -0,0 +1,55 @@
+-- source include/mysql_upgrade_preparation.inc
+-- source include/have_working_dns.inc
+-- source include/have_innodb.inc
+-- source include/have_partition.inc
+
+if (!`SELECT count(*) FROM INFORMATION_SCHEMA.VIEWS
+  WHERE TABLE_SCHEMA='mysql' AND TABLE_NAME='user'
+  AND CHARACTER_SET_CLIENT='latin1'
+  AND COLLATION_CONNECTION='latin1_swedish_ci'`)
+{
+  Skip Needs character_set_client=latin1 and collation_connection=latin1_swedish_ci in mysql.views;
+}
+
+
+--echo #
+--echo # MDEV-20228 `mysql_upgrade` fails on every version upgrade: "ERROR 1267 (HY000) at line 7: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like'"
+--echo #
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+
+SET sql_mode="";
+SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
+
+--echo #
+--echo # Changing character_set_client and collation_connection
+--echo # for the VIEW mysql.user to utf8mb4/utf8mb4_unicode_ci,
+--echo # to emulate that mysql.user was created by 'mysqld --bootstrap'
+--echo # using mysqld compiled with
+--echo # -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci
+--echo #
+
+--disable_query_log
+let $def= `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA='mysql' AND TABLE_NAME='user'`;
+--eval ALTER VIEW mysql.user AS $def;
+--enable_query_log
+
+SELECT CHARACTER_SET_CLIENT, COLLATION_CONNECTION
+FROM INFORMATION_SCHEMA.VIEWS
+WHERE TABLE_SCHEMA='mysql' AND TABLE_NAME='user';
+
+--echo # Running mysql_upgrade
+--exec $MYSQL_UPGRADE --default-character-set=utf8mb4 --force 2>&1
+--file_exists $MYSQLD_DATADIR/mysql_upgrade_info
+--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
+
+--echo #
+--echo # Restoring character_set_client and collation_connection back
+--echo # so post-check returns the expected check-mysqld_1.result
+--echo #
+
+SET NAMES latin1;
+--disable_query_log
+let $def= `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA='mysql' AND TABLE_NAME='user'`;
+--eval ALTER VIEW mysql.user AS $def;
+--enable_query_log
diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql
index 2415ce5ef0a..ba465bf576f 100644
--- a/scripts/mysql_system_tables_fix.sql
+++ b/scripts/mysql_system_tables_fix.sql
@@ -34,7 +34,7 @@ ALTER TABLE user add File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N'
 
 # Detect whether or not we had the Grant_priv column
 SET @hadGrantPriv:=0;
-SELECT @hadGrantPriv:=1 FROM user WHERE Grant_priv LIKE '%';
+SELECT @hadGrantPriv:=1 FROM user WHERE Grant_priv IS NOT NULL;
 
 ALTER TABLE user add Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
                  add References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
@@ -119,7 +119,7 @@ ALTER TABLE func add type enum ('function','aggregate') COLLATE utf8_general_ci
 
 # Detect whether we had Show_db_priv
 SET @hadShowDbPriv:=0;
-SELECT @hadShowDbPriv:=1 FROM user WHERE Show_db_priv LIKE '%';
+SELECT @hadShowDbPriv:=1 FROM user WHERE Show_db_priv IS NOT NULL;
 
 ALTER TABLE user
 ADD Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_priv,
@@ -267,7 +267,7 @@ ALTER TABLE plugin
 # Detect whether we had Create_view_priv
 #
 SET @hadCreateViewPriv:=0;
-SELECT @hadCreateViewPriv:=1 FROM user WHERE Create_view_priv LIKE '%';
+SELECT @hadCreateViewPriv:=1 FROM user WHERE Create_view_priv IS NOT NULL;
 
 #
 # Create VIEWs privileges (v5.0)
@@ -297,7 +297,7 @@ UPDATE user SET Create_view_priv=Create_priv, Show_view_priv=Create_priv where u
 #
 #
 SET @hadCreateRoutinePriv:=0;
-SELECT @hadCreateRoutinePriv:=1 FROM user WHERE Create_routine_priv LIKE '%';
+SELECT @hadCreateRoutinePriv:=1 FROM user WHERE Create_routine_priv IS NOT NULL;
 
 #
 # Create PROCEDUREs privileges (v5.0)
@@ -339,7 +339,7 @@ ALTER TABLE user MODIFY max_user_connections int(11) DEFAULT '0' NOT NULL AFTER
 #
 
 SET @hadCreateUserPriv:=0;
-SELECT @hadCreateUserPriv:=1 FROM user WHERE Create_user_priv LIKE '%';
+SELECT @hadCreateUserPriv:=1 FROM user WHERE Create_user_priv IS NOT NULL;
 
 ALTER TABLE user ADD Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_routine_priv;
 ALTER TABLE user MODIFY Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_routine_priv;
@@ -505,7 +505,7 @@ ALTER TABLE proc ADD aggregate enum('NONE', 'GROUP') DEFAULT 'NONE' NOT NULL
 # EVENT privilege
 #
 SET @hadEventPriv := 0;
-SELECT @hadEventPriv :=1 FROM user WHERE Event_priv LIKE '%';
+SELECT @hadEventPriv :=1 FROM user WHERE Event_priv IS NOT NULL;
 
 ALTER TABLE user ADD Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL AFTER Create_user_priv;
 ALTER TABLE user MODIFY Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL AFTER Create_user_priv;
@@ -599,7 +599,7 @@ set global event_scheduler=original;
 #
 
 SET @hadTriggerPriv := 0;
-SELECT @hadTriggerPriv :=1 FROM user WHERE Trigger_priv LIKE '%';
+SELECT @hadTriggerPriv :=1 FROM user WHERE Trigger_priv IS NOT NULL;
 
 ALTER TABLE user ADD Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Event_priv;
 ALTER TABLE user MODIFY Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Event_priv;
@@ -614,7 +614,7 @@ UPDATE user SET Trigger_priv=Super_priv WHERE @hadTriggerPriv = 0;
 #
 
 SET @hadCreateTablespacePriv := 0;
-SELECT @hadCreateTablespacePriv :=1 FROM user WHERE Create_tablespace_priv LIKE '%';
+SELECT @hadCreateTablespacePriv :=1 FROM user WHERE Create_tablespace_priv IS NOT NULL;
 
 ALTER TABLE user ADD Create_tablespace_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Trigger_priv;
 ALTER TABLE user MODIFY Create_tablespace_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Trigger_priv;
@@ -629,7 +629,7 @@ ALTER TABLE user change Truncate_versioning_priv Delete_history_priv enum('N','Y
 ALTER TABLE db change Truncate_versioning_priv Delete_history_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL DEFAULT 'N';
 
 SET @had_user_delete_history_priv := 0;
-SELECT @had_user_delete_history_priv :=1 FROM user WHERE Delete_history_priv LIKE '%';
+SELECT @had_user_delete_history_priv :=1 FROM user WHERE Delete_history_priv IS NOT NULL;
 
 ALTER TABLE user add Delete_history_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL DEFAULT 'N' after Create_tablespace_priv;
 ALTER TABLE user modify Delete_history_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL DEFAULT 'N';