diff options
| author | Venkata Sidagam <venkata.sidagam@oracle.com> | 2014-08-01 14:18:28 +0530 |
|---|---|---|
| committer | Venkata Sidagam <venkata.sidagam@oracle.com> | 2014-08-01 14:18:28 +0530 |
| commit | ace82cadcdff69a401f4c713caaeaa2148dfafbf (patch) | |
| tree | e551651d18f02aea144684dbcf4264cb89b4019d /scripts | |
| parent | 037ab0b943840fa11a2c809a1b0d7af09e3ed5a6 (diff) | |
| download | mariadb-git-ace82cadcdff69a401f4c713caaeaa2148dfafbf.tar.gz | |
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
Diffstat (limited to 'scripts')
| -rw-r--r-- | scripts/mysql_system_tables_fix.sql | 19 |
1 files changed, 19 insertions, 0 deletions
diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql index 23c8b7f7e5a..0578e37fb7a 100644 --- 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; |