mysql_ping() c api fails

Question

connection to mysql is getting lost after 8 hours ( i.e afer wait_timeout varibale times out). i am trying to use mysql_ping() to reconnect to the server but ping gives me Mysql server gone away error. I am using sql version 5.1. so i am not using mysql_options() to enable the reconnect flag as mysql_real_connect() sets it to 0. I am explicitly setting the reconnect flag to 1 like mysql_Conn->reconnect =1; after calling mysql_real_connect(). But mysqlping doesn't work. The reconnection is not happening. Kindly advice. Thanks in advance

Answer 1

Instead of trying to ping the connection back to life after it's dropped, far better to ping it periodically at about half the idle disconnect time (4 hours in your case) to keep it alive.

If the ping fails, drop the connection explicitly and reconnect.

Also, you might want to add something like this to your /etc/my.cnf:

[mysqld]
wait_timeout=400000

The timeout is in seconds, so this is about four and a half days, long enough to keep a connection alive over a long weekend while no one is using your system.