Resolved ERROR: Plesk\Exception\Database: DB query failed: SQLSTATE[HY000] [2002] Connection refused (Mysql.php:79)

[Tim_Wakeling]

I'm getting this error intermittently on my Plesk panel, and also website users are reporting that the database fails to connect every now and then when viewing web pages hosted on the machine. Refreshing the page in all cases solves it, but a few minutes later it happens again. It seems to affect all users on the server.

Backups are also failing with the same error.

The problems all started yesterday, and at the same moment my MySQL memory usage suddenly dropped from a stable 700 MB or so down to a rocky 200 MB:



I'm running the latest Plesk 18.0.39 and MariaDB 10.5.13 (which updated last night, and the problem remained).

I can log in fine to MariaDB on the command line, and when it's not going belly up, 99% of DB requests work normally.

Error 500s are appearing in the logs at the moments when the DB fails, but with no further detail recorded. Normally a 500 in the logs is accompanied by an obvious PHP error, but not this time.

Any thoughts anyone? Thanks in advance!

 

[Bitpalast]

What are the MySQL related entries in /var/log/messages?
# grep mysql /var/log/messages

 

[Tim_Wakeling]

Ah interesting!

Lots of repetitions of:
Nov 10 15:09:33 hosting mariadbd: 2021-11-10 15:09:33 0 [Note] /usr/sbin/mariadbd (mysqld 10.5.13-MariaDB) starting as process 11039 ...
Nov 10 15:09:33 hosting mariadbd: 2021-11-10 15:09:33 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
Nov 10 15:09:33 hosting mariadbd: Version: '10.5.13-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
Nov 10 15:18:14 hosting mariadbd: 211110 15:18:14 [ERROR] mysqld got signal 6 ;
Nov 10 15:18:14 hosting mariadbd: It is possible that mysqld could use up to
Nov 10 15:18:14 hosting mariadbd: where mysqld died. If you see no messages after this, something went
Nov 10 15:18:14 hosting mariadbd: ??:0(mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool, select_result*))[0x55f29c58c4ee]
Nov 10 15:18:14 hosting mariadbd: ??:0(mysql_execute_command(THD*))[0x55f29c5c275e]
Nov 10 15:18:14 hosting mariadbd: ??:0(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55f29c5c4d15]
Nov 10 15:18:14 hosting mariadbd: The manual page at How to Produce a Full Stack Trace for mysqld contains
Nov 10 15:18:14 hosting mariadbd: Working directory at /var/lib/mysql

 

[Bitpalast]

Awfully difficult to determine the cause from the log excerpt. On-server analysis will probably be required to determine the underlying cause. A first guess is that either the server resources (like RAM or disk space) are exhausted at some point or that a website is running a high number of queries that it exceeds the server's capability.

You can try this for starters:
Add low limits to /etc/my.cnf

[mysqld]
max_connections=100
max_user_connections=10

This prevents too many simultaneous connections in total and by single users.
Then restart the database server. Login to the database console, e.g.
# plesk db
and watch the processlist, e.g.
> show processlist;
frequently to see if any user exceeds the limit.

Alternatively you can try to run
# /usr/sbin/plesk db "SELECT User AS user, count(User) AS count FROM information_schema.processlist GROUP BY User;"
or better run this as a watch command like
# watch "/usr/sbin/plesk db \"SELECT User AS user, count(User) AS count FROM information_schema.processlist GROUP BY User;\""
to get automatic updates on what's run in the database every 2 seconds.

When the situation comes up that the database crashes, you will probably see a high number of connections or you will see some users who are constantly running queries (a high number of queries and repeated entries in the output of the watch command). If so, you know that a website of that user is abusing the database.

Please report back here if this delivers more insight into the issue.

 

[Tim_Wakeling]

Thanks!

Interestingly, the SHOW PROCESSLIST; command sometimes does what you expect, and other times gives an error:

MariaDB [psa]> SHOW PROCESSLIST;
+----+-----------------+-----------+----------------+---------+------+----------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-----------------+-----------+----------------+---------+------+----------+------------------+----------+
| 4 | admin | localhost | psa | Query | 0 | starting | SHOW PROCESSLIST | 0.000 |
| 19 | website_dbu | localhost | website_db | Sleep | 0 | | NULL | 0.000 |
+----+-----------------+-----------+----------------+---------+------+----------+------------------+----------+
2 rows in set (0.000 sec)

MariaDB [psa]> SHOW PROCESSLIST;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
ERROR: Can't connect to the server

 

[Bitpalast]

You can try this:

Add
innodb_force_recovery = 1
to the [mysqld] section of /etc/my.cnf. Do not use higher values than 1.

Restart the database server. Examine /var/log/messages for start-up messages, because normally corrupt tables will be mentioned in the start-up log sequence.

After it has started, run a full check and repair against it, e.g.
# /usr/bin/mysqlcheck --all-databases --auto-repair
Chances are that at least one table is corrupt. I've experienced a similar crash-and-restart situation before which was linked to single corrupt tables. Whenever certain queries are run against such tables, the server crashes. It can be a similar situation on your system.

 

[Tim_Wakeling]

That's really helpful. I've tried this and can't see any tables mentioned in the startup sequence:

