Bug #100820 mysql_upgrade fails when we have db1.s1 and Db1.s1 procedures
Submitted: 11 Sep 2020 21:35 Modified: 12 Sep 2020 1:52
Reporter: Venkatesh Duggirala Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.7 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any

[11 Sep 2020 21:35] Venkatesh Duggirala
Description:
Cannot run mysql_upgrade on a server that has two procedures
that only differs in it's database name letter case.

How to repeat:
Execute: 

create database db1;
create database Db1; ( //Note Captial letter D)
delimiter //
create procedure Db1.s1() begin select 1; end//
create procedure db1.s1() begin select 1; end//

Now execute mysql_upgrade , it fails with duplicate primary key error
because mysql_upgrade executes 
"ALTER TABLE proc CONVERT TO CHARACTER SET utf8" which tries to remove
utf8_bin collation for "db" column and causes duplicate error.
[11 Sep 2020 22:14] Venkatesh Duggirala
Suggested fix:
Drop and Recreate the primary key seems to be the easy way.
We can do something like this in scripts/mysql_system_tables_fix.sql

++ Before correcting the proc table character set and collation, remove the ++primary key.
++# If it is not removed, it can cause problems if two procedures exists
++# with the same in two different database that differences only in its letter ++case.
++# Eg: Db1.p1 and db1.p1 (Notice letter "D" case difference in db name)
++ALTER TABLE proc DROP PRIMARY KEY;

#Correct the character set and collation.
ALTER TABLE proc CONVERT TO CHARACTER SET utf8;
# Reset some fields after the conversion and change comment from char(64) to text
ALTER TABLE proc  MODIFY db
                         char(64) collate utf8_bin DEFAULT '' NOT NULL,
                  MODIFY definer
                         char(93) collate utf8_bin DEFAULT '' NOT NULL,
                  MODIFY comment
                         text collate utf8_bin DEFAULT '' NOT NULL;

++# Recreate primary key after collation of 'db' is adjusted.
++ALTER TABLE proc ADD PRIMARY KEY(`db`,`name`,`type`);
[12 Sep 2020 1:52] MySQL Verification Team
Thank you for the bug report.