User already has more than 'max_user_connections

ZC-1.5.5e, PHP 5.6.32, Database patch level 1.5.5

I get this log error once daily(User already has more than 'max_user_connections)
I spoke with my Server Host, My max db user connections is set at 50 per User. My understanding is that it is based on a concurrent basis. So there would have to be >= 50 db connections within milliseconds.
I read the previous posts regarding 'Rotating db users' .

*********************************************************

Code:

---

// Enter your 4 database usernames here.
// If you have only two, then repeat them: first, second, first, second
 $dbuser[] = 'username1';
 $db_pwd[] = 'password1';
 
 $dbuser[] = 'username2';
 $db_pwd[] = 'password1';
 
 $dbuser[] = 'username3';
 $db_pwd[] = 'password1';
 
 $dbuser[] = 'username4';
 $db_pwd[] = 'password1';


// NO NEED TO DO ANYTHING IN THIS SECTION:
 // calculate the current "minute"
 $current_minute = date('i');
 // calculate which "quarter of the hour" we're in
 $db_user_active = (int)($current_minute/15);
 define('DB_SERVER_USERNAME', $dbuser[$db_user_active]);
 define('DB_SERVER_PASSWORD', $db_pwd[$db_user_active]);
 //echo $db_user_active;


---

*************************************************************

Based on the above code it seems that this, 50 or greater simultaneous db connections log error, would still occur. since each different db user would still
be the designated user for 15 minutes. if the concurrency occurred at the second the switch to the next user the max could be increased to 100 theoretically.
After doing some searching on this, I came up with the following code :

************************************************************

Code:

---

$dbusers = array(
array('user' => 'mysql_username_1', 'password' => 'mysql_password_1') // First MySQL user/password combination
, array('user' => 'mysql_username_2', 'password' => 'mysql_password_2') // Second MySQL user/password combination
, array('user' => 'mysql_username_3', 'password' => 'mysql_password_3') // Third MySQL user/password combination
);
$mysql_user = $dbusers[rand(0, count($dbusers) - 1)];
$config['MasterServer']['username'] = $mysql_user['user'];
$config['MasterServer']['password'] = $mysql_user['password'];

---

****************************************************************
The No. of db users could be increased beyond 3


Everytime a page is opened, 1 of the defined username/password combinations will be choosen at random, and by this reducing the number of connections for each user.

My questions are:
1) Will this code work?
If it does work this could eliminate those max user connections.

To rotate randomly, I'm trying the following:

Code:

---

