AWS MySQL connection frequently times out

Question

I have a MySQL database in AWS (RDS), and I connect to it from command line via this command:

mysql -u _usernme_ -p_mypassword_ -h  _aws_mysql_host_ _dbname_

It connects fine, problem is, if it remains idle for 1-2 minutes, the connection dies, subsequent commands just hang. I have to kill the process and start a new one.

What configuration changes do I need to do, and where, so that it stays alive forever, just like localhost, until I expressly terminate the connection?

Solution 1

You can find out relevant timeout values as follows:

SHOW VARIABLES LIKE '%_timeout';

You might want to check to make sure wait_timeout and interactive_timeout are adequately set. Both are default to 28800 (i.e. 8 hours):

SET GLOBAL wait_timeout = 28800;
SET GLOBAL interactive_timeout = 28800;

Solution 2

This error happens when try to connect to RDS from the local computer and security group only allow to connect from within VPC.

To solve this issue go to your RDS security group and allow the particular IP or anyone can connect with a password and user_name in your Security Rule.

Below can connect anyone

Although it's not good practice just try to connect with RDS and track the issue

go to RDS instance

select the RDS instance and check in the description for the security group

select security group

select inbound rule

edit the security rule

allow your IP to access it or make it publicly accessible

Or if you think this error occurred in RDS you can simply check the logs of RDS

if the above setting seem fine then AWS says that

Connecting from Outside of Amazon EC2 —Firewall Timeout Issue

Example issue:

Your client connection to the database appears to hang or timeout when running long queries, such as a COPY command. In this case, you might observe that the Amazon Redshift console displays that the query has completed, but the client tool itself still appears to be running the query. The results of the query might be missing or incomplete depending on when the connection stopped.

This happens when you connect to Amazon Redshift from a computer other than an Amazon EC2 instance, and idle connections are terminated by an intermediate network component, such as a firewall, after a period of inactivity. This behavior is typical when you log in from a Virtual Private Network (VPN) or your local network.

To avoid these timeouts, we recommend the following changes:

• Increase client system values that deal with TCP/IP timeouts. You should make these changes on the computer you are using to connect to your cluster. The timeout period should be adjusted for your client and network. See To change TCP/IP timeout settings.
• Optionally, set keep-alive behavior at the DSN level. See To change DSN timeout settings.

To make these changing check the link

AWS:connecting-firewall-guidance.html

Comments

[Michael - sqlbot]

Where are you connecting from? EC2 or at your office or home? Such a short timeout suggests a network or firewall issue. When you connect, note your thread-id with SELECT CONNECTION_ID();. Then, when you have a stalled connection like you describe, reconnect to the server and SELECT * FROM information_schema.processlist WHERE id = xxx the ID from the previous connection. The result, if any, should provide useful troubleshooting info for us. Also, are you sure about 1-2 minutes? Could it be 15 minutes?

[Michael - sqlbot]

The connect_timeout will not be the solution if the issue occurs on a working connection. That variable is the timeout for the client to negotiate the MySQL client/server protocol handshake when it first connects. It has no meaning once you're at a point in the connection where you can execute queries. I suspect (and hope) the other two timers are at their defaults... this sounds like a network issue, to me.

[Leo C]

@Michael, you're right by the very definition of connect_timeout, but the note re: Increasing the connect_timeout value might help if clients frequently encounter errors of the form Lost connection to MySQL server at 'XXX', system error: errno (rather than just Unable to establish connection) in the doc seems to suggest that it might not be limited to the initial connection only.

[Michael - sqlbot]

I think you may be confusing that error with a similar one Lost connection to MySQL server during query, which is a different error code. Here, they're talking about errors like Lost connection to MySQL server at 'reading initial communication packet' or ...at 'reading authorization packet', during initial negotiation.

[Leo C]

@Michael, no, I simply interpreted that the unspecified 'XXX' could cover something broader than just initial connection issues. Given the uncertainty, I think I should stick to the definition of connect_timeout (i.e. for initial connection), and remove it from the answer.