perl dbi reconnect on disconnect
Solution 1
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.
Solution 2
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.
Linus
Updated on July 19, 2022Comments
-
Linus almost 2 years
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 ;} }
-
Linus over 12 yearshi, can you please point me to a full example on the usage of the Auto_reconnect? I am still pretty new at perl, can i just pass the attribute like below? my $connect = DBI->connect("dbi:mysql:$database:$host", $user, $pw, {RaiseError => 1, AutoCommit =>1, mysql_auto_reconnect=>1} );
-
Unos over 12 yearsYes, that should work. You can also set the bit after creating
$connect
, like$connect->{mysql_auto_reconnect} = 1
. However, you need consider the warning specified by @Ted, before using this feature. You can also take a look atDBIx::Connector
at CPAN, forFast, safe DBI connection and transaction management