MySQL Bugs: #100820: mysql_upgrade fails when we have db1.s1 and Db1.s1 procedures

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:	Assigned Account	CPU Architecture:	Any

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.

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`);

Thank you for the bug report.