5

I am running MySQL 5.6.14 on an Ubuntu server and noticed the error log constantly filling with:

Aborted connection 17201 to db: 'somedb' user: 'sdb' host: 'x.x.x.x' (Got timeout reading communication packets)

(These happen about 10 times per minute. The counter is up to 13,000 after a server uptime of only 24 hours.)

We have perhaps twenty or thirty instances of a Windows client program that uses the .NET framework via Connector/NET (6.2.2.0) to connect to the database and I've made sure that every time it creates a connection (MySqlConnection.Open()) it is also properly closed (MySqlConnection.Close()).

I've adjusted various timeouts, max_allowed_packet, and other settings based on research on the web, but these still seem to persist.

Here are (what I believe are) the relevant settings:

connect_timeout = 20
wait_timeout = 60
net_read_timeout = 60
net_write_timeout = 60
max_allowed_packet = 33554432 // 32M

What can I do to stop these events from constantly happening?

6

It turns out that the MySQL Connector/NET behavior is at the heart of the matter.

By default, it will automatically create a pool of connections and reuse them as necessary. When you call .Open() a connection might be created anew, or you might get a connection that already exists but was idle (SLEEP). When you call .Close() the connection is returned to the pool.

There are a lot of other questions and answers which deal with the concept of "Should I create new connections for every action or should I reuse the same connection?" - the Connector/NET deals with that for you, so you should create, open and close them. The pooling is automatic and behind the scenes.

Something of importance here: The maximum number of connections that Connector/NET can create by default is 100. If you've got a lot of clients doing concurrent queries or what have you, it's possible the server could exceed its max_connections limit. If necessary you can limit the number of connections that Connector/NET creates by specifying a Maximum Pool Size option in its connection string options.

So, why all the aborted clients? The connections in the pool managed by Connector/NET are held open for longer than your server's wait_timeout, so the server closes them and considers them a client abort.

How long are they held open? Well, it depends on your Connector/NET version, but:

Starting with MySQL Connector/Net 6.2, there is a background job that runs every three minutes and removes connections from pool that have been idle (unused) for more than three minutes. The pool cleanup frees resources on both client and server side. This is because on the client side every connection uses a socket, and on the server side every connection uses a socket and a thread.

Prior to this change, connections were never removed from the pool, and the pool always contained the peak number of open connections. For example, a web application that peaked at 1000 concurrent database connections would consume 1000 threads and 1000 open sockets at the server, without ever freeing up those resources from the connection pool. Note, connections, no matter how old, will not be closed if the number of connections in the pool is less than or equal to the value set by the Min Pool Size connection string parameter.

-- Reference

Since you're using version 6.2.2.0, they're closed after three minutes by the client, which doesn't cause the server to treat it as a warning.

Solution: Increase wait_timeout to five minutes or so. Note: watch the number of connections to the server to make sure that having connections able to SLEEP longer doesn't cause the total number of connections to approach max_connections.

3
  • This was a tricky issue to solve,you can check output of Show Full Processlist; and observe how many connections(threads) are in SLEEP mode.
  • Wait_timeout :

The number of seconds the server waits for activity on a noninteractive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections also you can have a look at another variable called interactivfe_timeout this value indicates number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect().

You should increase the value of wait_timeout and make it default = 28800 seconds (8 hours) and if a client is connecting using TCP connection specify high value for interactive_timeout while connecting to server as well and give a try, I think this will resolve the issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.