query_cache_limit removed in mysql-8, upgrade broke

[Mechano]

During upgrade from 19.04 server to 19.10 server.

ProblemType: Package
DistroRelease: Ubuntu 19.10
Package: mysql-server-8.0 8.0.17-0ubuntu2
ProcVersionSignature: Ubuntu 5.0.0-32.34-generic 5.0.21
Uname: Linux 5.0.0-32-generic x86_64
ApportVersion: 2.20.11-0ubuntu8.1
Architecture: amd64
Date: Fri Nov 1 09:10:56 2019
ErrorMessage: il sottoprocesso installato pacchetto mysql-server-8.0 script post-installation ha restituito lo stato di errore 1
InstallationDate: Installed on 2016-05-16 (1263 days ago)
InstallationMedia: Ubuntu-Server 14.04.4 LTS "Trusty Tahr" - Release amd64 (20160217.1)
Logs.var.log.daemon.log:

MySQLConf.etc.mysql.conf.d.mysql.cnf: [mysql]
MySQLConf.etc.mysql.conf.d.mysqld_safe_syslog.cnf:
 [mysqld_safe]
 syslog
MySQLConf.etc.mysql.conf.d.mysqldump.cnf:
 [mysqldump]
 quick
 quote-names
 max_allowed_packet = 16M
MySQLConf.etc.mysql.mysql.conf.d.mysqld_safe_syslog.cnf:
 [mysqld_safe]
 syslog
ProcCmdline: BOOT_IMAGE=/boot/vmlinuz-5.0.0-32-generic root=UUID=65a182cd-506f-4ce0-8bb8-51cc175d6001 ro
Python3Details: /usr/bin/python3.7, Python 3.7.5rc1, python3-minimal, 3.7.5-1
PythonDetails: /usr/bin/python2.7, Python 2.7.17rc1, python-minimal, 2.7.17-1
RelatedPackageVersions:
 dpkg 1.19.7ubuntu2
 apt 1.9.4
SourcePackage: mysql-8.0
Title: package mysql-hostname-8.0 8.0.17-0ubuntu2 failed to install/upgrade: il sottoprocesso installato pacchetto mysql-hostname-8.0 script post-installation ha restituito lo stato di errore 1
UpgradeStatus: Upgraded to eoan on 2019-11-01 (0 days ago)

[Andreas Hasenack (ahasenack)]

Thanks for filing this bug in Ubuntu.

I see the following messages in the mysql log file:

2019-11-01T08:12:31.317917Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2019-11-01T08:12:31.319323Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.17-0ubuntu2) starting as process 11983
2019-11-01T08:12:32.177301Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-11-01T08:12:32.179958Z 0 [ERROR] [MY-000067] [Server] unknown variable 'query_cache_limit=1M'.
2019-11-01T08:12:32.180190Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-11-01T08:12:33.958062Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.17-0ubuntu2) (Ubuntu).

From https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html, we can see that the config option query_cache_limit was removed in 8.0.3:
"""
 query_cache_limit: Do not cache results that are bigger than this. Removed in MySQL 8.0.3.
"""

That option is used by default in disco's /etc/mysql/mysql.conf.d/mysqld.cnf file, and removed on a release upgrade to mysql 8. I just did such a test. But you have it in /etc/mysql/my.cnf, I wonder if the upgrade procedure looks at that file.

In any case, I suggest to remove this option from your configuration and then try to finish the upgrade:

sudo apt update
sudo apt -f install

[Andreas Hasenack (ahasenack)]

@lars, is this something that should be taken care of by the package during upgrades?

[Lars Tangvald (lars-tangvald)]

Hi,

Query cache support was removed in 8.0 (ref https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html and https://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/).

Since these two options were in the 5.7 default config we do try to automatically handle it on upgrades, but the default config file location is /etc/mysql/mysql.conf.d/mysqld.cnf, so it only checks that location (/etc/mysql/my.cnf is also used by MariaDB, so by default it just includes the directory for the currently installed variant).

The fix for this should simply be to comment out the two lines with query_cache, but if you receive any similar errors after doing that you can check the mysql-nutshell page linked above to see if there's been any change for it.