List:MySQL++« Previous MessageNext Message »
From:Rick Gutleber Date:April 14 2009 5:01pm
Subject:Re: mysql++'s connection hangs after long timeout
View as plain text  
I experienced this problem a year or two ago and used the ping( ) option 
to solve it.  The application has a built-in event handling system that 
allows for timers, etc, in the main thread, so we set a timer to 
periodically ping the database.   It was an easy fix and solved the problem.

Warren Young wrote:
> 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.
>

Thread
mysql++'s connection hangs after long timeoutDenis Nagorny13 Apr
  • Re: mysql++'s connection hangs after long timeoutWarren Young13 Apr
    • Re: mysql++'s connection hangs after long timeoutDenis Nagorny14 Apr
    • Re: mysql++'s connection hangs after long timeoutRick Gutleber14 Apr