[storm] Storm and MySQL in WSGI environment - MySQL server run away

[Jamu Kakar]

Hi Steve,

On Wed, Jan 26, 2011 at 9:31 AM, Steve Kieu <msh.computing at gmail.com> wrote:
> I am developing a simple WSGI application using storm and MySQL. The problem
> is, after midnight ; or for some reason I restart MySQL server and my
> application dies with 500 - and inside I have exception by Storm raised:
>
> [Sun Jan 23 08:23:10 2011] [error] [client 169.173.0.179]   File
> "/usr/local/lib/python2.6/dist-packages/storm/databases/mysql.py", line 106,
> in execute
> [Sun Jan 23 08:23:10 2011] [error] [client 169.173.0.179]     return
> Connection.execute(self, statement, params, noresult)
> [Sun Jan 23 08:23:10 2011] [error] [client 169.173.0.179]   File
> "/usr/local/lib/python2.6/dist-packages/storm/database.py", line 227, in
> execute
> [Sun Jan 23 08:23:10 2011] [error] [client 169.173.0.179]
> self._ensure_connected()
> [Sun Jan 23 08:23:10 2011] [error] [client 169.173.0.179]   File
> "/usr/local/lib/python2.6/dist-packages/storm/database.py", line 344, in
> _ensure_connected
> [Sun Jan 23 08:23:10 2011] [error] [client 169.173.0.179]     raise
> DisconnectionError("Already disconnected")
>
> Sometimes I got the error mesasge like ; MySQL has gonna away ....

This is the expected behaviour.  When the underlying database goes
away (or is restarted) and an existing connection is used, Storm will
raise a DisconnectionError.  It's up to your application to detect
this and determine how to recover.  In the case of a web server you
can usually just retry the request being handled.  Be careful to put a
maximum retry limit.  If the database doesn't come back quickly you
don't want to be continuously retrying requests.

> To fix, just restart apache.

This works because Storm is creating new connections to MySQL when you
restart Apache.

> The problem is that storm tried to use existing connection and the server is
> gone. Storm does not want to reinitialize the connection but raise
> exception. In my code it is clearly that for each request I create a dsn and
> a Store object like store = Store(create_database(dsn)). Unfortunately it
> still reuse the connection somehow and then dies. (probably the python
> environment is reusing these variable somehow)

Storm will raise a DisconnectionError, as a signal that the
transaction failed and you need to recover.  If you try to use the
connection again Storm will attempt to reconnect to the database
automatically.  If that fails, it'll raise DisconnectionError again.

> To prove that is the issue I took a cgi to wsgi gateway from python PEP
> somewhere, fix it a bit so I can convert the whole wsgi to use cgi. Now
> problem goes away, I can restart mysql server but the application still
> works as expected.

Yep, this is because the CGI application is opening a new connection
to the database for each request.  If the database disappeared during
the CGI request you'd see the same exception.

Thanks,
J.