MySQL server not starting. Server-side SQL bad statement? Corrupted table? Cpanel autoupdate? – PHP Versions

Yesterday the mysql service stopped working from my dedicated server, taking down several webpages and other applications that run on it. Maybe a cpanel autoupdate didn’t finished correctly but I’ve only recieved warnings emails with this error:

(XID ######) The “mysql” service is down.

and the first one says:

The subprocess “/usr/local/cpanel/scripts/restartsrv_mysql” reported error number 2 when it ended.

System and server info:

• OS: CentOS Linux 8.3
• Apache Version: 2.4.46
• MySQL Version: Ver 8.0.25 for Linux on x86_64 (MySQL Community Server – GPL)
• PHP Version: 7.4.18
• Cpanel v94.0.8

I’m going to list some commands I’ve tried and their output:

# systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xe" for details.

Then:

# systemctl status mysqld.service
 ESCOD
 mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mysqld.service.d
           └─limits.conf
   Active: failed (Result: exit-code) since Tue 2021-05-11 17:33:19 UTC; 53s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 27686 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)
  Process: 27657 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 27686 (code=exited, status=1/FAILURE)
   Status: "Server upgrade complete"
    Error: 2 (No such file or directory)

May 11 17:33:17 xx-xx-xx-xx.cprapid.com mysqld[27686]: 2021-05-11T17:33:17.655235Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock>
May 11 17:33:17 xx-xx-xx-xx.cprapid.com mysqld[27686]: 2021-05-11T17:33:17.670697Z 4 [System] [MY-013381] [Server] Server upgrade frrom '80024' to '80025' started.>
May 11 17:33:18 xx-xx-xx-xx.cprapid.com mysqld[27686]: 2021-05-11T17:33:18.064279Z 4 [ERROR] [MY-010725] [Server] Couldn't repair table: mysql.general_log>
May 11 17:33:18 xx-xx-xx-xx.cprapid.com mysqld[27686]: 2021-05-11T17:33:18.064383Z 4 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement '-- Create general_log CREATE TABLE IF NOT EXISTS general_log (event_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), user_host MEDIUMTEXT NOT NULL, thread_id BIGINT UNSIGNED NOT NULL, server_id INTEGER UNSIGNED NOT NULL, command_type VARCHAR(64) NOT NULL, argument MEDIUMBLOB NOT NULL) engine=CSV CHARACTER SET utf8 comment="General log"; 'failed with error code = 1034, error message = 'Incorrect key file for table 'general_log'; try to repair it'.>>
May 11 17:33:18 xx-xx-xx-xx.cprapid.com mysqld[27686]: 2021-05-11T17:33:18.065026Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade >
May 11 17:33:18 xx-xx-xx-xx.cprapid.com mysqld[27686]: 2021-05-11T17:33:18.065176Z 0 [ERROR] [MY-010119] [Server] Aborting
May 11 17:33:18 xx-xx-xx-xx.cprapid.com mysqld[27686]: 2021-05-11T17:33:18.987996Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.25)  MySQL Community Server - GPL.>
May 11 17:33:19 xx-xx-xx-xx.cprapid.com systemd[1]: mysqld.service: Main process exited, code=exited, status=1/FAILURE
May 11 17:33:19 xx-xx-xx-xx.cprapid.com systemd[1]: mysqld.service: Failed with result 'exit-code'.
May 11 17:33:19 xx-xx-xx-xx.cprapid.com systemd[1]: Failed to start MySQL Server.

# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

This file doesn’t exist but there’s one located in /tmp/mysql.sock. Later, I changed some lines in my.cnf but got the same error

#mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

# nano /etc/my.cnf

[client]

socket=/tmp/mysql.sock

[mysqld]
disable-log-bin=1
default-authentication-plugin=mysql_native_password
innodb_file_per_table=1
performance-schema=0
socket=/tmp/mysql.sock

As said before, I changed socket=/var/lib/mysql/mysql.sock to socket=/tmp/mysql.sock for both client and mysqld. No more changes done.

# mysqld -u root
2021-05-11T17:11:04.993623Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.25) starting as process 25020
2021-05-11T17:11:04.997863Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-05-11T17:11:05.146263Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-05-11T17:11:05.432633Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: /var/run/mysqld/mysqlx.sock
2021-05-11T17:11:05.447752Z 4 [System] [MY-013381] [Server] Server upgrade from '80024' to '80025' started.
2021-05-11T17:11:10.666062Z 4 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement 'ALTER TABLE user   MODIFY max_updates int unsigned DEFAULT 0 NOT NULL,   MODIFY max_connections int unsigned DEFAULT 0 NOT NULL,   MODIFY max_user_connections int unsigned DEFAULT 0  NOT NULL,   MODIFY ssl_cipher BLOB NOT NULL,   MODIFY x509_issuer BLOB NOT NULL,   MODIFY x509_subject BLOB NOT NULL; ' failed with error code = 1138, error message = 'Invalid use of NULL value'.
2021-05-11T17:11:10.666934Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade server.
2021-05-11T17:11:10.667061Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-05-11T17:11:12.059278Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.25)  MySQL Community Server - GPL.

Here I find interesting 'ALTER TABLE user MODIFY... It looks like to have an additional space (sorry if this is unimportant, I’m not an expert in this matter).

# ls -la /var/lib |grep mysql
drwxr-x--x  10 mysql          mysql          4096 May 12 00:24 mysql
drwxr-x---   2 mysql          mysql             6 Apr 23 16:18 mysql-files
drwxr-x---   2 mysql          mysql             6 Apr 23 16:18 mysql-keyring

Lastly, after detecting the incident, the server didn’t respond to pings for an hour or so and needed to be unplug and restarted. Now I can access to the server through ssh but mysql still not working.