Bug #86462 mysql_ugprade: improve handling of upgrade errors
Submitted: 25 May 2017 16:56 Modified: 5 Jul 2017 4:57
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S4 (Feature request)
Version:5.7.18, 5.7.21 OS:Any
Assigned to: CPU Architecture:Any
Tags: 5.7.18, mysql_upgrade, upgrade
Triage: Needs Triage: D5 (Feature request)

[25 May 2017 16:56] Simon Mudd
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 ....>
[5 Jul 2017 4:57] Umesh Shastry
Hello Simon,

Thank you for the feature request!

Thanks,
Umesh
[5 Jul 2017 5:04] Umesh Shastry
I tried to reproduce this many times starting with 5.6.26, 5.6.36 and 5.7.18 but no luck as there is no simple way to create duplicate records(PRIMARY KEY (`Host`,`Port`)). This is FR to improve handling of upgrade errors, but really interesting to know how this happened.
[23 Jan 12:05] Umesh Shastry
Bug #89323 marked as duplicate of this one.

[22 Jan 9:26] Oli Sennhauser
Yes, Shane. As mentioned in the "suggested fix" section a more clear error message is my primary wish.

My secondary wish is, that the mysql_upgrade tool can handle very old (pre GA?) DD table structures...