| Bug #83982 | mysql_upgrade from 5.7.12 to 5.7.16 fails | ||
|---|---|---|---|
| Submitted: | 28 Nov 2016 8:46 | Modified: | 20 Jan 2017 8:21 |
| Reporter: | Oli Sennhauser | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Command-line Clients | Severity: | S2 (Serious) |
| Version: | 5.7.16 | OS: | Linux (n.a.) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | mysql_upgrade, slave_worker_info | ||
[20 Dec 2016 8:21]
MySQL Verification Team
Hello Oli, Thank you for the report. I tried to reproduce this issue by upgrading a slave instance from 5.7.12->5.7.17, later even with oldest instance 5.7.5->5.7.17 but not observed reported issue. Is this instance even older one i.e 5.6? Please let me know. This is the table structure from oldest instance 5.6.8-rc and up and I had no issues in upgrading(recommended way i.e 5.6.10 -> 5.6.35 (current) -> 5.7.17 (current)). CREATE TABLE IF NOT EXISTS slave_worker_info ( Id INTEGER UNSIGNED NOT NULL, Relay_log_name TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, Relay_log_pos BIGINT UNSIGNED NOT NULL, Master_log_name TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, Master_log_pos BIGINT UNSIGNED NOT NULL, Checkpoint_relay_log_name TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, Checkpoint_relay_log_pos BIGINT UNSIGNED NOT NULL, Checkpoint_master_log_name TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, Checkpoint_master_log_pos BIGINT UNSIGNED NOT NULL, Checkpoint_seqno INT UNSIGNED NOT NULL, Checkpoint_group_size INTEGER UNSIGNED NOT NULL, Checkpoint_group_bitmap BLOB NOT NULL, PRIMARY KEY(Id)) DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT 'Worker Information'"; Thanks, Umesh
[20 Dec 2016 8:26]
MySQL Verification Team
-- Another quick test confirms that problemed table exists from non-ga build 5.6.6-m9 which was not even GA -- setup replication rm -rf master scripts/mysql_install_db --basedir=/export/umesh/server/binaries/GABuilds/mysql-5.6.6-m9 --datadir=/export/umesh/server/binaries/GABuilds/mysql-5.6.6-m9/master -v bin/mysqld --no-defaults --basedir=/export/umesh/server/binaries/GABuilds/mysql-5.6.6-m9 --datadir=/export/umesh/server/binaries/GABuilds/mysql-5.6.6-m9/master --core-file --socket=/tmp/mysql_master.sock --port=3306 --log-error=/export/umesh/server/binaries/GABuilds/mysql-5.6.6-m9/master/log.err --log-bin=master-bin --server_id=1 2>&1 & rm -rf slave scripts/mysql_install_db --basedir=/export/umesh/server/binaries/GABuilds/mysql-5.6.6-m9 --datadir=/export/umesh/server/binaries/GABuilds/mysql-5.6.6-m9/slave -v bin/mysqld --no-defaults --basedir=/export/umesh/server/binaries/GABuilds/mysql-5.6.6-m9 --datadir=/export/umesh/server/binaries/GABuilds/mysql-5.6.6-m9/slave --core-file --socket=/tmp/mysql_slave.sock --port=3307 --log-error=/export/umesh/server/binaries/GABuilds/mysql-5.6.6-m9/slave/log.err --log-bin=slave-bin --server_id=2 --skip_slave_start --master-info-repository=TABLE --relay-log-info-repository=TABLE --slave_parallel_workers=12 2>&1 & -- upgrade slave to 5.7.17 -- bring down slave, perform binary upgrade from 5.6.6->5.7.17 and bring up slave. bin/mysqld --no-defaults --basedir=/export/umesh/server/binaries/GABuilds/mysql-5.7.17 --datadir=/export/umesh/server/binaries/GABuilds/mysql-5.6.6-m9/slave --core-file --socket=/tmp/mysql_slave.sock --port=3307 --log-error=/export/umesh/server/binaries/GABuilds/mysql-5.6.6-m9/slave/log.err --log-bin=slave-bin --server_id=2 --skip_slave_start --master-info-repository=TABLE --relay-log-info-repository=TABLE --slave_parallel_workers=12 2>&1 & -- upgrade should show the problem now [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.17: bin/mysql -uroot -p -S /tmp/mysql_slave.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.17-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@localhost [(none)]> \q Bye -- run upgrade [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.17: bin/mysql_upgrade -uroot -p -S /tmp/mysql_slave.sock Enter password: Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. mysql_upgrade: [ERROR] 1072: Key column 'Id' doesn't exist in table [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.17: Imho, this sounds like not a bug to me now.
[21 Jan 2017 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: We had a MySQL 5.7.12 Slave we wanted to upgrade to 5.7.16: shell> mysql_upgrade Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. mysql_upgrade: [ERROR] 1072: Key column 'Id' doesn't exist in table echo $? 5 shell> cat mysql_upgrade_info 5.7.12 How to repeat: Strace and General Query log shows as follows: recvfrom(3, "\1\0\0\1\3\33\0\0\2\3def\0\0\0\5Level\0\f-\0\34\0\0\0\375\1"..., 16384, 0, NULL, NULL) = 110 sendto(3, "\227\0\0\0\3ALTER TABLE slave_master_in"..., 155, 0, NULL, 0) = 155 recvfrom(3, "(\0\0\1\377$\4#42S21Duplicate column na"..., 16384, 0, NULL, NULL) = 44 sendto(3, "\235\0\0\0\3ALTER TABLE slave_master_in"..., 161, 0, NULL, 0) = 161 recvfrom(3, ",\0\0\1\377$\4#42S21Duplicate column na"..., 16384, 0, NULL, NULL) = 48 sendto(3, "3\0\0\0\3ALTER TABLE slave_master_in"..., 55, 0, NULL, 0) = 55 recvfrom(3, ".\0\0\1\0\0\0\2\0\0\0&Records: 0 Duplicat"..., 16384, 0, NULL, NULL) = 50 sendto(3, "\16\0\0\0\3SHOW WARNINGS", 18, 0, NULL, 0) = 18 recvfrom(3, "\1\0\0\1\3\33\0\0\2\3def\0\0\0\5Level\0\f-\0\34\0\0\0\375\1"..., 16384, 0, NULL, NULL) = 110 sendto(3, "3\0\0\0\3ALTER TABLE slave_worker_in"..., 55, 0, NULL, 0) = 55 recvfrom(3, ".\0\0\1\0\0\0\2\0\0\0&Records: 0 Duplicat"..., 16384, 0, NULL, NULL) = 50 sendto(3, "\16\0\0\0\3SHOW WARNINGS", 18, 0, NULL, 0) = 18 recvfrom(3, "\1\0\0\1\3\33\0\0\2\3def\0\0\0\5Level\0\f-\0\34\0\0\0\375\1"..., 16384, 0, NULL, NULL) = 110 sendto(3, "6\0\0\0\3ALTER TABLE slave_relay_log"..., 58, 0, NULL, 0) = 58 recvfrom(3, ".\0\0\1\0\0\0\2\0\0\0&Records: 0 Duplicat"..., 16384, 0, NULL, NULL) = 50 sendto(3, "\16\0\0\0\3SHOW WARNINGS", 18, 0, NULL, 0) = 18 recvfrom(3, "\1\0\0\1\3\33\0\0\2\3def\0\0\0\5Level\0\f-\0\34\0\0\0\375\1"..., 16384, 0, NULL, NULL) = 110 sendto(3, "/\0\0\0\3ALTER TABLE gtid_executed S"..., 51, 0, NULL, 0) = 51 recvfrom(3, ".\0\0\1\0\0\0\2\0\0\0&Records: 0 Duplicat"..., 16384, 0, NULL, NULL) = 50 sendto(3, "\16\0\0\0\3SHOW WARNINGS", 18, 0, NULL, 0) = 18 recvfrom(3, "\1\0\0\1\3\33\0\0\2\3def\0\0\0\5Level\0\f-\0\34\0\0\0\375\1"..., 16384, 0, NULL, NULL) = 110 sendto(3, "\23\1\0\0\3ALTER TABLE slave_master_in"..., 279, 0, NULL, 0) = 279 recvfrom(3, "-\0\0\1\377$\4#42S21Duplicate column na"..., 16384, 0, NULL, NULL) = 49 sendto(3, "\26\1\0\0\3ALTER TABLE slave_relay_log"..., 282, 0, NULL, 0) = 282 recvfrom(3, "-\0\0\1\377$\4#42S21Duplicate column na"..., 16384, 0, NULL, NULL) = 49 sendto(3, "\27\1\0\0\3ALTER TABLE slave_worker_in"..., 283, 0, NULL, 0) = 283 recvfrom(3, "/\0\0\1\3770\4#42000Key column 'Id' doe"..., 16384, 0, NULL, NULL) = 51 write(2, "mysql_upgrade", 13mysql_upgrade) = 13 write(2, ": [", 3: [) = 3 write(2, "ERROR", 5ERROR) = 5 write(2, "] ", 2] ) = 2 write(2, "1072", 41072) = 4 write(2, ": ", 2: ) = 2 write(2, "Key column 'Id' doesn't exist in"..., 38Key column 'Id' doesn't exist in table) = 38 write(2, "\n", 1 ) = 1 sendto(3, "\1\0\0\0\1", 5, 0, NULL, 0) = 5 shutdown(3, SHUT_RDWR) = 0 close(3) = 0 exit_group(5) = ? +++ exited with 5 +++ ALTER TABLE slave_worker_info ADD Channel_name CHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT 'The channel on which the slave is connected to a source. Used in Multisource Replication', DROP PRIMARY KEY, ADD PRIMARY KEY(Channel_name, Id) ; Old structure was: root@localhost [mysql]> show create table slave_worker_info\G *************************** 1. row *************************** Table: slave_worker_info Create Table: CREATE TABLE `slave_worker_info` ( `Master_id` int(10) unsigned NOT NULL, `Worker_id` int(10) unsigned NOT NULL, `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Relay_log_pos` bigint(20) unsigned NOT NULL, `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Master_log_pos` bigint(20) unsigned NOT NULL, `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL, `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL, `Checkpoint_seqno` int(10) unsigned NOT NULL, `Checkpoint_group_size` int(10) unsigned NOT NULL, `Checkpoint_group_bitmap` blob NOT NULL, PRIMARY KEY (`Master_id`,`Worker_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information' 1 row in set (0.00 sec) Workaround: drop table slave_worker_info and recreate from fresh 5.7.16 install. Then mysql_upgrade will work. Suggested fix: 1. Error message is not complete (missing table name). 2. I do not know if this table structure issue is from older releases (5.7.4/5.7.5) or if this is a bug. But IHMO this must be considered in upgrade skript or at least properly warned/noticed.