MySQL Bugs: #72066: mysql_upgrade duplicate key error for mysql.user for 5.5.35+, 5.6.15+, 5.7.3+

Bug #72066	mysql_upgrade duplicate key error for mysql.user for 5.5.35+, 5.6.15+, 5.7.3+
Submitted:	18 Mar 2014 1:54	Modified:	27 Aug 2014 23:11
Reporter:	Jesper wisborg Krogh	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S3 (Non-critical)
Version:	5.1.73 5.5.35 5.6.15 5.7.3	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When you upgrade to:

   * 5.1.73
   * 5.5.35+
   * 5.6.15+
   * 5.7.3+

and run mysql_upgrade, it will try to change all hostnames to lower case. This is the fix to bugs:

   * Bug 62254/Bug 12917151 - mysql_install_db still creates users with case sensitive hostname
   * Bug 62255/Bug 12917164 - DROP USER can't drop users with legacy upper case host name anymore

If you have two rows in mysql.user with the same username and hostname except for the capitalisation of the hostname (this is for example possible due to bug 62254/bug 12917151), mysql_upgrade will cause a duplicate key error and terminate.

How to repeat:
5.5.34> INSERT INTO mysql.user SELECT 'LOCALHOST', 'root', Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections, plugin, authentication_string FROM mysql.user WHERE User = 'root' AND Host = 'localhost';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

5.5.34> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

5.5.34> SELECT User, Host FROM mysql.user WHERE User = 'root' AND Host IN ('localhost', 'LOCALHOST');
+------+-----------+
| User | Host      |
+------+-----------+
| root | LOCALHOST |
| root | localhost |
+------+-----------+
2 rows in set (0.00 sec)

Upgrade:

shell$ mysql_upgrade
...
Running 'mysql_fix_privilege_tables'...
ERROR 1062 (23000) at line 1140: Duplicate entry 'localhost-root' for key 'PRIMARY'
FATAL ERROR: Upgrade failed

From the general log:

UPDATE user SET host=LOWER( host ) WHERE LOWER( host ) <> host

Suggested fix:
At least one of:

  * Have mysql_upgrade handle the case where another user exists user@LOWER(host)
  * Document that duplicates should be removed manually before upgrading

Noted in 5.5.40, 5.6.21, 5.7.5 changelogs.

mysql_upgrade could fail if the mysql.user table contained multiple
accounts with the same user name and host name where the host name
differed in lettercase. This is still not permitted, but now
mysql_upgrade prints a more informative error message to indicate the
nature of the problem:

ERROR 1644 (45000): Multiple accounts exist for user_name, host_name
that differ only in Host lettercase; remove all except one of them

revno: 4693
committer: Venkata Sidagam <venkata.sidagam@oracle.com>
branch nick: 5.5
timestamp: Fri 2014-08-01 14:18:28 +0530
message:
  Bug #18415196 MYSQL_UPGRADE DUPLICATE KEY ERROR FOR MYSQL.USER FOR 5.5.35+, 5.6.15+, 5.7.3+
  
  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

revno: 4694
committer: Venkata Sidagam <venkata.sidagam@oracle.com>
branch nick: 5.5
timestamp: Fri 2014-08-01 17:09:55 +0530
message:
  Bug #18415196 MYSQL_UPGRADE DUPLICATE KEY ERROR FOR MYSQL.USER FOR 5.5.35+, 5.6.15+, 5.7.3+
  
  Follow-up patch. Removed unwanted code.