MySQL Has Gone Away

[jlowery]

This isn't unique to SQLAlchemy, but when using MySQL after long
periods in inactivity (in a web-app server) with the objectstore
session, the error:

MySQL server has gone away

comes up, meaning the database conneciton has timed out.

Is there anyway to inject in a simple test query (and possibly
reconnect) function into SQLAlchemy so it runs this procedure to ensure
a valid connection?

[Michael Bayer]

use the "pool_recycle" setting on your create_engine call.

[jlowery]

Still same error with:

db= create_engine('mysql://root@localhost/test', pool_recycle=True)
meta = BoundMetaData(db)

[Michael Bayer]

pool_recycle is an integer number of seconds which to wait before
reopening a conneciton. However, setting it to "True" is equivalent to
1 which means it will reopen connections constantly.

check out the FAQ entry on this:

http://www.sqlalchemy.org/trac/wiki/FAQ#MySQLserverhasgoneaway/psycopg.InterfaceError:connectionalreadyclosed

note that the recycle only occurs on the connect() operation.

also turn on "echo_pool=True" so that you can see the connections being
recycled in the standard output

[Monty Taylor]

You can control the amount of time before your connection is
terminated with the MySQL parameter wait_timeout - although addressing
the fundamental problem of the app handling dropped connections should
obviously be addressed. In higher load environments with MySQL it may
actually behoove you to reap and recycle connections more frequently
rather than hold thousands of them open as you will typically be able
to scale better and handle higher concurrency that way. You also want
your app to be able to handle dropped connections in case you ever
want to build an HA database system behind the app, since connections
through a load balancer or other thing may want to be reaped more
rapidly than the app would expect.

But as a stop-gap measure (and if you aren't expecting data rates in
the 1000's per second), you can set wait_timeout (set in seconds) to
something hideously high.

Monty

[Lee McFadden]

I'm still having this problem with MySQL (both 4.1 and 5.0), SA
(tested with both 0.2.8 and 0.3.1) and TurboGears 1.0b1.

I've gone over the TG connection code a number of times and I can't
see anything wrong with it. This isn't after a long idle time either.
Sometimes I can start the app and have the "MySQL server has gone
away" exception after 2 or 3 requests.

To reproduce this is simple. Quickstart a new turbogears project with
SA and identity:

tg-admin quickstart -i --sqlalchemy

Change the dburi in dev.cfg to a mysql db. Then use ab to pound on
the server. The magic numbers that *always* causes the app to fail at
some point during the process is as follows:

ab -n 500 -c 10 http://localhost:8080/

I'm pretty sure that this isn't a TG problem (although I don't have an
issue with being proven wrong :) so is this an SA issue or a MySQLdb
issue?

Thanks,

Lee

--
Lee McFadden

blog: http://www.splee.co.uk
work: http://fireflisystems.com
skype: fireflisystems

[Sébastien LELONG]

> I'm still having this problem with MySQL (both 4.1 and 5.0), SA
> (tested with both 0.2.8 and 0.3.1) and TurboGears 1.0b1.

Almost the same for me (MySQL 5.0.22, SA 0.2.8 and 0.3.1, using CherryPy).
Using pool_recycle works nice on a non-high load environments. But while I
benchmark my app (using siege or ab), this always produces "Lost connection
during MySQL query...", and sometimes "MySQL server has gone away...".

Note the version of MySQLdb is important: using 1.2.2b1 (didn't tested the
last 1.2.2b2) can reduce those errors, but not completely though. Also,
MySQLdb is not thread-safe, so using it in a multithreaded environment can be
painful... (but I'm sure that's transparent using TG).

Since SA 0.2.4 (pool_recycle did not exist at this time), I use a specific
pool, derived from the QueuePool class, which *always* check the current
checked-out connection is valid, using connection.ping() (MySQL specific, I
think not all DBs support this). I'm still using my pool as of 0.3.1, to
prevent those kind of errors.

I didn't find out why this occurs: having a test-case is very difficult. I'd
also investigated the way the app access MySQL. IIRC, while using a socket,
if a connection is timed-out, reaccessing the server
automatically "regenerate" the connection (well, actually, I've not observed
this behavior for all the mysql servers I use), without producing those
errors. I have this kind of lines in MySQL logs when this occurs:

Connect slelong@localhost on dev
blablabla

Well, that's a lot of obscure observations... without a clean solution ! But
maybe someone will have some ideas...

Cheers,

--
Sébastien LELONG
sebastien.lelong[at]sirloon.net

[Michael Bayer]

yes it appears that MySQLDB, among its many limitations, is also
completely not threadsafe. Running a barebones web test to a MySQL
produces the same error. its a little amazing SA has made it for over
a year and nobody has really had this problem before.

So, for non-threadsafe DBAPIs we use the SingletonThreadPool, like
this:

meta = BoundMetaData('mysql://scott:tiger@localhost/test',
poolclass=pool.SingletonThreadPool)

It appears that I will have to make SingletonThreadPool the defualt
pool for the mysql module the same way it is for sqlite ....although Im
going to check MySQLDB now to see if thread-safety has been worked into
newer versions.

[Michael Bayer]

ok i REALLY cant narrow down the exact thing that causes this to happen
with MySQLdb. I have gone through pool.py carefully, adding flags and
assertions to the code to insure that its not sharing connections *or*
cursors between threads; its not. all cursors are closed before the
connection is ever returned to the pool. running a small WSGI-based
benchmark, i can consistently get the "mysql server has gone away"
errors as well as "commands out of sync". running the same benchmark
with postgres has no issues.

So i have checked in a fix in 2132 that does not prevent the error but
*does* keep it from being catastrophic, and its that the mysql dialect
catches error code 2006 and 2014 and invalidates the connection before
re-throwing the error, so that at least the pool doesnt get filled up
with busted connections.

as far as SingletonThreadPool, its not a terrific default setting since
for an applciation that keeps opening new threads (as some web
containers do), youre going to be opening new connections all the time.
also i think this issue is also platform-specific to some degree as
some colleagues on irc are unable to reproduce the problem on their
linux machines.

[Lee McFadden]

I've been able to recreate the problem on (OSX 10.4.8, MySQL 4.1,
MySQLdb 1.2.1p2), (Fedora Core 6, MySQL 5.0, MySQLdb 1.2.2b2) and also
on FreeBSD (although I have no version numbers for that system as it
was someone on IM yesterday who tested for me). Not sure if that
helps at all.

