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.
>
