Bug #92988 | 8.0.13 - mysql_upgrade fails due to primary key error without stating the table | ||
---|---|---|---|
Submitted: | 29 Oct 2018 13:39 | Modified: | 12 Dec 2018 22:39 |
Reporter: | Simon Mudd (OCA) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Command-line Clients | Severity: | S3 (Non-critical) |
Version: | 8.0.13 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | mysql_upgrade, upgrade_checker |
[29 Oct 2018 13:39]
Simon Mudd
[29 Oct 2018 15:17]
MySQL Verification Team
Hello Simon, Thank you for the report and feedback! regards, Umesh
[29 Oct 2018 15:18]
MySQL Verification Team
## -- 5.7.24 instance rm -rf 92988 bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/92988 --log-error-verbosity=3 bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/92988 --core-file --socket=/tmp/mysql_ushastry.sock --port=9999 --log-error=$PWD/92988/log.err --default-authentication-plugin=mysql_native_password 2>&1 & bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.24 MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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. mysql> create database db; Query OK, 1 row affected (0.00 sec) mysql> create table db.t1(id int not null); Query OK, 0 rows affected (0.00 sec) mysql> \q Bye bin/mysqladmin -uroot -S /tmp/mysql_ushastry.sock shutdown - Bring up 8.0, make sure --sql_require_primary_key=1, attempt to run mysql_upgrade bin/mysqld --no-defaults --basedir=$PWD --datadir=/export/umesh/server/binaries/GABuilds/mysql-5.7.24/92988 --core-file --socket=/tmp/mysql_ushastry.sock --port=9999 --log-error=/export/umesh/server/binaries/GABuilds/mysql-5.7.24/92988/log.err --sql_require_primary_key=1 2>&1 & bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2018, 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. mysql> \q Bye bin/mysql_upgrade -uroot -S /tmp/mysql_ushastry.sock Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. mysql_upgrade: [ERROR] 3750: Unable to create a table without PK, when system variable 'sql_require_primary_key' is set. Add a PK to the table or unset this variable to avoid this message. Note that tables without PK can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
[29 Oct 2018 19:21]
Simon Mudd
So still not sure if issue related to system tables or not
[30 Oct 2018 13:22]
Simon Mudd
Now I am. Turning on the general_log I see: 2018-10-30T13:19:24.462184Z 2347 Query CREATE TABLE IF NOT EXISTS general_log (event_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), user_host MEDIUMTEXT NOT NULL, thread_id BIGINT(21) UNSIGNED NOT NULL, server_id INTEGER UNSIGNED NOT NULL, command_type VARCHAR(64) NOT NULL, argument MEDIUMBLOB NOT NULL) engine=CSV CHARACTER SET utf8 comment="General log" So it looks like this is what is breaking the upgrade. Consequently it looks to me as if while doing an upgrade the session setting for sql_require_primary_key should be set to 0.
[30 Oct 2018 17:35]
Simon Mudd
Suggesst: https://github.com/mysql/mysql-server/pull/226
[31 Oct 2018 7:38]
Simon Mudd
Note: when a patch is applied for this as there's no "server code" changed it would be good to provide updated 8.0.13 packages so that 8.0.13 can be used as is. I'd really prefer not to wait 2 months for 8.0.14 to fix this when really it's only an issue with the mysql_upgrade program. For rpm this is provided on RHEL7 as part of the mysql-community-server-8.0.13-1.el7.x86_64 rpm. Building a fixed -2.el7 would be nice.
[31 Oct 2018 14:28]
OCA Admin
Contribution submitted via Github - Fix for bug 92988: disable sql_require_primary_key while [re]creating general_lo (*) Contribution by Simon Mudd (Github sjmudd, mysql-server/pull/226#issuecomment-434697395): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: git_patch_227015859.txt (text/plain), 2.77 KiB.
[12 Dec 2018 22:39]
Paul DuBois
Posted by developer: Fixed in 8.0.14. mysql_upgrade could fail to upgrade certain system tables if the sql_require_primary_key system variable was enabled.