From: Warren Young
Date: June 5 2007 5:09pm
Subject: Re: Stay connected with DB
List-Archive: http://lists.mysql.com/plusplus/6628
Message-Id: <466598C3.5000100@etr-usa.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Manuel Jung wrote:
> 
> It stays connected for a day or so, but always after a night
> (where probably not querys where started) the connections are lost. 

This is a relatively recent change in the MySQL server defaults.  (I 
believe it changed sometime in the middle of the 4.1 series.)  If the 
server doesn't see a query (or a ping) on a connection for 8 hours, it 
drops the connection.  This is a desirable feature, because it avoids 
problems with too many idle connections building up, wasting server 
resources.

If you don't like this behavior, it's better to change the wait_timeout 
setting in the [mysqld] section of the my.cnf file, which controls this 
behavior.  The default is 28800, or 8 hours, measured in seconds.  If 
you know you will get at least one query a day, you could change it to 
86400.

> Im trying to use Connection::ping for possibly reconnect, before i start
> some MySQL Work, but it doesn't help. 

Of course not.  The connection is already closed by that point.

If you must use pings to work around this, you need to ping separately 
from the main work loop, so the pings go out regardless of what work is 
happening.

If your program just sits there doing nothing while waiting for work, 
just set it to ping the server every 4 hours or so.

If instead it's an event-based system, where your code doesn't get 
called until there is work to do (as in a web application) there's not 
much you can do about this from the client side.  One more argument in 
favor of fixing this on the server side.

> I also create a new query object after pinging. 
 > (Query objects are just alive for a short time in my application).

That's not going to affect this.  If you recreate the _Connection_ 
object each time, that would fix the problem, but you'll pay a speed 
penalty.  This is why I keep coming back to fixing the server policy to 
suit your situation, or if that cannot work, adding a background pinger.

> What is the preferred solution to keep connections alive or reconnect them
> automaticly?

The preferred solution is to avoid getting into a situation where you 
need to do keepalives or reconnects.  Those solutions attack the 
symptom.  I prefer to attack the cause, where possible.