$dbusers = array(
array('user' => 'mysql_username_1', 'password' => 'mysql_password_1') // First MySQL user/password combination
, array('user' => 'mysql_username_2', 'password' => 'mysql_password_2') // Second MySQL user/password combination
, array('user' => 'mysql_username_3', 'password' => 'mysql_password_3') // Third MySQL user/password combination
);
$mysql_user = $dbusers[rand(0, count($dbusers) - 1)];
define('DB_SERVER_USERNAME', $mysql_user['user'];
define('DB_SERVER_PASSWORD', $mysql_user['password'];

---

That sort of concept can work.

But the real root issue is that you've outgrown your server's (or the hosting plan's) capabilities.
Now is the time to investigate a server without such limits.

Further, while practicing this will reduce the clashing of multiple user connections (though certainly 50 does seem to be a small number), one should try to look at what is causing that to occur from the onset...

There could be a poorly written script that could be causing the issue of using all of the connections. This could just be helping it run a little longer or by using more server resources.

Thanks for the input.
As far as figuring out what the cause is;

i've been recently inundated with this Romanian bot (100's of sessions daily): IP Address 185.206.81.10Host: 185.206.81.10 every day.

The following are a few sessions from the User Tracker Mod:

Code:

---

Session ID        User Shopping Cart
Guest, hsa8l0af3rficsevqvbu4mqr35DeleteView       
Empty Cart
Click Count:        1       
Start Time:        19:12:41        Idle Time:        00:10:23
End Time:        19:12:41        Total Time:        00:00:00
Country:        Romania Romania
IP Address:        185.206.81.10
Host:        185.206.81.10
Originating URL:        /mysite/toys/tv_movie_and_character_toys?zenid=6i3ouklnofd5ogsbcg7cpgcbn4&pid=04


Session ID        User Shopping Cart
Guest, gad0nrause6vcpe9572nnl0gm0DeleteView       
Empty Cart
Click Count:        1       
Start Time:        19:08:10        Idle Time:        00:14:54
End Time:        19:08:10        Total Time:        00:00:00
Country:        Romania Romania
IP Address:        185.206.81.10
Host:        185.206.81.10
Originating URL:        /mysite/?amp%3Bamp%3Bamp%3Bamp%3Bamp%3Bmanufacturers_id=68&amp%3Bamp%3Bamp%3Bamp%3Bamp%3Bzenid=6i3ouklnofd5ogsbcg7cpgcbn4&review=362


Session ID        User Shopping Cart
Guest, e55qdtfv75t141snvnd9c8kcn7DeleteView       
Empty Cart
Click Count:        1       
Start Time:        18:51:02        Idle Time:        00:32:02
End Time:        18:51:02        Total Time:        00:00:00
Country:        Romania Romania
IP Address:        185.206.81.10
Host:        185.206.81.10
Originating URL:        /mysite/?amp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bmanufacturers_id=68&amp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bzenid=6i3ouklnofd5ogsbcg7cpgcbn4&pid=108


Session ID        User Shopping Cart
Guest, nsifem6betp9r9kopua5si8e52DeleteView       
Empty Cart
Click Count:        1       
Start Time:        18:45:51        Idle Time:        00:37:13
End Time:        18:45:51        Total Time:        00:00:00
Country:        Romania Romania
IP Address:        185.206.81.10
Host:        185.206.81.10
Originating URL:        /mysite/?amp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bmanufacturers_id=68&amp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3Bzenid=6i3ouklnofd5ogsbcg7cpgcbn4&con=74

---

Could this Bot be responsible for the DB hits over 50?
How do you know if this is a Good bot or Bad one?
Could the 'Bad Bot Block' mod help in this instance?

Any help would be appreciated

Even if it's a good bot, if you're not expecting customers from the audience that that bot's search-index-results will normally serve, then it's prudent to block it.

You can google several things:
- lists of good and bad bots
- using robots.txt rules to block (friendly) bots that you wish to not index your site

And you can use a firewall or a plugin to do the blocking.
ie: if the undesired bot is always visiting from a single (or group of) IP address, the most efficient way is to use the server firewall to block it, cuz then it never even hits your site. One can also use some hosting control panels to block undesired IP addresses (in the background it drops those IPs into a .htaccess file).
Using a plugin won't reduce db connections because the hits will actually get to your store and the store will have to do the ignoring after the db lookup.

Many thanks
The Hosting Control Panel 'IP Address Deny Manager'
is a great way denying a range of IP addresses.

Just moved hosts and have discovered the new one has max users set to 25 (old hosting was 0/unlimited).
I assume this is very low indeed...indicating a server squeezed to the max.

Testing locally all is fine for 2 and above so I assume scripts are ok.

I would be interested to know what value others have if they are using shared servers (where there is no user control over this parameter/it's set by the hosting).

This is shown in the ZC Admin->Tools->Server/Version Info, search for "max_user_connections".

Quote:

---

Originally Posted by torvista

I would be interested to know what value others have if they are using shared servers (where there is no user control over this parameter/it's set by the hosting).

This is shown in the ZC Admin->Tools->Server/Version Info, search for "max_user_connections".

---

I've seen 100 on a shared server.

Quote:

---

Originally Posted by torvista

I would be interested to know what value others have if they are using shared servers (where there is no user control over this parameter/it's set by the hosting).

This is shown in the ZC Admin->Tools->Server/Version Info, search for "max_user_connections".

---

Our local test servers default to $nbsp; for zc158 and zc158a or display nothing with zc200.

Production servers running on virtual hosts default to $nbsp; for zc158 and zc158a

If no number is shown, it's because it is 0 which is no limit (https://github.com/zencart/zencart/issues/6228).

I'll note that my hosting company has their shared server option limited to 25.
I was getting errors from this so moved to their VPS option with no limit/my limit. No errors since.

For interest I reduced this to 25 recently. No errors since, so I'm assuming it was some external factors triggering those errors on the shared server.

Quote:

---

Originally Posted by mikebr

To rotate randomly, I'm trying the following:

Code:

---

$dbusers = array(
array('user' => 'mysql_username_1', 'password' => 'mysql_password_1') // First MySQL user/password combination
, array('user' => 'mysql_username_2', 'password' => 'mysql_password_2') // Second MySQL user/password combination
, array('user' => 'mysql_username_3', 'password' => 'mysql_password_3') // Third MySQL user/password combination
);
$mysql_user = $dbusers[rand(0, count($dbusers) - 1)];
define('DB_SERVER_USERNAME', $mysql_user['user'];
define('DB_SERVER_PASSWORD', $mysql_user['password'];

---

---

Hi. I just tried your code with zc1.58a with php 8.1, and it worked great except one typo... Here is the updated code:

Code:

---

$dbusers = array(
array('user' => 'mysql_username_1', 'password' => 'mysql_password_1') // First MySQL user/password combination
, array('user' => 'mysql_username_2', 'password' => 'mysql_password_2') // Second MySQL user/password combination
, array('user' => 'mysql_username_3', 'password' => 'mysql_password_3') // Third MySQL user/password combination
);
$mysql_user = $dbusers[rand(0, count($dbusers) - 1)];
define('DB_SERVER_USERNAME', $mysql_user['user']);
define('DB_SERVER_PASSWORD', $mysql_user['password']);

---

>Hi. I just tried your code with zc1.58a with php 8.1, and it worked great

You mean you've stopped getting the error logs?

So my site was killed over the last couple of days, CPU and memory shot up to 100%
Attachment 20675

I got the server log and examined the hits per minute by counting the rows:

hour	min	rows
10	35	135
10	36	99
10	37	177
10	38	128
10	39	319
10	40	279
10	41	212
10	42	190
10	43	132

Just from this I don't think this is excessive.
Anyway, examining in detail 10.39 I find it to be mainly

facebookexternalhit/1.1 (+http://www.facebook.com/externalhit_uatext.php)

but the hits are at most 3 per second. Again, this does not seem excessive enough to max out the cpu/memory.

max_user_connections is at 30.

I'm using three mysql users, rotating randomly as above: if I used only one, the site page would show the database connection error.

There are always two errors

--> PHP Warning: mysqli_connect(): (HY000/1203): User tienda_user1 already has more than 'max_user_connections' active connections in includes/classes/db/mysql/query_factory.php on line 86.

and

[29-Jun-2024 14:24:37 Europe/Madrid] PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000] [1203] User tienda_user1 already has more than 'max_user_connections' active connections in ...../laravel/vendor/illuminate/database/Connectors/Connector.php:70
Stack trace:

followed by a load of laravel trace.

I assumed the second debug was just the result of the first....

But, my site has never had these problems until the last couple of years.

Is it possible that the addition of Laravel has introduced some performance issue that only shows up on these more restricted servers?

There are multiple threads on this max_user_connections error.

I would note that my site while being heavily customised, is using the GitHub development code.

I don't think I can take this any further apart from upping the server spec, but maybe that is just masking a problem.

I'm open to dev suggestions as to what more can be done to investigate further, or if the above traffic and result is not unreasonable.

Quote:

---

Originally Posted by torvista

>Hi. I just tried your code with zc1.58a with php 8.1, and it worked great

You mean you've stopped getting the error logs?

---

Yes, the mod in this thread definitely helps. But my issue is intermittent anyway. It looks like some hackers tried to attack my site via DDoS sometimes.

Quote:

---

Originally Posted by torvista

So my site was killed over the last couple of days, CPU and memory shot up to 100%
Attachment 20675

I got the server log and examined the hits per minute by counting the rows:

hour	min	rows
10	35	135
10	36	99
10	37	177
10	38	128
10	39	319
10	40	279
10	41	212
10	42	190
10	43	132

Just from this I don't think this is excessive.
Anyway, examining in detail 10.39 I find it to be mainly

facebookexternalhit/1.1 (+http://www.facebook.com/externalhit_uatext.php)

but the hits are at most 3 per second. Again, this does not seem excessive enough to max out the cpu/memory.

max_user_connections is at 30.

I'm using three mysql users, rotating randomly as above: if I used only one, the site page would show the database connection error.

There are always two errors

--> PHP Warning: mysqli_connect(): (HY000/1203): User tienda_user1 already has more than 'max_user_connections' active connections in includes/classes/db/mysql/query_factory.php on line 86.

and

[29-Jun-2024 14:24:37 Europe/Madrid] PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000] [1203] User tienda_user1 already has more than 'max_user_connections' active connections in ...../laravel/vendor/illuminate/database/Connectors/Connector.php:70
Stack trace:

followed by a load of laravel trace.

I assumed the second debug was just the result of the first....

But, my site has never had these problems until the last couple of years.

Is it possible that the addition of Laravel has introduced some performance issue that only shows up on these more restricted servers?

There are multiple threads on this max_user_connections error.

I would note that my site while being heavily customised, is using the GitHub development code.

I don't think I can take this any further apart from upping the server spec, but maybe that is just masking a problem.

I'm open to dev suggestions as to what more can be done to investigate further, or if the above traffic and result is not unreasonable.

---

I got the same issue from facebookexternalhit recently, so I blocked it in htaccess file using RewriteCond %{HTTP_USER_AGENT}. I suspected facebookexternalhit was hacked somehow.

In the end I moved to a server without the low restrictions....end of error logs and site disappearing.

I am getting this on php 8.3 and zencart 2.1. Though only noticed the past month, getting that dreaded 'database crashed' cartoon logo sometimes. Server seems to suggest it is bots, they have blocked a lot. Also they say I have 11 processes running, though not sure if thats a problem. I have a wordpress site connected, though its hardly used now anyway. Always updated though. Thanks in advance.