MDEV-6178 mysql_upgrade breaks databases with long user names

Added a 5.5-specific test which involves manual modification of system
tables. The problem itself was fixed in MDEV-6068

2 files changed, 145 insertions, 0 deletions

diff --git a/mysql-test/r/mysql_upgrade.result b/mysql-test/r/mysql_upgrade.result
index 285b00ae27f..fb831082dfe 100644
--- a/mysql-test/r/mysql_upgrade.result
+++ b/mysql-test/r/mysql_upgrade.result
@@ -287,4 +287,87 @@ performance_schema
 test
 Phase 3/3: Running 'mysql_fix_privilege_tables'...
 OK
+#
+# MDEV-4332 Increase username length from 16 characters
+# MDEV-6068, MDEV-6178 mysql_upgrade breaks databases with long 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 '';
+flush privileges;
+GRANT SELECT ON mysql.* TO very_long_user_name_number_1;
+GRANT SELECT ON mysql.* TO very_long_user_name_number_2;
+GRANT ALL ON *.* TO even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost WITH GRANT OPTION;
+GRANT INSERT ON mysql.user TO very_long_user_name_number_1;
+GRANT INSERT ON mysql.user TO very_long_user_name_number_2;
+GRANT UPDATE (User) ON mysql.db TO very_long_user_name_number_1;
+GRANT UPDATE (User) ON mysql.db TO very_long_user_name_number_2;
+CREATE PROCEDURE test.pr() BEGIN END;
+Phase 1/3: Fixing table and database names
+Phase 2/3: Checking and upgrading tables
+Processing databases
+information_schema
+mtr
+mtr.global_suppressions                            OK
+mtr.test_suppressions                              OK
+mysql
+mysql.columns_priv                                 OK
+mysql.db                                           OK
+mysql.event                                        OK
+mysql.func                                         OK
+mysql.help_category                                OK
+mysql.help_keyword                                 OK
+mysql.help_relation                                OK
+mysql.help_topic                                   OK
+mysql.host                                         OK
+mysql.ndb_binlog_index                             OK
+mysql.plugin                                       OK
+mysql.proc                                         OK
+mysql.procs_priv                                   OK
+mysql.proxies_priv                                 OK
+mysql.servers                                      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.user                                         OK
+performance_schema
+test
+Phase 3/3: Running 'mysql_fix_privilege_tables'...
+OK
+SELECT definer FROM mysql.proc WHERE db = 'test' AND name = 'pr';
+definer
+even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost
+SELECT grantor FROM mysql.tables_priv WHERE db = 'mysql' AND table_name = 'user';
+grantor
+even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost
+even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost
+DROP USER very_long_user_name_number_1, very_long_user_name_number_2, even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost;
+DROP PROCEDURE test.pr;
+alter table mysql.user         modify User         char(16)  binary not null default '';
+alter table mysql.db           modify User         char(16)  binary not null default '';
+alter table mysql.tables_priv  modify User         char(16)  binary not null default '';
+alter table mysql.columns_priv modify User         char(16)  binary not null default '';
+alter table mysql.procs_priv   modify User         char(16)  binary not null default '';
+alter table mysql.proc         modify definer      char(77) collate utf8_bin not null default '';
+alter table mysql.event        modify definer      char(77) collate utf8_bin not null default '';
+alter table mysql.proxies_priv modify User         char(16)  COLLATE utf8_bin not null default '';
+alter table mysql.proxies_priv modify Proxied_user char(16)  COLLATE utf8_bin not null default '';
+alter table mysql.proxies_priv modify Grantor      char(77) COLLATE utf8_bin not null default '';
+alter table mysql.servers      modify Username     char(64)                   not null default '';
+alter table mysql.procs_priv   modify Grantor      char(77) COLLATE utf8_bin not null default '';
+alter table mysql.tables_priv  modify Grantor      char(77) COLLATE utf8_bin not null default '';
+flush privileges;
 End of tests
diff --git a/mysql-test/t/mysql_upgrade.test b/mysql-test/t/mysql_upgrade.test
index 820144216ab..e4923e5f429 100644
--- a/mysql-test/t/mysql_upgrade.test
+++ b/mysql-test/t/mysql_upgrade.test
@@ -130,4 +130,66 @@ let $MYSQLD_DATADIR= `select @@datadir`;
 # so the following command should never fail.
 --remove_file $MYSQLD_DATADIR/mysql_upgrade_info
 
+
+# 5.5-only test (involves manual modification of system tables)
+--echo #
+--echo # MDEV-4332 Increase username length from 16 characters
+--echo # MDEV-6068, MDEV-6178 mysql_upgrade breaks databases with long user names
+--echo #
+
+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 '';
+flush privileges;
+
+connection default;
+GRANT SELECT ON mysql.* TO very_long_user_name_number_1;
+GRANT SELECT ON mysql.* TO very_long_user_name_number_2;
+GRANT ALL ON *.* TO even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost WITH GRANT OPTION;
+--change_user even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length
+
+GRANT INSERT ON mysql.user TO very_long_user_name_number_1;
+GRANT INSERT ON mysql.user TO very_long_user_name_number_2;
+GRANT UPDATE (User) ON mysql.db TO very_long_user_name_number_1;
+GRANT UPDATE (User) ON mysql.db TO very_long_user_name_number_2;
+
+CREATE PROCEDURE test.pr() BEGIN END;
+
+--change_user root
+
+--replace_result $MYSQLTEST_VARDIR var
+--exec $MYSQL_UPGRADE --force 2>&1
+
+SELECT definer FROM mysql.proc WHERE db = 'test' AND name = 'pr';
+SELECT grantor FROM mysql.tables_priv WHERE db = 'mysql' AND table_name = 'user';
+DROP USER very_long_user_name_number_1, very_long_user_name_number_2, even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost;
+DROP PROCEDURE test.pr;
+
+# Restore system tables
+
+alter table mysql.user         modify User         char(16)  binary not null default '';
+alter table mysql.db           modify User         char(16)  binary not null default '';
+alter table mysql.tables_priv  modify User         char(16)  binary not null default '';
+alter table mysql.columns_priv modify User         char(16)  binary not null default '';
+alter table mysql.procs_priv   modify User         char(16)  binary not null default '';
+alter table mysql.proc         modify definer      char(77) collate utf8_bin not null default '';
+alter table mysql.event        modify definer      char(77) collate utf8_bin not null default '';
+alter table mysql.proxies_priv modify User         char(16)  COLLATE utf8_bin not null default '';
+alter table mysql.proxies_priv modify Proxied_user char(16)  COLLATE utf8_bin not null default '';
+alter table mysql.proxies_priv modify Grantor      char(77) COLLATE utf8_bin not null default '';
+alter table mysql.servers      modify Username     char(64)                   not null default '';
+alter table mysql.procs_priv   modify Grantor      char(77) COLLATE utf8_bin not null default '';
+alter table mysql.tables_priv  modify Grantor      char(77) COLLATE utf8_bin not null default '';
+flush privileges;
+
 --echo End of tests