Re: [Zope3-dev] Re: rdb: Disappearing Connection
Hi, Thanks for posting this, it saved me quite a few hours. On Fri, Dec 16, 2005 at 04:23:36PM -0500, Jim Washington wrote: > 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 have two comments that I noticed: * If the connection was closed directly, by something else, you can fail when you try to close the connection: File "/home/jinty/src/Z3/z3/src/mysqldbda/adapter.py", line 87, in isConnected self._v_connection.close() ProgrammingError: closing a closed connection Which means that _v_connection will not be set to None and it will carry on failing. * Also, if, in this transaction, some SQL has been sent through the connection, the connection will have registered itself with the transaction manager. This made both aborting and commiting the transaction fail for me. -- Brian Sutherland Metropolis - "it's the first movie with a robot. And she's a woman. And she's EVIL!!" ___ Zope3-dev mailing list Zope3-dev@zope.org Unsub: http://mail.zope.org/mailman/options/zope3-dev/archive%40mail-archive.com
Re: [Zope3-dev] Re: rdb: Disappearing Connection
Jim Washington wrote at 2005-12-16 16:23 -0500: > ... >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. "isConnected" is fine as you cite it above. However, the action taken (by the caller) when "isConnected" returns "False" might be wrong. Reopening and then using the connection in this case is safe only when the connection was not yet used before in this same transaction. -- Dieter ___ Zope3-dev mailing list Zope3-dev@zope.org Unsub: http://mail.zope.org/mailman/options/zope3-dev/archive%40mail-archive.com
Re: [Zope3-dev] Re: rdb: Disappearing Connection
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
___
Zope3-dev mailing list
Zope3-dev@zope.org
Unsub: http://mail.zope.org/mailman/options/zope3-dev/archive%40mail-archive.com
Re: [Zope3-dev] Re: rdb: Disappearing Connection
jürgen Kartnaller wrote: MySQL::Ping will automatically reopen a closed connection in a way where the existing connection object can still be used. If MySQL::Ping fails something really bad is going on. To reduce the number of ping's in my application I used a timer which was tested before every call to a database function. If the timer expired I did a ping to ensure mysql is reconnected. This is the code sqlobject is using : while (1): try: return cursor.execute(query) except MySQLdb.OperationalError, e: if e.args[0] == 2013: # SERVER_LOST error if self.debug: self.printDebug(conn, str(e), 'ERROR') else: raise So, checking for SERVER_LOST and then eventually using MySQL::Ping would do the trick. Jürgen Sorry if that is mentioned somewhere, I missed the begining of the thread, but I just want to add, that MySQL is not the only SQL server in use. And this particular problem is not MySQL specific - I'm having troubles with disappearing connections with PostgreSQL, which doesn't have MySQL::Ping. The patch, that was proposed already - use a SELECT statement to ensure the connection is available - is not the best solution at least in my case, because my code never dares to store, or reuse a connection, knowing that connections are being managed by Zope (with a volatile attribute) and this turns into one getConnection() call per request. I was thinking about handling exceptions, because it does generate exception at the end and I know I need to reconnect when I see it in the log (restart the server actually, because I authenticate trough the SQL database). I do hope however that I will be able to configure the SQL server in a way to prevent the connection from dissappearing at all, when the time comes to solve this problem. Regards Velko Ivanov ___ Zope3-dev mailing list Zope3-dev@zope.org Unsub: http://mail.zope.org/mailman/options/zope3-dev/archive%40mail-archive.com
Re: [Zope3-dev] Re: rdb: Disappearing Connection
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). -- Dieter ___ Zope3-dev mailing list Zope3-dev@zope.org Unsub: http://mail.zope.org/mailman/options/zope3-dev/archive%40mail-archive.com
Re: [Zope3-dev] Re: rdb: Disappearing Connection
jürgen Kartnaller wrote: Without going much deeper into the way zope uses database connections. Wouldn't MySQL:Ping solve the reconnect Problem. That's what I used in my old C++ projects. Some kind of "ensureConnected" at the right place. Jürgen Thanks! I'll look into it. -Jim Washington ___ Zope3-dev mailing list Zope3-dev@zope.org Unsub: http://mail.zope.org/mailman/options/zope3-dev/archive%40mail-archive.com
Re: [Zope3-dev] Re: rdb: Disappearing Connection
Jim Washington wrote: Florent Guillaume wrote: Jim Washington wrote: Spelunking a bit in the code, zope.app.rdb.ZopeDatabaseAdapter.isConnected() looks at whether _v_connection is present, not whether the connection is actually alive. If we fix this here, isConnected() perhaps should handle the case where the connection might be reaped by the back end db. Frankly anything that uses _v_ attributes is asking for trouble because it relies on low level behaviour of the ZODB and persistence mechanisms. They can disappear at unpredictable times. Avoid them. Hi, Florent In this case, it is not particulary a problem if the _v_ attribute is not there. It gets recreated if it does not exist. It is designed this way so that different zope threads get separate rdb connections, which is good. The problem here is false positives, where the _v_ attribute exists, but the connection it promises has been silently expired and disconnected by the MySQL server. I was thinking of a check in isConnected() by making a cursor then "try"ing executing "select 1" and return True if it does not raise the exception. But that would be bad because it ~doubles the number of requests to the SQL server. Perhaps not smart. 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. -Jim Washington ___ Zope3-dev mailing list Zope3-dev@zope.org Unsub: http://mail.zope.org/mailman/options/zope3-dev/archive%40mail-archive.com
