MySQL stuck in an upgrade loop with high CPU usage

[cadwhisker]

SYSTEM INFORMATION
OS type and version:	Ubuntu 20.04 LTS
Webmin version:	1.981
Virtualmin version:	6.17-3
Related products version:	MySQL 80027

---

Hoping someone can help here…
I have a MySQL upgrade that fails and restarts continuously. It’s using 90-100% CPU usage.
I can’t run mysql to query the database because the server is caught mid-upgrade:

root@my_system:~# service mysql status
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: activating (start) since Wed 2021-10-27 09:07:52 UTC; 1min 11s ago
    Process: 14699 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 14707 (mysqld)
     Status: "Server upgrade in progress"
      Tasks: 28 (limit: 2279)
     Memory: 810.8M
     CGroup: /system.slice/mysql.service
             └─14707 /usr/sbin/mysqld

Oct 27 09:07:52 my_system.com systemd[1]: mysql.service: Scheduled restart job, restart counter is at 25.
Oct 27 09:07:52 my_system.com systemd[1]: Stopped MySQL Community Server.
Oct 27 09:07:52 my_system.com systemd[1]: Starting MySQL Community Server...

The log file shows the upgrade loop looking like this:

root@my_system:~# tail /var/log/mysql/error.log
2021-10-27T09:09:45.171014Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-10-27T09:09:47.647552Z 4 [System] [MY-013381] [Server] Server upgrade from '80026' to '80027' started.
2021-10-27T09:10:25.084022Z 4 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement '-- Copyright (c) 2015, 2021, Oracle and/or its affiliates. -- -- This program is free software; you can redistribute it and/or modify -- it under the terms of the GNU General Public License as published by -- the Free Software Foundation; version 2 of the License. -- -- This program is distributed in the hope that it will be useful, -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the -- GNU General Public License for more details. -- -- You should have received a copy of the GNU General Public License -- along with this program; if not, write to the Free Software -- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -- NOTE: This needs to be replicated within the sys_config_clean.inc file INSERT IGNORE INTO sys.sys_config (variable, value) VALUES     ('statement_truncate_len', 64),     ('statement_performance_analyzer.limit', 100),     ('statement_performance_analyzer.view', NULL),     ('diagnostics.allow_i_s_tables', 'OFF'),     ('diagnostics.include_raw', 'OFF'),     ('ps_thread_trx_info.max_length', 65535); ' failed with error code = 1436, error message = 'Thread stack overrun:  12576 bytes used of a 131072 byte stack, and 160000 bytes needed.  Use 'mysqld --thread_stack=#' to specify a bigger stack.'.
2021-10-27T09:10:25.085210Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade server.
2021-10-27T09:10:25.085888Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-10-27T09:10:26.035985Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.27-0ubuntu0.20.04.1)  (Ubuntu).
2021-10-27T09:10:26.476529Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.27-0ubuntu0.20.04.1) starting as process 15965
2021-10-27T09:10:26.486313Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-10-27T09:10:58.986031Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-10-27T09:11:01.778102Z 4 [System] [MY-013381] [Server] Server upgrade from '80026' to '80027' started.

Can someone tell me how I can break out of this loop? I can’t stop or start the mysql service.

I can’t apt update the package.

I’m open to any suggestions for what to do next.

Thanks.

[cadwhisker]

OK, wow; I just found it because that huge error log line tells you what’s wrong! Specifically:

failed with error code = 1436, error message = 'Thread stack overrun: 12576 bytes used of a 131072 byte stack, and 160000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.'

SOLUTION:
I found a config file which set the thread_size and edited it:
root@my_server:# nano -w /etc/mysql/mysql.conf.d/mysqld.cnf

Changed the line that says: thread_stack = 128K to: thread_stack = 256K.

I can now start the service with service mysql start and it doesn’t crash. Running apt upgrade goes through without failing, too.
END SOLUTION:

However, it’s not solved yet; mysqld is still running with >90% CPU usage.

I rebooted and CPU usage stayed high. I can run mysql now with: mysql -u root -p (enter password) and I get to the mysql> prompt.

I’ve got htop open in another window and I query what it’s doing to take the high CPU:
mysql> select * from performance_schema.threads where THREAD_OS_ID = 1881 \G
(where 1881) is the PID of the high CPU usage thread.

It says:

mysql> select * from performance_schema.threads where THREAD_OS_ID = 1881 \G
*************************** 1. row ***************************
          THREAD_ID: 34
               NAME: thread/innodb/srv_purge_thread
               TYPE: BACKGROUND
     PROCESSLIST_ID: NULL
   PROCESSLIST_USER: NULL
   PROCESSLIST_HOST: NULL
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: NULL
   PROCESSLIST_TIME: 111
  PROCESSLIST_STATE: NULL
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: NULL
       THREAD_OS_ID: 1881
     RESOURCE_GROUP: SYS_default
1 row in set (0.00 sec)

So, it’s doing a purge of something. I’ll let it run a bit longer…

… and after 10 mins, the CPU usage has dropped…

[cadwhisker]

Extra debugging notes:
Lots of SSH sessions open with one of these commands in each:

htop
tail -f /var/log/syslog
tail -f /var/log/mysql/error.log

[Taha]

Excellent.

It is a memory issue of MySQL.

In similar cases, I suggest adding extra RAM and also adding a swap file so that you could have some extra buffer.

htop and free -m will help to monitor your RAM.

Also, you may check your Apache or Nginx access logs to see if there are any brute-force attacks that could be causing the issue.

In Amazon AWS EC2
1- Create a swap file
the swap file is 4 GB (128 MB x 32)
$ sudo dd if=/dev/zero of=/swapfile bs=128M count=32

If the OUTPUT: dd: failed to open ‘/swapfile’: Text file busy
$ sudo swapoff --all
$ sudo dd if=/dev/zero of=/swapfile bs=128M count=32
OUTPUT:
sudo: unable to resolve host ip-172-31-89-117: Temporary failure in name resolution
32+0 records in
32+0 records out
4294967296 bytes (4.3 GB, 4.0 GiB) copied, 157.097 s, 27.3 MB/s
$ sudo swapon --all

2- // Update the read and write permissions for the swap file:
$ sudo chmod 600 /swapfile

3- Set up a Linux swap area:
$ sudo mkswap /swapfile

4- Make the swap file available for immediate use by adding the swap file to swap space:
$ sudo swapon /swapfile

5- Verify that the procedure was successful:
$ sudo swapon -s

6- Enable the swap file at boot time by editing the /etc/fstab file.
$ sudo vi /etc/fstab
// Add the following new line at the end of the file, save the file, and then exit:

/swapfile swap swap defaults 0 0