[Zope3-dev] rdb: Disappearing Connection
Christian Theune
ct at gocept.com
Tue Dec 13 15:56:06 EST 2005
Hi,
Am Dienstag, den 13.12.2005, 13:47 -0500 schrieb Jim Washington:
> I have an rdb connection that keeps disappearing.
>
> I know the real problem is that the default wait_timeout and
> interactive_timeout for MySql 5.0 is set at 8 hours. The site I am
> working on for development waits 8 hours overnight for me to start
> working again, then I get
>
> "Mysql server has gone away"
I love that one. Really. And I had lots of that kind of trouble with
mysql lately. What a fabulous database. ;)
> or something like that. The easy way to refresh the connection is to
> restart the zope3 instance.
>
> The question is
>
> Where should this be handled? Hooking a
> connection-check/reconnect-if-down in each method contacting the sql
> server seems to violate DRY.
ACK.
> So, should the connection check be in zope.app.rdb.ZopeCursor, or
> further up the chain in ZopeDatabaseAdapter or MySQLdb? I'm thinking
> that connections disappearing after 8 hours idle is probably a good
> thing, so than any process that forgets to conn.close() eventually
> releases the connection back to the pool (zope is not the only thing
> that connects to this db). I think that at the moment, this is specific
> to MySQL 5.0. But it would not surprise me if other databases might
> adopt this sort of policy to reduce "out of handles" errors.
MySQL has problems like that a lot but it might be that the Zope 3
adapter isn't as robust as it could be. (That's just guessing from my
point of view.)
> 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.
>
> I know that I can set wait_timeout and interactive_timeout to higher
> numbers in my.cnf. But let's say I have authentication looking at MySQL
> for data and I guess a too-small number for the timeout (what would be a
> good default, anyway? My users are often at schools that take breaks
> for weeks at a time). Then, several users attempt to log in and get the
> "contact Jim" page I provide for errors like this. Then, of course, my
> telephone starts ringing. Not good.
Well. First, the error would be something like an OperationalError (or
similar). Hmm. Not too distinguishable. Ideally we could:
- differentiate this kind of error from other OperationalErrors (like a
syntax error in your query)
- delete the _v_connection
- restart the transaction
Otherwise you might start over within a transaction that is out ouf sync
with the state of the mysql database.
Christian
--
gocept gmbh & co. kg - schalaunische str. 6 - 06366 koethen - germany
www.gocept.com - ct at gocept.com - phone +49 3496 30 99 112 -
fax +49 3496 30 99 118 - zope and plone consulting and development
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
Url : http://mail.zope.org/pipermail/zope3-dev/attachments/20051213/02cb58ce/attachment.bin
More information about the Zope3-dev
mailing list