[Zope3-dev] Re: rdb: Disappearing Connection

[Jim Washington]

Dieter Maurer wrote:

>Jim Washington wrote at 2005-12-13 21:40 -0500:
>  
>
>>...
>>Now, looking closer at the code, a ping like this might be not too bad, 
>>because isConnected() is only called when a connection is requested, not 
>>for every SQL statement executed.  So, it might not be so onerous as 
>>originally thought. Still not ideal, though.
>>    
>>
>
>Be careful what you do when you find that "isConnected()" returns false.
>Note, that, in general, it would be a bug to just reopen a new connection
>in this case: previous operations against the connection might have
>been lost without notice when you simply reopen a new connection
>and do as if nothing bad had happened.
>Instead, you should reopen the connection and then raise an exception
>similar to "ZODB.POSException.ConflictError". Hopefully, the publisher
>will handle this kind of exception correctly (by retrying the complete
>request).
>
>  
>
Thanks, Dieter.

The situation I wish to handle is the case where MySQL server silently 
closes its connection with Zope after 8 hours idle, which likely will 
happen at least once a day.

What seems to work for me now is the following as 
mysqldbda.adapter.MySQLdbAdapter.isConnected()

def isConnected(self):
        try:
            self._v_connection.ping()
        except:
            # not connected or ping did not restore MySQL connection
            if self._v_connection is not None:
                self._v_connection.close()
                self._v_connection = None
            return False
        return True

I am not well-versed in the intricacies of conflictError, so what else 
should happen here?  I do not see how there would be unresolved 
transactions remaining, and the connection I need is now available or 
soon will be established.

In the current rdb implementation, isConnected() is not called for each 
SQL statement.  It is only called when the utility is called.

e.g.,

conn = zapi.getUtility(IZopeDatabaseAdapter, 'spam') #isConnected has 
not been called yet
db = conn() #here, isConnected() gets called
c = db.cursor() #isConnected() is not called
c.execute('SELECT 1') #isConnected() is not called

-Jim Washington