Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

MySQL "Aborted connection ... (Got an error reading communication packets)": which client, which timeout

Technical article
Troubleshooting

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

A line such as Aborted connection 854 to db: 'shop' user: 'shopapp' host: '192.0.2.31' (Got an error reading communication packets) in the MySQL error log means a client that had connected successfully was then disconnected improperly or was terminated. The server counts it in Aborted_clients and logs the line. The manual's list of causes is mostly client and network behaviour. Match the entries to one client, then check the timeouts, the packet size and the network path.

What the message means

The server error reference gives this log message format, one of several variants: Aborted connection %u to db: '%s' user: '%s' host: '%s' (%s). The text in brackets is the reason. It also lists these protocol messages as separate errors: 1158 Got an error reading communication packets, 1159 Got timeout reading communication packets and 1160 Got an error writing communication packets.

The manual's page on communication errors separates two counters:

  • Aborted_connects counts clients that could not connect at all: no privileges for the database, an incorrect password, a connection packet without the right information, or more than connect_timeout seconds to obtain a connect packet.
  • Aborted_clients counts clients that connected and later "disconnects improperly or is terminated". The server then "logs an Aborted connection message to the error log".

The manual lists three causes for the second case:

  1. The client program did not call mysql_close() before exiting.
  2. The client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any request.
  3. The client program ended abruptly in the middle of a data transfer.

It also lists other reasons: max_allowed_packet too small, or queries needing more memory than mysqld has allocated; the Linux Ethernet duplex bug; a thread-library problem that interrupts reads; badly configured TCP/IP; and faulty Ethernet hardware, hubs, switches or cables. The page's own example of such a line is [Note] Aborted connection 854 to db: 'employees' user: 'josh', shown for log_error_verbosity set to 3. The default for that variable is 2, which permits only ERROR and WARNING messages, and the manual does not say at which priority each version writes this line. Do not infer severity from the word in the log.

The packet and timeout variables the manual defines:

  • net_read_timeout: seconds to wait for more data from a connection before aborting the read, default 30.
  • net_write_timeout: seconds to wait for a block to be written before aborting the write, default 60.
  • wait_timeout and interactive_timeout: seconds the server waits for activity on a noninteractive or interactive connection before closing it, default 28800 seconds, which is 8 × 3600 (8 hours).
  • max_allowed_packet: default 67108864 bytes (64MB).

Likely causes

Cause What you see How to confirm
Application exits or is killed without closing connections Entries cluster at deploys, restarts or crashes of one client Timestamps of entries against the client's own log
Idle connection longer than wait_timeout or interactive_timeout Entries from clients that hold connections open, such as pools; the next use may fail A Sleep session whose Time approaches the timeout in SHOW PROCESSLIST
Client ended abruptly in the middle of a transfer Entries during large reads or loads Entries line up with large queries or loads from one host
max_allowed_packet too small, or a query needing more memory than allocated Entries with large writes or long strings Client errors on the same statement; the variable's value
Network path or hardware problem Entries from many clients, or after a fixed quiet time Pattern across hosts; the failure follows the path, not the application
Failed connection attempts Aborted_connects rises, not Aborted_clients Compare the two counters

Prove the cause before changing anything

All of these are read-only. SHOW STATUS and SHOW VARIABLES need no privilege beyond connecting.

1. Which counter is rising?

SHOW GLOBAL STATUS LIKE 'Aborted%';

Run it twice a few minutes apart. The counters accumulate from server start, so only the change between runs matters. If Aborted_connects rises, the clients cannot connect, and the topic is credentials; see the title "MySQL ERROR 1129: Host 'host_name' is blocked because of many connection errors" if one host fails repeatedly. If Aborted_clients rises, continue.

2. Where is the log, and who is in it?

SHOW GLOBAL VARIABLES LIKE 'log_error';

The manual says the value is stderr when the destination is the console, otherwise the log file's name. Read the lines and note the user, host and db fields and the times. One host or user in most entries points at one client.

3. What are the timeouts?

SHOW GLOBAL VARIABLES LIKE '%timeout%';

Note wait_timeout, interactive_timeout, net_read_timeout, net_write_timeout and connect_timeout.

4. How long have sessions been idle?

SHOW PROCESSLIST;

A session with Command Sleep is idle, and Time is the seconds it has been in its current state. With the PROCESS privilege you see all threads; without it you see only your own. A pool whose idle sessions sit near wait_timeout seconds matches cause 2.

