Bug #18415196 MYSQL_UPGRADE DUPLICATE KEY ERROR FOR MYSQL.USER FOR 5.5.35+, 5.6.15+, 5.7.3+

vsmurthys · vsmurthys · commit 7879b3eefd88 · 2014-08-01T14:18:28.000+05:30
Description: mysql_upgrade fails with below error, 
when there are duplicate entries(like 'root'@'LOCALHOST'
and 'root'@'localhost') in mysql.user table.
ERROR 1062 (23000) at line 1140: Duplicate entry 'localhost-root' for key 'PRIMARY'
FATAL ERROR: Upgrade failed

Analysis: As part of the bug 12917151 fix we are 
making all the hostnames as lower case hostnames.
So, this has been done by mysql_upgrade.
In case of above mentioned duplicate entries 
mysql_upgrade tries to change hostname to lowercase.
Since there is already 'root'@'localhost' exists.
it is failing with "duplicate entry" error.

Fix: Since its a valid error failure. We are 
making the error more verbose. So, that user will
delete the duplicate errors manually.
Along with existing error we are printing below
error as well.
ERROR 1644 (45000) at line 1153: Multiple accounts exist for @user_name, @host_name that differ only in Host lettercase; remove all except one of them
diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql
@@ -652,6 +652,25 @@ INSERT INTO tmp_proxies_priv VALUES ('localhost', 'root', '', '', TRUE, '', now(
 INSERT INTO proxies_priv SELECT * FROM tmp_proxies_priv WHERE @had_proxies_priv_table=0;
 DROP TABLE tmp_proxies_priv;
 
+-- Checking for any duplicate hostname and username combination are exists.
+-- If exits we will throw error.
+DROP PROCEDURE IF EXISTS mysql.count_duplicate_host_names;
+DELIMITER //
+CREATE PROCEDURE mysql.count_duplicate_host_names()
+BEGIN
+  SET @duplicate_hosts=0;
+  SET @duplicate_hosts=(SELECT count(*) FROM mysql.user GROUP BY user, lower(host) HAVING count(*) > 1 LIMIT 1);
+  select @duplicate_hosts;
+  IF @duplicate_hosts > 1 THEN
+    SIGNAL SQLSTATE '45000'  SET MESSAGE_TEXT = 'Multiple accounts exist for @user_name, @host_name that differ only in Host lettercase; remove all except one of them';
+  END IF;
+END //
+DELIMITER ;
+CALL mysql.count_duplicate_host_names();
+-- Get warnings (if any)
+SHOW WARNINGS;
+DROP PROCEDURE mysql.count_duplicate_host_names;
+
 # Convering the host name to lower case for existing users
 UPDATE user SET host=LOWER( host ) WHERE LOWER( host ) <> host;
 