In the meantime I'm going to create a patch for TG to use
SingletonThreadPool as lower performance is better than no
performance.

Thanks again for your quick responses Michael.

[Arnar Birgisson]

Good to hear some work is being done one this. I'm having the exact
same problem as the OP describes. In my case, downgrading down to SA
0.2.7, not changing anything else, makes the problem go away - except
of course I don't have many wonderfuls SA 0.3.0 features :o)

On 12/8/06, Michael Bayer <zzz...@gmail.com> wrote:

> So i have checked in a fix in 2132 that does not prevent the error but
> *does* keep it from being catastrophic, and its that the mysql dialect
> catches error code 2006 and 2014 and invalidates the connection before
> re-throwing the error, so that at least the pool doesnt get filled up
> with busted connections.

So in a webserver environment this makes just the request where the
error happens fail, instead of making all subsequent requests on the
same thread fail.. correct? If so, half the battle is won in my case.

Arnar

[Michael Bayer]

well that makes my day a *lot* harder, since it means the problem can
be fixed. this is now ticket #387.

[Michael Bayer]

ok, dont change anything in turbogears yet. i think i have fixed the
problem, please try out rev 2133. ticket #387 explains the issue.

[Lee McFadden]

On 12/8/06, Michael Bayer <zzz...@gmail.com> wrote:
>

> ok, dont change anything in turbogears yet. i think i have fixed the
> problem, please try out rev 2133. ticket #387 explains the issue.
>

Well, I've made the changes already to work with the current release
of SQLAlchemy and I'll revert them once the fix that you've added here
has been tested and added to the latest SA release.

I'll run some tests with SA's trunk and see if this fixes the problem
without using the SingletonThreadPool.

Thanks again for getting this fixed so quickly.

[Sébastien LELONG]

> i think i have fixed the
> problem, please try out rev 2133. ticket #387 explains the issue.

This works perfectly for me !!! *Many* thanks for this bugfix.

Cheers

Seb

[Ray]

Michael Bayer wrote:
> ok, dont change anything in turbogears yet. i think i have fixed the
> problem, please try out rev 2133. ticket #387 explains the issue.

This issue seems very similar to one I found using the MySQL Zope
database adapter. The symptom was the same (MySQL has gone away), and I
think the underlying issue might be the same. Both Alchemy and Zope DA
starting having this problem only after upgrading to MySQL 5.

It seems that the Alchemy patch resolves the issue while closing the
connection, but the real problem might be that the database closed the
connection to the client, and the client was never made aware? The
Zope DA uses a isConnected method to determine the validity of the
client connection. The fix for it involved making this method a
little more robust using the MySQL connection's ping, which (usually)
automatically reconnects to the DB as necessary. If a
MySQLdb.OperationalError is thrown while pinging, it closes the
connection. Perhaps the issue is similar for Alchemy, it's just not
manifesting itself until the connection is being closed. (Perhaps it
is being closed *because* the connection is invalid?)

I'm not sure if these issues are related, but they seem to be very
similar. Just thought this information might give more ideas/another
angle with which to view the problem.

[Michael Bayer]

as the ticket I mentioned says, the main problem was a bug in the
connection pool whereby rollback() was being called twice on a
connection that was already being used in another thread. so i dont
think theres any similarity to zope here.