[egenix-users] On demand connect

[M.-A. Lemburg]

M.-A. Lemburg wrote:
> F.Baart at SFK.NL wrote:
> 
>> Dear egenix users,
>>
>>
>> I use mx.ODBC.Zope DA 1.0.8  with mysql 4.0.14 on zope 2.6.1.  Once in a
>> while I get the following error message while executing a query from 
>> zope:
>>
>>
>> Error Type: ProgrammingError
>> Error Value: ('HY000', 2013, '[MySQL][ODBC 3.51
>> Driver][mysqld-4.0.14-standard]Lost connection to MySQL server during
>> query', 5923)
>>
>>
>> I found that this happens when no query has been executed for 1800 
>> seconds
>> (mysql wait-timeout variable). I also get this message when I manually 
>> kill
>> the connection from mysql. If I execute the same query again it will run
>> succesful.
>>
>>
>> I thought that using the "Use Connect on Demand" option would 
>> reconnect to
>> mysql automaticly. It does reconnect but only after raising a
>> ProgrammingError at the first query which gets excecuted after a lost
>> connection.
>>
>>
>> Does anybody know how to fix this?
> 
> 
> The Zope DA automatically reconnects in case it gets an
> OperatingError from the database (which is what the database
> driver should generate for these kinds of errors).
> 
> The error that MySQL uses a generic error HY0000 which could
> be anything from connection problems to interface problems
> or programming errors. The driver *should* have sent HYT00
> or HYT01.
> 
> The next version of the Zope DA will have an option that
> allows doing an alive check on the connection before trying
> to execute a statement (more expensive, but needed
> in order to support auto-reconnect with some ODBC
> drivers).
> 
> Another thing you could try is report this as bug to MySQL
> and have them generate the correct error code for the
> timeout.

I just remembered a trick that might help in the meantime:

You can change the mapping of ODBC error codes to
exceptions that mxODBC uses internally by accessing
the ODBC sub-package's .errorclass dictionary, e.g.

import mx.ODBC.Windows
mx.ODBC.Windows.errorclass['HY000'] = mx.ODBC.Windows.OperationalError

will map the generic error to an OperationalError which
triggers the auto-reconnect in the Zope DA (on Windows).

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Apr 13 2004)
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
Python UK 2004, Oxford, UK                                   2 days left
EuroPython 2004, Göteborg, Sweden                           54 days left

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::