Nov 10 16:17:02 hosting systemd: Starting MariaDB 10.5.13 database server...
Nov 10 16:17:02 hosting mariadbd: 2021-11-10 16:17:02 0 [Note] /usr/sbin/mariadbd (mysqld 10.5.13-MariaDB) starting as process 21152 ...
Nov 10 16:17:02 hosting mariadbd: 2021-11-10 16:17:02 0 [Note] InnoDB: !!! innodb_force_recovery is set to 1 !!!
Nov 10 16:17:02 hosting mariadbd: 2021-11-10 16:17:02 0 [Note] InnoDB: Uses event mutexes
Nov 10 16:17:02 hosting mariadbd: 2021-11-10 16:17:02 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
Nov 10 16:17:02 hosting mariadbd: 2021-11-10 16:17:02 0 [Note] InnoDB: Number of pools: 1
Nov 10 16:17:02 hosting mariadbd: 2021-11-10 16:17:02 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
Nov 10 16:17:02 hosting mariadbd: 2021-11-10 16:17:02 0 [Note] InnoDB: Using Linux native AIO
Nov 10 16:17:02 hosting mariadbd: 2021-11-10 16:17:02 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
Nov 10 16:17:02 hosting mariadbd: 2021-11-10 16:17:02 0 [Note] InnoDB: Completed initialization of buffer pool
Nov 10 16:17:02 hosting mariadbd: 2021-11-10 16:17:02 0 [Note] InnoDB: 128 rollback segments are active.
Nov 10 16:17:02 hosting mariadbd: 2021-11-10 16:17:02 0 [Note] InnoDB: Creating shared tablespace for temporary tables
Nov 10 16:17:02 hosting mariadbd: 2021-11-10 16:17:02 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
Nov 10 16:17:02 hosting mariadbd: 2021-11-10 16:17:02 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
Nov 10 16:17:02 hosting mariadbd: 2021-11-10 16:17:02 0 [Note] InnoDB: 10.5.13 started; log sequence number 211311333828; transaction id 112729955
Nov 10 16:17:02 hosting mariadbd: 2021-11-10 16:17:02 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
Nov 10 16:17:02 hosting mariadbd: 2021-11-10 16:17:02 0 [Note] Plugin 'FEEDBACK' is disabled.
Nov 10 16:17:02 hosting mariadbd: 2021-11-10 16:17:02 0 [Note] Server socket created on IP: '127.0.0.1'.
Nov 10 16:17:02 hosting mariadbd: 2021-11-10 16:17:02 0 [Note] Reading of all Master_info entries succeeded
Nov 10 16:17:02 hosting mariadbd: 2021-11-10 16:17:02 0 [Note] Added new Master_info '' to hash table
Nov 10 16:17:02 hosting mariadbd: 2021-11-10 16:17:02 0 [Note] /usr/sbin/mariadbd: ready for connections.
Nov 10 16:17:02 hosting mariadbd: Version: '10.5.13-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
Nov 10 16:17:03 hosting mariadbd: 2021-11-10 16:17:03 0 [Note] InnoDB: Buffer pool(s) load completed at 211110 16:17:03
Nov 10 16:17:03 hosting systemd: Started MariaDB 10.5.13 database server.

I think you're probably right, it's a corrupt table. In the previous log you suggested to me I saw 5 or 6 tables appear again and again as "having crashed" so I used phpMyAdmin to repair each of these. That hasn't solved it, but I feel like I'm on the right lines. It may be that a different table from these is corrupt but the crash caused by the corrupt table interrupts a command on one of these tables and thereby stuffs it up ... so the actual troublemaker might be another one.

The auto repair command you've given me sounds promising except that it gives me this when I run it, even though I'm root:
/usr/bin/mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

Thank you so much again for your ideas. It's a really big deal for me, this.

 

[Bitpalast]

Regarding the mysqlcheck login, this needs the admin login of Plesk. You can try

# cat /etc/psa/.psa.shadow
Note the output and replace the "output_of_previous_cat_command_here" of the following line with it:
# /usr/bin/mysqlcheck --all-databases --auto-repair -u admin -poutput_of_previous_cat_command_here
The complete command will then look somewhat like
# /usr/bin/mysqlcheck --all-databases --auto-repair -u admin -p'$AES-128-CBC$ZmY/EEpy1+TwCNq5kalqSA==$Pd02kf4TTlpXdi/qyeo92w=='

 

[Tim_Wakeling]

Morning - I am pleased to report I worked out what to do, thanks to your guiding me in the right direction.

A corrupt table on one of my sites was causing MariaDB to crash every time it was accessed, as you said, and that was every couple of minutes. It restarted immediately of course, but in the intervening couple of seconds all sorts of other queries on other sites failed.

Looking at /var/log/messages (which is a log file I didn't know about before), I was able to see three sites recurring in the list where tables were "marked as crashed". I suspended those domains and boom - all the other sites were back to normal and MariaDB was no longer crashing. Next I tried reactivating the three domains one by one, and each time running an incremental backup (this always reliably crashed MariaDB so it was a good test). Doing this, I found that only one of those three sites was actually making things crash. So I left that one till morning and will now investigate further on it before I bring it back.

I'm afraid I still couldn't make the mysqlcheck command work even with your latest instructions, but I'm not worried. Now that I know which site is the problem I can manually repair its tables via phpMyAdmin and experiment there. Not a problem.

I couldn't have done this without you Peter. Do you have a tip jar or anything?

 

[mow]

Note the output and replace the "output_of_previous_cat_command_here" of the following line with it:

Or just use it like this:
/usr/bin/mysqlcheck --all-databases --auto-repair -u admin -p`cat /etc/psa/.psa.shadow`
or
/usr/bin/mysqlcheck --all-databases --auto-repair -u admin -p$(cat /etc/psa/.psa.shadow)