Sometimes, we can get a warning when the number of simultaneous connections exceeds the AWS RDS parameter group max_allowed_packet.
Basically with the 'max_allowed_packet' parameter, we can restrict client use of MySQL server resources by setting it to a nonzero value.
Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform related AWS queries.
In this context, we shall look into how to modify this parameter to fix this error.
When the number of simultaneous connections exceeds the AWS RDS parameter group max_allowed_packet, we encounter the error:
"Warning: mysql_connect(): User xxxxxxxx already has more than 'max_user_connections' active connections"
The AWS RDS parameter limits the number of simultaneous connections a specific account can make. The default value is 0, which doesn't limit simultaneous connections for the specified account.
To fix the warning, we need to create a new parameter group and associate it with the DB instance or modify the parameter in the parameter group.
We can use the AWS Management Console to create a new DB parameter group:
How to Associate a DB parameter group with a DB instance ?
When we change the DB parameter group associated with a DB instance, we manually reboot the instance before using it:
We can modify parameter values in a customer-created DB parameter group. However, we can't change values in a default parameter group.
The changes we make applies to all DB instances that associate with the DB parameter group:
This article covers how to create and modify the AWS RDS parameter.
What is max_connect_errors parameter ?
This parameter indicates how many connection errors are possible before the server blocks a host. If more than max_connect_errors successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections. The default value is 100 and can be tuned to your security requirements and environment.
For example, if max_connect_errors=5000, after 5,000 connection requests from Host X are interrupted you get an error like the following:
Host X is blocked because of many connection errors ()
You can simply Unblock the host using the following command:
mysql> flush hosts;
mysql> show global variables like 'max_connect_errors';
+| Variable_name | Value | +--------------------+-------+ | max_connect_errors | 5000| +--------------------+-------+
1 row in set (0.00 sec)