'mysql server has gone away'

[ivi...@gmail.com]

I'm getting the following after a couple of hours inactivity:

File
"/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1631-py2.4.egg/sqlobject/main.py",
line 1329, in selectBy
conn = connection or cls._connection
File
"/usr/lib/python2.4/site-packages/TurboGears-0.9a4-py2.4.egg/turbogears/database.py",
line 150, in __get__
return self.hub.__get__(obj, type)
File
"/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1631-py2.4.egg/sqlobject/dbconnection.py",
line 935, in __get__
return self.getConnection()
File
"/usr/lib/python2.4/site-packages/TurboGears-0.9a4-py2.4.egg/turbogears/database.py",
line 56, in getConnection
return self.begin(conn)
File
"/usr/lib/python2.4/site-packages/TurboGears-0.9a4-py2.4.egg/turbogears/database.py",
line 87, in begin
trans = conn.transaction()
File
"/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1631-py2.4.egg/sqlobject/dbconnection.py",
line 365, in transaction
return Transaction(self)
File
"/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1631-py2.4.egg/sqlobject/dbconnection.py",
line 790, in __init__
self._dbConnection._setAutoCommit(self._connection, 0)
File
"/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1631-py2.4.egg/sqlobject/mysql/mysqlconnection.py",
line 61, in _setAutoCommit
conn.autocommit(auto)
OperationalError: (2006, 'MySQL server has gone away')

This also happens when I 'killall -9 mysqld' (ie kill the current open
threads)

>From looking at the turbogears PackageManager in database.py, it seems
it is meant to reconnect, but it doesn't for me. I've been unable to
figure it out - am I doing something wrong or is this a bug?

--
Dan Ros

[ivi...@gmail.com]

I fixed this with this hack, which tries to reestablish a connection
_once_ on error. This seems to take care of most transient 'server has
gone away' mysql server errors like timed-out connections, restarted
mysql server, etc.

Is there a better way of doing this? If not could I suggest that this
kind of functionality is included in TG? It seems like this _should_ be
a common problem.

I use mysql 5, TG 0.9a4 fwiw.

Changed the PackageManager code in database.py as follows:

def __get__(self, obj, type):
import _mysql_exceptions
if not self.hub:
self.set_hub()
try:
data = self.hub.__get__(obj, type)
except _mysql_exceptions.OperationalError:
#reconnect and try again once
self.set_hub()
data = self.hub.__get__(obj, type)
return data

[fren...@gmail.com]

I've seen this kind of transient error before with another web
framework called Skunkweb (and its ORM "PyDO"), and once with
Turbogears as well -- pretty much any time I've tried to use MySQLdb,
MySQL (with MyISAM tables, not innodb -- I'm switching to InnoDB for
TG), and any python web framework. The culprits I found were:

OperationalError 2006 ("Server gone away")
OperationalError 2013 ("Lost connection during query")
ProgrammingError 2014 ("Commands out of sync; You can't run this
command now")

I didn't really get a chance to track down the root cause; it may have
been some timeout in the mysql server, some quirk of the DB connection
pooling of the web framework I was using, a quirk in MySQLdb itself, or
some pathological combination of the above.

If you google some of these error messages, though, it seems that other
users are commonly afflicted with this problem too -- Django, Webware,
Zope, etc., which leads me to believe it's a problem with MySQL or
MySQLdb. Most other blog or newsgroup posts mentioning it say it seems
to be "random" but "frequent."

I implemented a similar (ghetto) solution (retrying on any of those
three exceptions by re-establishing the connection and re-executing the
query) which has worked fine so far, but this solution is hardly
elegant, and in the back of my mind I'm still afraid of lost and/or
duplicate queries (e.g. consequences of dropping or duplicating "update
account set balance=balance+100 where uid=xxx" are disastrous.)

Hopefully if more people see this problem we can get to the bottom of
it -- I think it's caused a lot of grief and I've never seen a robust
solution to it. Next time I encounter it I'll dig a little deeper.

Update: People in ruby-on-rails land have encountered the same thing,
with similar extensive commentary and hand-wringing (
http://dev.rubyonrails.org/ticket/428 ). The bug is marked as
resolved-fixed, and I don't have time right this second to verify, but
it looks like they check DB connections to see if they're stale before
executing queries. Notably (and appropriately), they also strongly
decided against any automatic query retrying because of data integrity
issues.

Maybe a similar workaround would be appropriate for TG/SQLObject.

-Drew

[Patrick Coleman]

I'd like to chip in that I have this problem too, and gathered (from
the fact that I could find no other documented instances of the
problems) that it was something I was doing wrong. Perhaps it is - if
someone else has deployed Turbogears on a low-traffic site on MySQL and
has no problems please let us know here :)

Apparently increasing the wait_timeout variable on your mysql server is
supposed to help, though I saw no difference in my case. This bug
drives me nuts, because everything else about turbogears is very slick,
and it isn't exactly in a feature that rarely gets used. 500 Server
errors don't look terribly professional to end users, and telling them
to "Just refresh, and it'll work the second time" isn't an option
either.

Surely this isn't a difficult bug to fix? How about if the server
discarded any connection that has not been used for x seconds before
executing a query?
Thanks,
Patrick
--
http://www.labyrinthdata.net.au

[Gerhard Häring]

Patrick Coleman wrote:
> I'd like to chip in that I have this problem too, and gathered (from
> the fact that I could find no other documented instances of the
> problems) that it was something I was doing wrong. Perhaps it is - if
> someone else has deployed Turbogears on a low-traffic site on MySQL and

> has no problems please let us know here :) [...]

I've filed Ticket #781 and described the real problem: SQLObject does
not transparently reconnect if a connection goes bad.

-- Gerhard