MySQL Service Status shows down while the service is running

[jndawson]

One of our customer's cpanel boxes is running v.104.0.3 on Centos 7.9, system PHP is 7.4. They elected to upgrade MySQL to MariaDB 10.3 a couple of months ago. No issues until today when we started getting email alerts about MySQL being down.

Logged into WHM, restarted mysql:

Cpanel::Exception::Services::StartError Service Status
undefined status from Cpanel::ServiceManager::Services::Mysql

Service Error
(XID sw5u44) The “mysql” service failed to start.

Startup Log
Jun 06 14:35:23 cp5.domain.tld systemd[1]: Starting MariaDB 10.3.35 database server...
Jun 06 14:35:24 cp5.domain.tld mysqld[18437]: 2022-06-06 14:35:24 0 [Note] /usr/sbin/mysqld (mysqld 10.3.35-MariaDB) starting as process 18437 ...
Jun 06 14:35:57 cp5.domain.tld systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Jun 06 14:35:57 cp5.domain.tld systemd[1]: Failed to start MariaDB 10.3.35 database server.
Jun 06 14:35:57 cp5.domain.tld systemd[1]: Unit mariadb.service entered failed state.
Jun 06 14:35:57 cp5.domain.tld systemd[1]: mariadb.service failed.

mysql has failed. Contact your system administrator if the service does not automagically recover.

Checked processes at cl:

