MySQL Bugs: #92988: 8.0.13 - mysql_upgrade fails due to primary key error without stating the table

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:	None
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

Description:
When upgrading a server to 8.0.13 from 5.7.23 I see that when running the mysql_upgrade step I get a failure which does not mention the source of the failure. This is due to me adding in my 8.0.13 configuration setting sql_require_primary_key = 1.

The error is not very pleasant as it gives no clue as to which table this causes problems with.
The problem may be with system tables? It is more likely it's related to non-system user tables but I can't see that here, so I'll need to inspect the server source code to try to find this out.

How to repeat:
Stop 5.7, change /etc/my.cnf, start 8.0 and then:

# mysql_upgrade
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.
# rpm -q mysql-community-server
mysql-community-server-8.0.13-1.el7.x86_64

Suggested fix:
Things to improve this:
* Improve the message so that the statement that is executed is shown, together with the error message.
* Add this check to the upgrade checker (maybe just check for tables with no primary key and make it a warning if sql_require_primary_key = 0, and an error otherwise.

I know that mysql_upgrade may go away and be incorporated into the server in future releases as mentioned at the OOW 2018 conference but this same change will then be needed inside the server.

Note: I reported a problem with mysql_upgrade recently asking for the statement and error to be shown and this wasn't addressed. It would have been handy and clarified the problem here.

See: https://bugs.mysql.com/bug.php?id=86462 and see end of "suggested fix".

Workaround:
Go to mysqld and run set global sql_require_primary_key = 0; then change it back.

Hello Simon,

Thank you for the report and feedback!

regards,
Umesh

##
-- 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.

So still not sure if issue related to system tables or not

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.

Suggesst: https://github.com/mysql/mysql-server/pull/226

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.

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.

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.