MySQL Bugs: #89267: Unable to access 8.0.4 server after starting on top of 5.7.20 database

Bug #89267	Unable to access 8.0.4 server after starting on top of 5.7.20 database
Submitted:	16 Jan 2018 15:34	Modified:	21 Mar 2018 15:21
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	8.0.4	OS:	CentOS (7)
Assigned to:	Rahul Sisondia	CPU Architecture:	Any
Tags:	mysql_upgrade, unknown error, upgrade

Description:
I do a 5.7.20 inplace upgrade to 8.0.4. Stop the server.
Adjust /etc/my.cnf
Swap binaries and start 8.0.4.

Partial logging shows:

...
2018-01-16T14:42:28.303837Z 2 [Note] [MY-010006] Using data dictionary with version '80004'.
2018-01-16T14:42:28.536859Z 0 [Note] [MY-011066] InnoDB: DDL log recovery : begin
2018-01-16T14:42:28.536916Z 0 [Note] [MY-011066] InnoDB: DDL log recovery : end
2018-01-16T14:42:29.114570Z 0 [Note] [MY-010305] Auto generated SSL certificates are placed in data directory.
2018-01-16T14:42:29.115197Z 0 [Warning] [MY-010068] CA certificate ca.pem is self signed.
2018-01-16T14:42:29.202054Z 0 [Note] [MY-010309] Auto generated RSA key files through --sha256_password_auto_generate_rsa_keys are placed in data directory.
2018-01-16T14:42:29.202070Z 0 [Note] [MY-010308] Skipping generation of RSA key pair through --caching_sha2_password_auto_generate_rsa_keys as key files are present in data directory.
2018-01-16T14:42:29.204055Z 0 [ERROR] [MY-001146] Table 'mysql.component' doesn't exist
2018-01-16T14:42:29.204235Z 0 [Warning] [MY-003543] The mysql.component table is missing or has an incorrect definition.
2018-01-16T14:42:29.208307Z 0 [Warning] [MY-010405] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2018-01-16T14:42:29.208355Z 0 [ERROR] [MY-010422] Error in checking mysql.slave_master_info repository info type of TABLE.
2018-01-16T14:42:29.208373Z 0 [ERROR] [MY-010415] Error creating master info: Error checking repositories.
2018-01-16T14:42:29.208378Z 0 [ERROR] [MY-010426] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.
2018-01-16T14:42:29.208394Z 0 [ERROR] [MY-010529] Failed to create or recover replication info repositories.
2018-01-16T14:42:29.208414Z 0 [Note] [MY-011029] Some of the channels are not created/initialized properly. Check for additional messages above. You will not be able to start replication on those channels until the issue is resolved and the server restarted.
...

My automation has started mysqld this way (systemd) by doing this:

Settings MYSQLD_OPTS to --skip-slave-start --skip-grant-tables --skip-networking --skip-innodb-buffer-pool-dump-at-shutdown --explicit-defaults-for-timestamp --skip-read-only

However, when trying to run mysql_upgrade accessing the server via a socket access fails.

I try to connect using the 8.0 mysql client and get:

[root@myserver ~]# mysql
ERROR 2000 (HY000): Unknown MySQL error

Configuration of the /root/.my.cnf file shows

[client]
user=root
socket=/path/to/mysql.sock
password=root_password

So this breaks my upgrade automation.

However, If I stop mysqld and restart it then access works and I can run mysql_upgrade fine.

How to repeat:
See above.

Suggested fix:
I'm not sure which of the above option is preventing access to the server but clearly authentication is not working. That should work "enough" for 8.0.4 to run even with a 5.7.20 disk layout.  It worked with 8.0.3.

I'll try and provide more information but hopefully this gives enough pointers as to the problem.
If not please feel free to ask.

Not mentioned. This was with CentOS 7 rpms.

A colleague said to me:  For being able to connect to MySQL, you need to remove "--skip-grant-tables".

For clarity on a previous comment: "stop mysqld and restart" refers to starting mysql normally with no special options, not to restart with the special startup options shown.

Hello Simon,

Thank you for the report and feedback.

Thanks,
Umesh

test results

Attachment: 89267.results (application/octet-stream, text), 6.96 KiB.

Workaround - Don't specify the password at the client while connecting to the
server which is started with --skip-grant-tables option.

Something like this seems to work:

1. stop 5.7.21
2. swap binaries for 8.0.4
3. start mysql with skip-networking and skip-grant-tables etc.
4. sudo mysql_upgrade --no-defaults $(sudo my_print_defaults client | egrep '^--socket' | head -1)
5. restart mysql

Posted by developer:
 
Fixed in 8.0.11.

If the server was started with --skip-grant-tables, clients that
authenticate using the caching_sha2_password plugin were unable to
connect.