Massyn has asked for the wisdom of the Perl Monks concerning the following question:

#!/fellow/monks.pl

Although I understand what the MySQL server has gone away error means from a mySQL point of view, I would like to know how to resolve it from a Perl DBI point of view. My script works perfectly through CGI, but putting it into mod_perl causes the error if there's no activity for 8 hours. The problem really is that I can't make any changes to mySQL (ISP restrictions, etc), and from what I've seen, Apache::DBI keeps the sessions alive, so obviously DBI still thinks it's alive, but mySQL has dropped the connection already.

Thank you fellow monks. 

|\/| _. _ _  ._
|  |(_|_>_>\/| |
           /

Replies are listed 'Best First'.
Re: MySQL server has gone away
by liz (Monsignor) on Dec 27, 2003 at 11:12 UTC
    From the documentation of DBD::mysql:
    mysql_auto_reconnect
    This attribute determines whether DBD::mysql will automatically reconnect to mysql if the connection be lost. This feature defaults to off; however, if either the GATEWAY_INTERFACE or MOD_PERL environment variable is set, DBD::mysql will turn mysql_auto_reconnect on. 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.

    Hope this helps.

    Liz

[reply]
Re: MySQL server has gone away
by sheep (Chaplain) on Dec 27, 2003 at 11:37 UTC

    Hi there,
    Liz has already replied to your question, I would like to add some info, hope this may help.
    You write, that:

    it...causes the error if there's no activity for 8 hours.
    perl.apache.org describes the so called morning bug suggesting setting the wait_timeout mysqld system variable during the mysqld start to a higher value.
    I know, you said, you can't make any changes to mySQL (MySQL?), but the same document also sends to the Apache::DBI documentation. I am not getting into details, because there is no point in retypeing what has been written in there, but what applies to your problem:
    It describes:
    1. The ping() method and its example.
    2. The way to configure the usage of the ping method (Apache::DBI->setPingTimeOut($data_source, $timeout)).
    Practical mod_perl book says, that this issue applies to old versions of DBI/DBD. As you don't provide any information abut versions the server is using, I can't suggest upgrading.
    Also on PM site dug in 306648 thread and pope in 185416 node write about the "morning bug".

    -sheep

[reply]
Re: MySQL server has gone away
by stvn (Monsignor) on Dec 28, 2003 at 03:21 UTC

    Personally I have never tried setting the attribute that Liz is suggesting, in general I am leary of things like that while using Apache::DBI. I feel that I am already giving up enough control over my connections to that module, to also allow automatic reconnects just leaves me uneasy. Maybe I have just spent too much time hunting mod_perl/DBI connection bugs.

    While I agree with sheep that this sounds something like the MySQL morning bug. I have seen other issues with DBI/DBD::mysql while using Apache::DBI and mod_perl where connections can seem to disappear (and then leak memory like crazy). I found this bug/issue on Win32 with Apache 2 & mod_perl 1.99 (yes, yes, I know its not even alpha, but I was forced into using it), but during my research I saw others having similar issues on Linux systems as well.

    I was able to solve my lost-connection/memory-leak issue by simply adding this code at the top of every function that was passed a DBI connection. 

    
$dbh ||= DBI->connect("connection string", "username", "password");

    [download]
    Its pretty efficient as well since it uses the short circuit assignment. The right hand side wont get executed unless it needs to be, and it will not incur the overhead of entering and exiting a "if" block.

    I know its kinda paranoid programming, but I find paranoia is a good thing when using both mod_perl and Apache::DBI.

    -stvn
[reply]
[d/l]
Re: MySQL server has gone away
by akis (Novice) on Dec 29, 2003 at 08:42 UTC
    Take a lesson from slashdot's database connection routine. Basically, you have a connect() function that will check if you are still connected. If yes, then all is well and return, but if connection has gone away, we try to reconnect again.

    Then have a wrapper for DBI's execute() function that will call connect() everytime before a real execute goes to your database. This may suffer a small preformance penality, but you need this in your production servers.

    The slashcode function in question is called sqlConnect in file Slash/DB/Utility/Utility.pm

    To re-establish a connection, slashcode actually calls the connect function recursively, which is interesting. Hopefully they know that the function won't be called over and over again.

[reply]
Re: MySQL server has gone away
by Massyn (Hermit) on Dec 31, 2003 at 05:25 UTC

    Thank you all for the great feedback. Have a blessed New Year!

    Thanks! 

    |\/| _. _ _  ._
|  |(_|_>_>\/| |
           /
[reply]

Back to Seekers of Perl Wisdom