From d851dd619f9e373c453355fd613088488e2c3d5c Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 19 Oct 2018 20:18:34 +0200 Subject: MDEV-13912 mysql_upgrade: case (in)sensitivity for stored procedures mysql_upgrade used to convert all columns of mysql.db to utf8_general_ci and then back to utf8_bin. In two separate ALTER's. This failed if UNIQUE indexes in mysql.db contained entries that differ only in the letter case. --- mysql-test/r/lowercase_fs_off.result | 41 ++++++++++++++++++++++++++++++++++++ mysql-test/t/lowercase_fs_off.test | 10 +++++++++ scripts/mysql_system_tables_fix.sql | 10 ++++----- 3 files changed, 56 insertions(+), 5 deletions(-) diff --git a/mysql-test/r/lowercase_fs_off.result b/mysql-test/r/lowercase_fs_off.result index c3284b225dd..362661d8911 100644 --- a/mysql-test/r/lowercase_fs_off.result +++ b/mysql-test/r/lowercase_fs_off.result @@ -63,3 +63,44 @@ CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a= 1; RENAME TABLE t1 TO T1; ALTER TABLE T1 RENAME t1; DROP TABLE t1; +create database TEST; +create procedure TEST.pr() begin end; +create procedure test.pr() begin end; +Phase 1/4: Fixing views +Phase 2/4: Fixing table and database names +Phase 3/4: Checking and upgrading tables +Processing databases +information_schema +TEST +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 4/4: Running 'mysql_fix_privilege_tables' +OK +drop procedure test.pr; +drop database TEST; diff --git a/mysql-test/t/lowercase_fs_off.test b/mysql-test/t/lowercase_fs_off.test index 1be0351f9bc..8729efde02c 100644 --- a/mysql-test/t/lowercase_fs_off.test +++ b/mysql-test/t/lowercase_fs_off.test @@ -102,3 +102,13 @@ CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a= 1; RENAME TABLE t1 TO T1; ALTER TABLE T1 RENAME t1; DROP TABLE t1; + +# +# MDEV-13912 mysql_upgrade: case (in)sensitivity for stored procedures +# +create database TEST; +create procedure TEST.pr() begin end; +create procedure test.pr() begin end; +--exec $MYSQL_UPGRADE --force 2>&1 +drop procedure test.pr; +drop database TEST; diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql index 9bc61f38cea..dd792ce9f2f 100644 --- a/scripts/mysql_system_tables_fix.sql +++ b/scripts/mysql_system_tables_fix.sql @@ -468,15 +468,15 @@ ALTER TABLE proc MODIFY name char(64) DEFAULT '' NOT NULL, DEFAULT CHARACTER SET utf8; # Correct the character set and collation -ALTER TABLE proc CONVERT TO CHARACTER SET utf8; # Reset some fields after the conversion SET @alter_statement = CONCAT(" -ALTER TABLE proc MODIFY db - char(64) collate utf8_bin DEFAULT '' NOT NULL, +ALTER TABLE proc CONVERT TO CHARACTER SET utf8, + MODIFY db + char(64) binary DEFAULT '' NOT NULL, MODIFY definer - char(", @definer_name_length, ") collate utf8_bin DEFAULT '' NOT NULL, + char(", @definer_name_length, ") binary DEFAULT '' NOT NULL, MODIFY comment - char(64) collate utf8_bin DEFAULT '' NOT NULL + char(64) binary DEFAULT '' NOT NULL "); PREPARE alter_stmt FROM @alter_statement; EXECUTE alter_stmt; -- cgit v1.2.1