5. Packet size.

SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet';

Compare it with the largest statement or value the application sends. The client-side error for the same causes is covered under the title "MySQL server has gone away (error 2006): wait_timeout, max_allowed_packet or a crash".

Fix

Fix the client first

Causes 1 and 3 are client behaviour. The program should close its connection before exit, and a deploy or restart should let running work finish. Because a client that sleeps longer than wait_timeout or interactive_timeout is one of the manual's causes, a pool's maximum connection lifetime should be shorter than the server's wait_timeout, so the pool retires a connection before the server does. This is general practice, not a statement from the manual.

First note the application's current pool settings (idle timeout, maximum lifetime, validation interval) from its configuration. Copy the current values into a text file or your change record before you edit anything.

Risk: the next change alters how an application opens and recycles its database connections. Too short a lifetime causes more reconnects and load on the server. Test on a staging copy before production.

Set the pool's maximum lifetime to a value below wait_timeout; with the default of 28800 seconds, 25200 seconds (7 hours) is below it. The setting's name depends on the application's library.

To undo it: put the noted settings back and restart the application as its documentation describes.

Raise wait_timeout, only if the client cannot be fixed

The manual says that on thread startup the session wait_timeout value is initialised "from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client". Raise the one your clients use. A client that connects as interactive takes interactive_timeout, so raising only wait_timeout does nothing for it. The change affects connections made afterwards. Setting a global variable needs the SYSTEM_VARIABLES_ADMIN privilege or the deprecated SUPER, according to the System Variable Privileges page, so the statement can be refused for an ordinary account.

First print and keep both current values:

SHOW GLOBAL VARIABLES WHERE Variable_name IN ('wait_timeout', 'interactive_timeout');

Risk: the next statement lets every new idle connection stay open longer, and each one keeps a server connection in use. A client that is leaking connections will leak them for longer.

Run only the line for the kind of client you have. If your clients connect non-interactively, run:

SET GLOBAL wait_timeout = 43200;

If your clients connect as interactive clients, run instead:

SET GLOBAL interactive_timeout = 43200;

43200 seconds is 12 × 3600. The Persisted System Variables page says SET GLOBAL affects "only the currently running server instance" and does not carry over to later starts.

To undo it: for each variable you changed, if the saved value was 28800, run its line below, then print both variables again.

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

Use your own saved numbers if they differed. The SET page says a global change does not affect the session value for current client sessions, so existing sessions keep the value they started with.

Network path

If entries come from many clients or after a fixed quiet time, look at firewalls, load balancers and cabling. The manual names faulty Ethernet hardware as something that "can be diagnosed properly only by replacing hardware". The idle-timer case is covered under the title "Connections drop after a few idle minutes through a firewall or load balancer: idle timers and TCP keepalive".

Example (illustrative): On db01, the error log shows about 40 lines like Aborted connection 854 to db: 'shop' user: 'shopapp' host: '192.0.2.31' (Got an error reading communication packets) between 02:10 and 02:11. SHOW GLOBAL STATUS LIKE 'Aborted%'; before and after shows Aborted_clients up by 40 and Aborted_connects unchanged. The host 192.0.2.31 is web01, whose nightly deploy restarts the application at 02:10. The restart ended the application's open connections without closing them. The team changes the deploy to stop the application gracefully, and the next night's counter change is 0.

Confirm the fix

  1. Run SHOW GLOBAL STATUS LIKE 'Aborted%'; before and after a period that used to produce entries. Aborted_clients should stop rising.
  2. The error log has no new Aborted connection lines for the client.
  3. If you raised wait_timeout or interactive_timeout, new sessions show the new value in SHOW VARIABLES, and the application's own errors have stopped.

What to watch afterwards

  • Record Aborted_clients and Aborted_connects from time to time and graph the difference over a day, not the raw total.
  • After each application release, check whether entries appear at the release time.
  • Keep application connection lifetimes below the shortest idle timer between the client and the server, whether on the server, a firewall or a load balancer.
  • A single stray line after a laptop sleep or a network blip is normal. A steady rise is not.

Risks and limits

  • These commands have not been run or lab-tested.
  • The manual lists the causes of Aborted_clients but does not say which one produces each bracketed reason. Use the pattern in your log, not the wording alone, to choose the cause.
  • The manual's list of other reasons includes hardware and driver problems that cannot be tested remotely.
  • Raising a timeout hides a client problem; it does not fix it.

Sources