User already has more than 'max_user_connections

[mikebr]

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' .

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

// 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 :

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

$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.

[mikebr]

To rotate randomly, I'm trying the following:

$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'];

[DrByte]

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.

[mc12345678]

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.

[mikebr]

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:

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

[DrByte]

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.

[njcyx]

To rotate randomly, I'm trying the following:

$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:

$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']);

[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?

[torvista]

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


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.

[njcyx]

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


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.