Re: Reconnecting to mysql

Hello Jon,

> {mysql_auto_reconnect
> => 1} isn't enough since the database can be down at the time of my
> query and that makes DBD::mysql sad.

Sounds like you are using real MySQL server side prepared statements,
rather than the DBD emulation. So, one of the things you should
consider is that, although MySQL supports automatic reconnection, the
MySQL sever side components of the prepared statements are lost when
MySQL exits. Therefore, all of your DBI statement handles are
invalid/worthless, without their MySQL counterparts, as
mysql_auto_reconnect doesn't re-prepair the statements. If you were to
use driver emulated prepared statements, auto_reconnect should be
sufficient, to the best of my knowledge.

So, what you may be able to do:
* disable mysql_auto_reconnect
* have a connect sub, where you also (re)prepair all of your prepaired
statements one after the other (you could save them in a global hash).
You could loop the connect() part every few seconds until it succeeds
and then re-prepair the statements.
* If you don't like/use evals around your DBI code, you need to set
Raise Error to 0 and check the return value from connect, prepare and
execute for errors and call the connect sub again if necessary and
then re-execute the statement.
* You can use $dbh->ping to check whether your connection is still
live (as long as mysql_auto_reconnect is off). However, rather than
checking that before every execute/prepare, you should probably just
check the return value from DBI to catch any errors.

Best Regards,

Christian

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]