From: Warren Young
Date: April 13 2009 7:50pm
Subject: Re: mysql++'s connection hangs after long timeout
List-Archive: http://lists.mysql.com/plusplus/8517
Message-Id: <49E3976A.7060301@etr-usa.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

Denis Nagorny wrote:
> 
> There are no any requests during weekend 

You've just run into a common issue, the default 8-hour MySQL connection 
timeout.  I've added a section to the user manual to cover this issue. 
Here's what it currently says:



By default, current MySQL servers have an 8 hour idle timeout on 
connections. The most common way to run into this problem is when you 
have a program that runs continually, but you keep finding that it dies 
overnight or over the weekend because no one is making it issue queries. 
The MySQL server closes the connection after the configured idle timeout 
to free up resources, because a networked program can never be sure that 
the remote peer is still there when no data is being transferred.

You cannot detect this by calling Connection::connected(). If that 
returns true, it just means that either the connect-on-create 
constructor or the connect() call succeeded and that we haven’t observed 
the connection to be down since then. If the server closes the 
connection, you won’t know it until the next time you try to talk to it. 
This is simply due to the nature of network programming.

One way around this problem is to configure MySQL to use a different 
timeout. This timeout is in seconds, so the default of 8 hours is 28,800 
seconds. You could figure out the longest possible time that your 
program could be left idle. For instance, it could be left running over 
a long 4-day weekend, which is 345,600 seconds, which you could round up 
to 350,000 or 400,000 just to keep things simple.

You can also change this parameter on a per-connection basis, too, by 
using MySQL++’s ConnectTimeoutOption connection option.

Another way to tackle this, if your program is asynchronous (i.e. 
doesn’t stay blocked forever waiting on I/O while idle) or uses threads, 
is to periodically call Connection::ping(). This sends the smallest 
possible amount of data to the database server, which will reset its 
idle timer. If the server responds, ping() returns true, so this also 
gives your program the ability to detect that the server has gone away 
before trying a query. Don’t ping the server too often, though, as it 
takes a tiny amount of processing capability to handle a ping, which can 
add up to a significant amount if done often enough by a client, or even 
just rarely by enough clients. When I do this, I just assume the 
database server is there, pinging it no more often than half the 
connection timeout. With the default of 8 hours, then, I’d ping between 
every 4 and 7 hours.

An interesting variant is to ping the server before each query, or, 
better, before each group of queries within a larger operation. It has 
an advantage over pinging during idle time in that the client is about 
to use far more server resources to handle the query than it will take 
to handle the ping, so the ping time gets lost in the overhead. On the 
other hand, if the client issues queries frequently when not idle, it 
can result in a lot more pings than would happen if you just pinged 
every N hours while idle.

Finally, some programmers prefer to wrap the querying mechanism in an 
error handler that catches the “server has gone away” error and tries to 
reestablish the connection and reissue the query. This adds some 
complexity, but it makes your program more robust without taking up 
unnecessary resources. If you did this, you could even change the server 
to drop idle connections more often, thus tying up fewer TCP/IP stack 
resources.