[Zope3-dev] Re: rdb: Disappearing Connection

[Velko Ivanov]

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