[ [email protected] ~># ps axuww |grep mysql
mysql      509  0.0  0.0 113416  1304 ?        Ss   13:31   0:00 /bin/sh /usr/bin/mysqld_safe
mysql      690  0.1  3.1 1773552 121516 ?      Sl   13:31   0:04 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/cp5.domain.tld.err --open-files-limit=40000 --pid-file=cp5.domain.tld.pid
root     18711  0.0  0.0 112812   980 pts/0    S+   14:36   0:00 grep --color=auto mysql

Just for grins, decided to restart using cli and see what happens:

[ [email protected] ~># /usr/bin/systemctl restart mariadb.service --no-ask-password
Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.
[ [email protected] ~># ps axuww |grep mysql
mysql      509  0.0  0.0 113416  1268 ?        Ss   13:31   0:00 /bin/sh /usr/bin/mysqld_safe
mysql      690  0.1  2.5 1773552 98028 ?       Sl   13:31   0:04 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/cp5.domain.tld.err --open-files-limit=40000 --pid-file=cp5.domain.tld.pid
root     21502  0.0  0.0 112812   980 pts/0    S+   14:50   0:00 grep --color=auto mysql
[ Mon Jun 06 14:50:55 ]

And

[ [email protected] scripts># ./restartsrv_mysql
Waiting for “mysql” to start ……info [restartsrv_mysql] systemd failed to start the service “mariadb” (The “/usr/bin/systemctl restart mariadb.service --no-ask-password” command (process 23151) reported error number 1 when it ended.): Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.

…info [restartsrv_mysql] systemd failed to start the service “mariadb” (The “/usr/bin/systemctl restart mariadb.service --no-ask-password” command (process 23391) reported error number 1 when it ended.): Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.

…info [restartsrv_mysql] systemd failed to start the service “mariadb” (The “/usr/bin/systemctl restart mariadb.service --no-ask-password” command (process 23729) reported error number 1 when it ended.): Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.

…failed.

Cpanel::Exception::Services::StartError
Service Status
        undefined status from Cpanel::ServiceManager::Services::Mysql

Service Error
        (XID 9u3n8c) The “mysql” service failed to start.

Startup Log
        Jun 06 15:00:41 cp5.domain.tld systemd[1]: Starting MariaDB 10.3.35 database server...
        Jun 06 15:00:41 cp5.domain.tld mysqld[23764]: 2022-06-06 15:00:41 0 [Note] /usr/sbin/mysqld (mysqld 10.3.35-MariaDB) starting as process 23764 ...
        Jun 06 15:01:14 cp5.domain.tld systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
        Jun 06 15:01:14 cp5.domain.tld systemd[1]: Failed to start MariaDB 10.3.35 database server.
        Jun 06 15:01:14 cp5.domain.tld systemd[1]: Unit mariadb.service entered failed state.
        Jun 06 15:01:14 cp5.domain.tld systemd[1]: mariadb.service failed.

mysql has failed. Contact your system administrator if the service does not automagically recover.
[ Mon Jun 06 15:01:14 ]
[ [email protected] scripts># ps axuww |grep mysql
mysql      509  0.0  0.0 113416  1268 ?        Ss   13:31   0:00 /bin/sh /usr/bin/mysqld_safe
mysql      690  0.0  2.5 1773552 98016 ?       Sl   13:31   0:05 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/cp5.domain.tld.err --open-files-limit=40000 --pid-file=cp5.domain.tld.pid
root     24161  0.0  0.0 112812   976 pts/0    S+   15:02   0:00 grep --color=auto mysql

So, daemon is running, but cpanel doesn't think so. What do we do to fix this besides turning off mysql monitoring to stop the emails (which we did)?

 

[cPRex]

Hey there! Is it possible that the MySQL server is so busy that the monitoring tools can't properly connect? You can see what MySQL is doing with the "mysqladmin proc status" command. If there are a very large number of queries per second or a large number of open tables, it's possible the monitoring tool is having issues detecting MySQL running properly.

 

[jndawson]

Hey there! Is it possible that the MySQL server is so busy that the monitoring tools can't properly connect? You can see what MySQL is doing with the "mysqladmin proc status" command. If there are a very large number of queries per second or a large number of open tables, it's possible the monitoring tool is having issues detecting MySQL running properly.

The entire system sees very low activity:

[ [email protected] scripts># mysqladmin proc status
+------+-------------+-----------+----+---------+------+--------------------------+------------------+----------+
| Id   | User        | Host      | db | Command | Time | State                    | Info             | Progress |
+------+-------------+-----------+----+---------+------+--------------------------+------------------+----------+
| 2    | system user |           |    | Daemon  |      | InnoDB purge worker      |                  | 0.000    |
| 1    | system user |           |    | Daemon  |      | InnoDB purge coordinator |                  | 0.000    |
| 3    | system user |           |    | Daemon  |      | InnoDB purge worker      |                  | 0.000    |
| 4    | system user |           |    | Daemon  |      | InnoDB purge worker      |                  | 0.000    |
| 5    | system user |           |    | Daemon  |      | InnoDB shutdown handler  |                  | 0.000    |
| 2170 | root        | localhost |    | Query   | 0    | Init                     | show processlist | 0.000    |
+------+-------------+-----------+----+---------+------+--------------------------+------------------+----------+
Uptime: 74494  Threads: 8  Questions: 10038  Slow queries: 0  Opens: 506  Flush tables: 1  Open tables: 499  Queries per second avg: 0.134

 

[cPRex]

Thanks for that - is there anything in the MySQL error log that would provide more details?

 

[jndawson]

Thanks for that - is there anything in the MySQL error log that would provide more details?

Does the mysql error log indicate when cpanel monitoring is screwed up?

To clarify, mysql is running just fine. cpanel service status monitor indicates it's not running at all. We want to know why and fix it so the status monitor is no longer inaccurate.

 

[cPRex]

Sure, it's possible there could be something in that log file that provides a clue. I'm a big fan of the phrase "never come up with reasons not to test something" and it wouldn't hurt to check the log just to see.

You could also check the cPanel error log at /usr/local/cpanel/logs/error_log to see if anything shows up when the monitoring issue happens.

If there's nothing in either of those locations, it would be best to create a ticket with our team so we can take a look at the server.

 

[jndawson]

Recap and update:
Running MariaDB 10.3.35.
Service Status suddenly started sending email notices every 5 minutes that MySQL/MariaDB was down, showing several mysql.tables corrupted due to Innodb errors. Restarting via whm didn't work according to displayed results. Restarting at cli didn't work according to displayed results. ps shows MySQL/MariaDB is actually running. Turned off service monitor.

Checked the affected dB tables, which were mysql.tables, and discovered innodb errors. Ran repair on all tables - still corrupted. Checked whm > sql services > edit sql conf and discovered temp and max heap memory table sizes were 1/2 recommended amount. Fixed according to instructions on the page. Restarted MariaDB with no errors, restarted MariaDB service monitor. No more errors.

Why it suddenly happened is unknown. Perhaps an upgrade changed things.