AnyEvent::DBI and database reconnection

I'm using AnyEvent::DBI in a singleton package, and when there is no activity in the database / queries etc after many hours, I get this error while trying to issue a query:

DBD::mysql::st execute failed: MySQL server has gone away at /usr/local/share/perl/5.10.1/AnyEvent/DBI.pm line 98.

A simple approach is to: connect to DB ==> Issue query ==> Close connection. this way is what I usually use when I'm working with DBI.

Th开发者_开发百科e issue in my case is that I cant find a way to re-connect to the database using AnyEvent::DBI, except from creating a new AnyEvent::DBI instance (new).

Any suggestions would be great!

As per the DBD::mysql documentation on CPAN, you want to use:

$dbh->{mysql_auto_reconnect} = 1;

Here is the link to the reference:

http://metacpan.org/pod/DBD::mysql

In MySQL there's a wait_timeout variable that sets the duration of non-interactive connections.

For long-running connections you can try to alter that value ( default = 28800 ), to a more suitable one.

wait_timeout value can be updated with a simple query

mysql> SET SESSION wait_timeout = 50000;

and retrieved with another one

mysql> SHOW VARIABLES LIKE 'wait_timeout';