CODEWITHSUNDEEP
mysqlconfiguration
skwokie
skwokie

Reputation: 475

Regarding MySQL Aborted connection

I'm looking into aborted connection -

2022-11-21T20:10:43.215738Z 640870 [Note] Aborted connection 640870 to db: '' user: '' host: '10.0.0.**' (Got timeout reading communication packets)

My understanding is that I need to figure out whether it is an interactive or not connection, and increase wait_timeout (or interactive_timeout) accordingly. If it has no effect, then I'll need to adjust net_read_timeout or net_write_timeout and see. I'd like to ask:

  1. Is there a meta table that I can query for the connection type (interactive or not)?
  2. There are how-to's on the internet on adjusting wait_timeout (or interactive_timeout) and all of them have rebooting the database as the last step. Is that really required? Given that immediate effect is not required, the sessions are supposed to come and go, and new sessions will pick up the new value (after the system value is set), I suppose if there is a way to track how many connections are left with the old values, then it will be ok?
  3. Finally, can someone suggest any blog (strategy) on handling aborted connection or adjusting the timeout values?

Thank you!

RDS MySQL version 5.7

Upvotes: 0

Views: 1607

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562300

There is only one client that sets the interactive flag by default: the mysql command-line client. All other client tools and connectors do not set this flag by default. You can choose to set the interactive flag, because it's a flag in the MySQL client API mysql_real_connect(). So you would know if you did it. In some connectors, you aren't calling the MySQL client API directly, and it isn't even an option to set this flag.

So for practical purposes, you can ignore the difference between wait_timeout and interactive_timeout, unless you're trying to tune the timeout of the mysql client in a shell window.

You should never need to restart the MySQL Server. The timeout means the client closed the session after there has been no activity for wait_timeout seconds. The default value is 28800, which is 8 hours.

The proper way of handling this in application code is to catch exceptions, reconnect if necessary, and then retry whatever query was interrupted.

Some connectors have an auto-reconnect option. Auto-reconnect does not automatically retry the query.

In many applications, you are borrowing a connection from a connection pool, and the connection pool manager is supposed to test the connection before returning it to the caller. For example running SELECT 1; is a common test. The action of testing the connection causes a reconnect if the connection was not used for 8 hours.

If you don't use a connection pool (for example if your client program is PHP, which doesn't support connection pools as far as I know), then your client opens a new connection on request, so naturally it can't be idle for 8 hours if it's a new connection. Then the connection is closed as the request finishes, and presumably this request lasts less than 8 hours.

So this comes up only if your client opens a long-lived MySQL connection that is inactive for periods of 8 hours or more. In such cases, it's your responsibility to test the connection and reopen it if necessary before running a query.

Upvotes: 1

Related Questions