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
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
[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). 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 -- --- Jürgen Kartnaller mailto:juergen_at_kartnaller.at http://www.kartnaller.at http://www.mcb-bregenz.at --- ___ 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
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
[Zope3-dev] Re: rdb: Disappearing Connection
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. Florent -- Florent Guillaume, Nuxeo (Paris, France) Director of RD +33 1 40 33 71 59 http://nuxeo.com [EMAIL PROTECTED] ___ Zope3-dev mailing list Zope3-dev@zope.org Unsub: http://mail.zope.org/mailman/options/zope3-dev/archive%40mail-archive.com
[Zope3-dev] Re: rdb: Disappearing Connection
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 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. There *is* a reasonable pattern for using them, with the following caveat: the value you are caching must depend *only* on other attribtues of the *same* persistent object *or* its non-persistent subobjects (i.e., the values are pickled / ghostified as part of the same ZODB record). In this case, the following works entirely as expected: class Foo(SomePersistentBaseClass): _v_cache_an_expensive_operation = None def getExpensiveResult(self): if self._v_cache_an_expensive_operation is None: self._v_cache_an_expensive_operation = _expensive() return self._v_cache_an_expensive_operation This pattern is reliable because the volatile will be wiped if *any* of the attributes of its object are modified, at the point that the transaction (or subtransaction) commits / reaches a savepoint. If '_expensive' depends on *any other persistent object*, you lose (eventually, anyway). Tres. - -- === Tres Seaver +1 202-558-7113 [EMAIL PROTECTED] Palladion Software Excellence by Designhttp://palladion.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDn3gV+gerLs4ltQ4RAmekAKCf8ByFHX9beo51HUYOYyIoAm6eswCfRkLi czMxyJZPhaKMgq6K1lJup6g= =gkrJ -END PGP SIGNATURE- ___ 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 trying 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
