Description:
I'm not sure how this situation arose but I saw this when upgrading a server from 5.6.27 to 5.7.18. After upgrading the binaries I tried to run mysql_upgrade with the following result:
[myuser@myhost ~]$ sudo mysql_upgrade
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
mysql_upgrade: [ERROR] 1062: Duplicate entry ‘’ for key ‘PRIMARY’
This error message is not very helpful as it does not indicate where the problem is, or what I need to do about it.
How to repeat:
See above.
Suggested fix:
I did further investigations and saw this:
(1) strace showed part of the command that was failing:
sendto(3, “\23\1\0\0\3ALTER TABLE slave_master_in”..., 279, 0, NULL, 0) = 279
recvfrom(3, “-\0\0\1\377&\4#23000Duplicate entry ‘’ “..., 16384, 0, NULL, NULL) = 49
(2) I tried to find the full command that was being executed by using strings:
[myuser@myhost ~]$ strings `which mysql_upgrade` | grep “ALTER TABLE slave_master_info”
ALTER TABLE slave_master_info ADD Ssl_crl TEXT CHARACTER SET utf8 COLLATE utf8_bin COMMENT ‘The file used for the Certificate Revocation List (CRL)‘;
ALTER TABLE slave_master_info ADD Ssl_crlpath TEXT CHARACTER SET utf8 COLLATE utf8_bin COMMENT ‘The path used for Certificate Revocation List (CRL) files’;
ALTER TABLE slave_master_info STATS_PERSISTENT=0;
ALTER TABLE slave_master_info
ALTER TABLE slave_master_info ADD Tls_version TEXT CHARACTER SET utf8 COLLATE utf8_bin COMMENT ‘Tls version’;
ALTER TABLE slave_master_info
These commands if run interactively worked or talked about the column existing so clearly there were 2 statements I was missing.
(3) given this was a duplicate key error I looked at the table definition and content of the table.
CREATE TABLE `slave_master_info` (
…
`Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘The host name of the master.‘,
...
`Port` int(10) unsigned NOT NULL COMMENT ‘The network port used to connect to the master.‘,
…
PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT=‘Master Information’
and checked the table:
myuser@myhost [mysql]> select host,port from slave_master_info;
+------------------------+------+
| host | port |
+------------------------+------+
| masterhost.example.com | 3306 |
| masterhost.example.com | 3306 |
+------------------------+------+
2 rows in set (0.00 sec)
Obviously masterhost.example.com was not the real hostname but the same host/port was shown in the table.
I removed the extra row and then was able to run mysql_upgrade again with no problems.
It's not clear how this table got into this situation but I guess if it happens to me then it could happen to others. My suggestion here would be to provide the statement that that was executed if an unexpected error happens as then the DBA has a better clue as to where to look for the problem and hopefully resolve it more quickly.
So amend to something of the form:
[myuser@myhost ~]$ sudo mysql_upgrade
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
mysql_upgrade: [ERROR] 1062: Duplicate entry ‘’ for key ‘PRIMARY’
while executing: <some SQL related to mysql.slave_master_info ....>
