perl dbi reconnect on disconnect

Question

I have searched google but could not find an answer to what I think is an easy question.

I have a Perl code (example below) that gets data every 3 seconds and updates the received data into MySQL database but sometimes MySQL database is not available and the script dies. How can I make MySQL connection again if it fails?

use DBD::Mysql;

sub updateMysqlDB{
my $connect = DBI->connect("dbi:mysql:$database:$host", 
                        $user,
                        $pw,
                        {RaiseError => 1}
                        );
$myquery = "My sql query to insrt data into columns";
$query_handle=$connect->prepare($myquery);
$query_handle->execute();
$connect->disconnect;
}

while (1) {

if data received call updateMysqlDB ();

else wait for data { sleep 3 ;}
}

Answer 1

The DBD::mysql driver (that DBI uses for MySQL databases) supports an attribute mysql_auto_reconnect. To turn it on, just execute

$connect->{mysql_auto_reconnect} = 1;

Note that the docs have this warning:

Setting mysql_auto_reconnect to on is not advised if 'lock tables' is used because if DBD::mysql reconnect to mysql all table locks will be lost. This attribute is ignored when AutoCommit is turned off, and when AutoCommit is turned off, DBD::mysql will not automatically reconnect to the server.

Answer 2

You can also look at this thread : http://www.perlmonks.org/?node_id=317168

This discusses the way to deal with "MySQL server has gone away" problem, but a few answers apply to your problem too. You can use the recommendations there, in addition to the mysql_auto_reconnect switch.