max_user_connections problem preventing users from completing orders

[xarga]

Aimeos 2019:10
Laravel 6.0
PHP 7.3
Centos 7.9

The site has been stable for the last year but recently following a Centos Update, users are constantly running into an error

(3/3) QueryException
SQLSTATE[HY000] [1203] User ****_shopusr already has more than 'max_user_connections' active connections (SQL: select * from `users` where `id` = 1 limit 1)

in Connection.php line 669
at Connection->runQueryCallback('select * from `users` where `id` = ? limit 1', array(1), object(Closure))
in Connection.php line 629

One shop administrator reported the following:

" Logged out, opened new "incognito" window. Logged back in, was able to navigate back end for a couple of minutes, then same error message.

Also opened the store from customer side "incognito" was able to navigate until trying to pay, then error message.

Also, when I was able to view dashboard in the backend. I noticed still no new orders, that's only 2 in three days. Obviously, no one is getting in to complete a transaction.

Sometimes the shop shows the full SQL error and sometimes it just shows "a non-recoverable error occurred "
---------------------------------------

https://shop.thefrenchgourmet.com

Currently MySQL is configured to allow up to 25 connections per user.

Would appreciate any guidance as to why this may have started happening.

[aimeos]

Seems like the connections aren't closed properly any more. Maybe the installed PHP version has an issue

[xarga]

I tried back reving to PHP 7.2 but still seeing the same issues. The database isn't heavily loaded

Server version: 10.3.28-MariaDB MariaDB Server

Code: Select all

MariaDB [(none)]> SHOW PROCESSLIST;
+------+------------------+-----------+----------------+---------+------+--------------------------+------------------+----------+
| Id   | User             | Host      | db             | Command | Time | State                    | Info             | Progress |
+------+------------------+-----------+----------------+---------+------+--------------------------+------------------+----------+
|    2 | system user      |           | NULL           | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|    1 | system user      |           | NULL           | Daemon  | NULL | InnoDB purge coordinator | NULL             |    0.000 |
|    4 | system user      |           | NULL           | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|    3 | system user      |           | NULL           | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|    5 | system user      |           | NULL           | Daemon  | NULL | InnoDB shutdown handler  | NULL             |    0.000 |
|   48 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   27 |                          | NULL             |    0.000 |
|   51 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   27 |                          | NULL             |    0.000 |
|   54 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   27 |                          | NULL             |    0.000 |
|   57 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   26 |                          | NULL             |    0.000 |
|   58 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   26 |                          | NULL             |    0.000 |
|   59 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   26 |                          | NULL             |    0.000 |
|   61 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   25 |                          | NULL             |    0.000 |
|   62 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   25 |                          | NULL             |    0.000 |
|   63 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   25 |                          | NULL             |    0.000 |
|   64 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   25 |                          | NULL             |    0.000 |
|   66 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   25 |                          | NULL             |    0.000 |
|   67 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   24 |                          | NULL             |    0.000 |
|   69 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   24 |                          | NULL             |    0.000 |
|   70 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   24 |                          | NULL             |    0.000 |
|   71 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   24 |                          | NULL             |    0.000 |
|  154 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   27 |                          | NULL             |    0.000 |
|  233 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   29 |                          | NULL             |    0.000 |
|  380 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   28 |                          | NULL             |    0.000 |
|  443 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   27 |                          | NULL             |    0.000 |
|  536 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   29 |                          | NULL             |    0.000 |
|  625 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   29 |                          | NULL             |    0.000 |
|  773 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   29 |                          | NULL             |    0.000 |
|  920 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   29 |                          | NULL             |    0.000 |
| 1078 | fgourmet_shopusr | localhost | fgourmet_shop  | Sleep   |   30 |                          | NULL             |    0.000 |
| 1221 | root             | localhost | NULL           | Query   |    0 | Init                     | SHOW PROCESSLIST |    0.000 |
| 1225 | sceniccy_wp      | localhost | sceniccy_wp    | Sleep   |    5 |                          | NULL             |    0.000 |
| 1228 | architec_usr     | localhost | architec_dbase | Sleep   |    0 |                          | NULL             |    0.000 |
| 1241 | sceniccy_wp      | localhost | sceniccy_wp    | Sleep   |    0 |                          | NULL             |    0.000 |
+------+------------------+-----------+----------------+---------+------+--------------------------+------------------+----------+
33 rows in set (0.001 sec)

[aimeos]

If PHP isn't the issue, check MariaDB and other updated packages.

[xarga]

We just increased the number of allowed connections/user from WHM/CPANEL default of 25 to 50. Its seems there are always around 23-24 sleeping connections from Aimeos so it was frequently hitting the ceiling of 25 each time a new user came onto the site..

[aimeos]

Aimeos doesn't use that much connections at all. It uses max. 1-2 connections per request depending on the task.

Most likely PHP-FPM isn't closing the connections after the request is finished - especially as you've said you updated the OS but not Aimeos. Check if persistant connections are enabled in the PHP configuration now which can cause such issues.