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.
Nature of AWS RDS parameter group max_allowed_packet
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.
Methods to fix AWS RDS parameter group max_allowed_packet ?
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.
1. Create a DB parameter group
We can use the AWS Management Console to create a new DB parameter group:
- Initially, we sign in to the AWS Management Console and open the Amazon RDS console.
- In the navigation pane, we choose Parameter Groups.
- Then we select Create parameter group.
- In the Parameter group family list, we select a DB parameter group family.
- Then in the Type list, we select DB Parameter Group.
- We enter the name of the new DB parameter group in the Group name box.
- Also, in the Description box, we enter a description for the new DB parameter group.
- Then we select, Create.
- Once done, we edit it to increase the "max_allowed_packet" to the value it requires.
- Finally, we click save changes.
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:
- Firstly, we sign in to the AWS Management Console and open the Amazon RDS console.
- In the navigation pane, we select Databases, then we choose the DB instance to modify.
- When we select Modify, the Modify DB Instance page appears.
- Here, we change the DB parameter group setting.
- Then we select, Continue and check the summary of modifications.
- On the confirmation page, we can review the changes.
- Once done, we choose Modify DB instance to save the changes.
- In case it requires any changes, we select Back to edit or Cancel to cancel the changes.
2. Modify the parameter
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:
- We sign in to the AWS Management Console and open the Amazon RDS console.
- In the navigation pane, we choose Parameter Groups.
- In the list, we select the parameter group to modify.
- Then for Parameter group actions, we select Edit.
- Change the value of "max_allowed_packet" as per the requirement.
- Once done, we click, Save changes.
[Need help to create or modify AWS RDS parameter? We'd be happy to assist. ]
Conclusion
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)
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)
