MySQL Bugs: #87738: mysql_upgrade failed even after removing query cache settings

Bug #87738	mysql_upgrade failed even after removing query cache settings
Submitted:	12 Sep 2017 12:57	Modified:	13 Oct 2017 9:18
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	8.0.3	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysql_upgrade

Description:
8.0.3 removes the query cache settings but I forgot to remove these settings when upgrading.

I had in /etc/my.cnf

...
query_cache_size = 0
query_cache_type = 0
...

These settings triggered 8.0.3 to fail on startup and go into a loop restarting (CentOS 7 community rpms) with the following error message:

2017-09-12T10:41:36.200470Z 0 [ERROR] [000000] unknown variable 'query_cache_size=0'

After removing the offending section mysqld starts up. When I ran mysql_upgrade it failed.

=== snip ===
previous version of mysql ran was 5.7.19

12:42:19 [myuser@my-server ~]$ sudo mysql_upgrade  # on 8.0.3
Checking if update is needed.
Checking server version.  <<===== Would be good for mysql_upgrade to confirm the version of the server!
Running queries to upgrade MySQL server.
Upgrading system table data.
Checking system database.
mysql.columns_priv                                 OK
mysql.component                                    OK
mysql.db                                           OK
mysql.db_slaves                                    OK
mysql.default_roles                                OK
mysql.dsns                                         OK
mysql.engine_cost                                  OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.global_grants                                OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.innodb_ddl_log                               OK
mysql.innodb_dynamic_metadata                      OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.inventory                                    OK
mysql.ndb_binlog_index                             OK
mysql.password_history                             OK
mysql.plugin                                       OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.role_edges                                   OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Found outdated sys schema version 1.5.1.
Upgrading the sys schema.
mysql_upgrade: [ERROR] 1146: Table 'performance_schema.global_status' doesn't exist
12:42:26 [myuser@my-server ~]$ rpm -q mysql-community-server
mysql-community-server-8.0.3-0.1.rc.el7.x86_64
12:43:48 [myuser@my-server ~]$
=== snip ===

After trying with mysql_upgrade --force this seemed to work.

How to repeat:
I'm not sure how reproducible this is but I basically did:
* install 5.7.19 as a slave
* settings as above were not removed
* start 8.0.3 and see that it failed, getting into a stop/start loop
* remove the bad entries from my.cnf
* see that mysqld starts up
* run mysql_upgrade and see the error.
* run mysql_upgrade --force (some time later) and see that this works

Suggested fix:
* Handle the issue with performance_schema.global_status better and do not require a --force to get this fixed.
* make mysql_upgrade report the version of mysqld it is talking to.

Hello Simon,

Thank you for the report and feedback.
I tried to reproduce the error i.e "mysql_upgrade: [ERROR] 1146: Table 'performance_schema.global_status' doesn't exist" but not seeing the issue when I tried binary in place upgrade from 5.7.19 -> 8.0.3.

I didn't setup replication and tried to upgrade slave(5.7.19) instead attempted on a standalone 5.7.19 instance. Is there anything else required to reproduce this issue? Could you please provide 5.7.19 conf file from your environment to see if that helps me trigger the error? Please let us know.  

Also, query_cache_size issue seems to be valid one as query_cache_size is removed in 8.0.3 and documented - https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_query_cache_si...

Thanks,
Umesh

I think the query cache settings should be accepted in my.cnf etc. and should then be ignored and/or just cause an deprecation warning.

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