Re: OperationalError: (2006, 'MySQL server has gone away')

-- 
Seth Remington
SaberLogic LLC
661 Suite B Weber Drive   Wadsworth, Ohio   44281
[email protected]   Ph: 330.335.6442    Fax: 330.336.8559


>>> On Tue, Dec 26, 2006 at  9:53 pm, in message
<94cdd26f0612261853p6d6027acq2da1cbfbcc46a16b-JsoAwUIsXosN+BqQ9rBEUg@public.gmane.org>,
[email protected] wrote: 
> On 12/26/06, Roger Haase <crosseyedpenguin-/[email protected]> wrote:
>> Hi,
>>
>> I am testing a new server with Fedora Core 6 and Middlekit- Webware 9.2
>> and MySQL 5.0.  When the new server is idle for 5 (or more) hours I get
>> abends with OperationalError: (2006, 'MySQL server has gone away').
>> Restarting the webkit AppServer eliminates the immediate problem.
>>
>> I have not seen this problem on a lightly loaded server with Fedora
>> Core 4, MySQL 4.1, and Webware 9.1.  The Webkit AppServer is restarted
>> by cron daily in both cases.   In both cases the result of a call to
>> Middlekit MySQLObjectStore is saved in the Sitepage module at load time
>> for use by all servlets.
>>
>> Where should I be setting a timeout parameter for the Middlekit- MySql
>> connection?  Is 24 hours an unreasonable time limit?
> 
> Did your version of MySQLdb change as well? I've had problems on more
> than one occasion when upgrading.
> 
> Also, is there a setting on the server side, perhaps? Maybe something
> about how long to keep connections alive?
> 
> If this ultimately requires a fix in MiddleKit, I'm not yet sure what
> it should be or where it should go, as I haven't experienced this
> problem myself (but have not yet run MySQL 5 for lengthy periods of
> time). One option you might try is tweaking
> MiddleKit\Run\MySQLObjectStore.py to trap that specific exception and
> do some kind of recovery and retry. If you need help coding that, let
> me know.
> 
> But if there is a MySQL option that applies, that will be probably be
> the most expedient fix.
> 
> Let us know how it goes,
> 
> - Chuck


I ran into this same issue in SaberNet DCS (http://sabernetdcs.sourceforge.net/). I don't recall all of the details when I researched it at the time but I can tell you the solution I came up with. It involves using the ping() function in MySQLdb to keep the connection alive:


from TaskKit.Task import Task
from sndcs.Store import store
import time

class KeepSQLAlive(Task):
    
    def __init__(self):
            Task.__init__(self)
            self.store = store
    
    def run(self):
            conn, cur = self.store.connectionAndCursor()
            try:
                conn.ping()
            except:
                pass


This is implemented as a TaskKit task and by default runs every 30 seconds (probably overkill). The "store" object is a singleton data store instance used by everything, probably similar to what you describe in your app. Hope that helps.

-Seth

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________

-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV