7

After an upgrade to Ubuntu 20.04 LTS, my WordPress site is stuck in Maintenance mode. mySQL is trying to start with these same 10 lines repeating every couple of seconds in the /var/log/mysql/error.log file. At least for me, a quick search of the term unknown variable 'query_cache_limit=1M' only yields pages telling how to implement this parameter. How do I get it working again?

2020-11-12T16:12:25.733349Z 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.
2020-11-12T16:12:25.735469Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.22-0ubuntu0.20.04.2) starting as process 20068
2020-11-12T16:12:25.746199Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-11-12T16:12:26.131087Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-11-12T16:12:26.307241Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2020-11-12T16:12:26.370126Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-11-12T16:12:26.370481Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2020-11-12T16:12:26.377752Z 0 [ERROR] [MY-000067] [Server] unknown variable 'query_cache_limit=1M'.
2020-11-12T16:12:26.378449Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-11-12T16:12:27.821939Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.22-0ubuntu0.20.04.2)  (Ubuntu).
wruckie
  • 546
  • 5
  • 18

1 Answers1

16

After an upgrade to Ubuntu 20.04 LTS, my WordPress site was stuck in Maintenance mode. (This was my 4th machine to upgrade and the first 3 machines upgraded without any glitches.) After checking apache2, I went looking at mySQL. During the install, I had glimpsed a red failure in the middle of Ubuntu 20.04 upgrade churn but there was no way to go back and see what it had said... So I tailed the mySQL logs and it appeared that mySQL was trying repeatedly to start itself. It kept repeating the same 10 lines every couple seconds. The only line that seemed to be problematic was the unknown variable line. So I sent looking for that variable ....

I found query_cache_limit and removed it from the /etc/mysql/my.cnf file. As soon as I removed it, the error log changed to a query_cache_size one so I removed that one too from my.cnf file. Within seconds, mySQL restarted successfully and I was back in business.

# * Query Cache Configuration
#
query_cache_limit = 1M  
query_cache_size        = 16M 
#
# * Logging and Replication
#

Postmortem: Don't know how long these parameters have been present, or if I introduced them (I suspect I did), because this VPS has been running for 6+ years. Removing them got mySQL running again. I still don't know why mySQL choked on them - seems that they could have easily just ignored them. Hope this is a help to someone else as there is presently precious little about this particular error.

root@Ubuntu20.04_server:~# mysql -V
mysql  Ver 8.0.22-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))
wruckie
  • 546
  • 5
  • 18
  • 4
    Looks like the [variables were removed in MySQL 8](https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed) – xofer Nov 12 '20 at 17:28
  • 1
    The accepted answer by wruckie solved the problem for me. Noting: re OP - the default location for the log file is `/var/log/mysql/error.log` rather than `/etc/log/mysql/error.log` – Roger Coathup May 17 '21 at 09:27
  • @RogerCoathup. Thank you, directory fixed – wruckie May 19 '21 at 23:51
  • https://bugs.launchpad.net/ubuntu/+source/mysql-8.0/+bug/1850895 - > Yes, just comment or delete them. – B. Shea Jul 30 '21 at 19:41
  • 1
    fixed my problem to. These mysql variables have been around for probably 10 years, but apparently the upgrade to ubuntu 20.x doesn't want or need them. I commented out the vars in /etc/mysql/my.cnf and restarted the mysql service and all was good. sudo service mysql restart – technology101010 Sep 27 '21 at 20:41
  • 1
    This sorted it for mine too but in my case it was in my.cnf.migraded where i had to remove them. – Gordon Taylor May 23 '22 at 08:28