"mysql server has gone away", again

[eas...@gmail.com]

Hi group,
Has anyone managed to resolve the mysql connection resiliency
problems? My hosting provider seems to run a 30 minute max on mysql
connections, so TG/SQLObject's inability to deal with this is a bit of
a non-starter.

As suggested in this thread
(http://groups.google.com/group/turbogears/browse_frm/thread/eeb289d5a0ca36f9/d2cd5f7ee2674082?q=mysql+server+away&rnum=1#d2cd5f7ee2674082)
I tried modifying the __get__ method in class PackageHub. It catches
the exception ok
but self.set_hub() doesn't seem to reset the connection.

I also tried catching the exception in SQLObject's mysqlconnection.py,
recalling MySQLConnection.makeConnection(). This also doesn't seem to
help.

I'm running TG0.8.8. Would upgrading help?

Thanks for any leads!
Paul
http://pauleastham.com

[Justin Johnson]

If your host is dropping the connection after 30 minutes of
inactivity then you could keep it alive by creating a thread that
performs a simple dummy SQLObject operation every 15 minutes or so to
keep the connection live.

However it is actually possible, although unlikely I've found, for a
connection to die and having a reconnection strategy would be a good
thing.

Justin

[eas...@gmail.com]

Unfortunately it's not that -- it seems to be that the server is
restarting every 30 minutes or
so, so all connections are lost idle or not. (Yeah, I'll look into
having the provider fix the database too :)

Paul

[Damjan]

I just tested with MySQLdb 1.2.0 and MySQL 4.1.14 and it seems
reconnection happens automatically.
>>> import MySQLdb
>>> db = MySQLdb.connect()
>>> db.thread_id()
3
This is actually the 3-rd connection from the start of the Mysql
server... now I stop and start the daemon.
>>> db.thread_id()
3
>>> c = db.cursor()
>>> db.thread_id()
3
>>> c.execute('show databases')
>>> db.thread_id()
1
Oh now you reconnect.

But since Mysql 5.0.3 auto-reconnect is disabled by default (it was
enabled by default previosly) since it's considered dangerous (you
silently loose all the connection state).

http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html

[Robin Haswell]

Justin Johnson wrote:
>
> If your host is dropping the connection after 30 minutes of
> inactivity then you could keep it alive by creating a thread that
> performs a simple dummy SQLObject operation every 15 minutes or so to
> keep the connection live.
>
> However it is actually possible, although unlikely I've found, for a
> connection to die and having a reconnection strategy would be a good
> thing.

Indeed, I have noticed in the past that this is possible. We really
should attempt to fix the bug at the source - will this require
modifying SQLObject or can it be done in TG?

-Rob

>
> Justin

[Jorge Godoy]

Em Sexta 05 Maio 2006 06:14, Robin Haswell escreveu:
>
> Indeed, I have noticed in the past that this is possible. We really
> should attempt to fix the bug at the source - will this require
> modifying SQLObject or can it be done in TG?

I'm 99% sure it is the ORM code (does this happen with SQL Alchemy?) or even
lower, at driver level... We can, maybe, do some workaround on TG's side,
but...

--
Jorge Godoy <jgo...@gmail.com>

[eas...@gmail.com]

I was able to fix this -- unfortunately the fix is in MySQLdb. You
need to set an option to auto-reconnect on the C-layer MYSQL object,
and as far as I can tell MySQLdb does not expose an interface into
python to do this.

The essential part of the solution is to make the following call after
the mysql_real_connect() in
_mysql.c:_mysql_ConnectionObject_Initialize():

my_bool bool = 1;
mysql_options(conn, MYSQL_OPT_RECONNECT, &bool);

I'll submit a patch to MySQLdb at sourceforge that exposes an interface
for this, so that the behavior may be controlled by SQLObject/TG. If
anyone wants this patch (which is a bit long to post due to all the
C/python API stuff), just send an email.

Paul
--
Paul Eastham eas...@gmail.com http://pauleastham.com

[Mark Ramm]

On 5/6/06, eas...@gmail.com <eas...@gmail.com> wrote:
>
> I was able to fix this -- unfortunately the fix is in MySQLdb. You
> need to set an option to auto-reconnect on the C-layer MYSQL object,
> and as far as I can tell MySQLdb does not expose an interface into
> python to do this.
>

This is awesome!

If you could post the patch in a ticket on the TurboGears or SQL
Object Trac that would be great. That way when/if the MySQLdb patch
makes it in we can push forward with the SQLObject side of things.

--
Mark Ramm-Christensen
email: mark at compoundthinking dot com
blog: www.compoundthinking.com/blog