MySQL server has gone away

[mback2k]

After some time the tornado server runs into the following error for each WebSocket connection. Any idea why this might happen while the MySQL server is still there? I guess it might be a problem related to connection timeouts:

ERROR:tornado.application:Uncaught exception in /data/782/e73lchdl/websocket
Traceback (most recent call last):
  File "/var/local/pyweb/virtualenv/webgcal/local/lib/python2.7/site-packages/tornado/websocket.py", line 369, in _run_callback
    callback(*args, **kwargs)
  File "/var/local/pyweb/virtualenv/webgcal/local/lib/python2.7/site-packages/sockjs/tornado/transports/websocket.py", line 70, in on_message
    self.abort_connection()
  File "/var/local/pyweb/virtualenv/webgcal/local/lib/python2.7/site-packages/sockjs/tornado/websocket.py", line 28, in abort_connection
    self.ws_connection._abort()
AttributeError: 'NoneType' object has no attribute '_abort'
ERROR:tornado.general:WebSocket
Traceback (most recent call last):
  File "/var/local/pyweb/virtualenv/webgcal/local/lib/python2.7/site-packages/sockjs/tornado/transports/websocket.py", line 60, in on_message
    self.session.on_messages(msg)
  File "/var/local/pyweb/virtualenv/webgcal/local/lib/python2.7/site-packages/sockjs/tornado/session.py", line 423, in on_messages
    self.conn.on_message(msg)
  File "/var/local/pyweb/virtualenv/webgcal/local/lib/python2.7/site-packages/swampdragon/connections/sockjs_connection.py", line 39, in on_message
    raise e
OperationalError: (2006, 'MySQL server has gone away')

[hagsteel]

I have not used this with MySQL (I'm using Postgres for my projects), and I don't have this problem with postgres.
Any more information you can give me so I can try to replicate this?
I will setup a test project with MySQL and see if I get this issue.

[hagsteel]

I've created a demo project running a celery task every 2 second updating a self publishing model.
I have five browsers open viewing the page that receives the updates.
So far I have not seen any issues with MySQL but I'll leave it running for a while.

[mback2k]

Thanks for trying to re-create my issue. It seems like my MySQL server is running into a connection limit and randomly aborts connections. I get tons of the following errors:

[Warning] Aborted connection $n to db: '$dbname' user: '$user' host: '$host' (Unknown error)

Let me check if I can increase that limit.

[mback2k]

Okay, got it. The MySQL wait_timeout parameter was set too low. 600 seconds is not enough for the SwampDragon server. Does it perform any kind of heartbeat operation to keep the database connection open?

[hagsteel]

Currently it doesn't. I would like to know how to replicate this issue.
I'm not overly familiar with MySQL. I set the timeout to a low number but I'm still not getting a timeout.
Could you describe your setup so I can tweak my test project to be more similar (and hopefully get the same issue so I can solve it)?

[mback2k]

It seems like increasing wait_time only delays the problem. Here is the my.cnf used for mariadb-server (10.0.13+maria-1~wheezy) and the relevant environment-specific Django config: https://gist.github.com/mback2k/899fced7241f64b50793. The remaining Django config can be found here: https://github.com/mback2k/django-webgcal/tree/master/webgcal/settings.
You said that your celery task actually performs something in the background, so the database connection is actually used. What happens if you disable that task, just connect to the WebSocket once to get some data and then close your browser until wait_timeout is hit? Thanks for investigating this.

[hagsteel]

I've managed to replicate this issue now (thanks for the settings).
It doesn't happen when running with Postgres (and SQLite) which are the only databases I've tried until now.

It wasn't until I added

    'OPTIONS': {
        'init_command': 'SET storage_engine=INNODB',
    }

to settings that this started happening.

I've created a gist for this for now until I figure out what to do in the long run:
https://gist.github.com/jonashagstedt/6113b0e0e9cd4b4c3585

Let me know if that solves the problem.
I've tested this by setting the timeout to a very low number and it works.

Remember to update the connection in settings:

SWAMP_DRAGON_CONNECTION = ('myproject.connection.MysqlHeartbeatConnection', '/data')

[hagsteel]

I ran a very simple test to make sure there wasn't a significant performance loss with this solution (and there wasn't).

The test is very simple and does the following:

1. Connect 300 clients
2. Subscribe to a channel
3. Get a list of models

With the gist provided above, I ran the test five times:
631 ms
624 ms
642 ms
642 ms
616 ms
avg: 631 ms

With the default connection:
639 ms
632 ms
642 ms
641 ms
653 ms
avg: 641 ms

[mback2k]

Thanks for providing this workaround. I verified that it also solves the problem for me. I also tried to alternatively set CONN_MAX_AGE to something below wait_timeout, but that alone didn't help. Maybe you want to include that heartbeat into the default connection and make it detect MySQL like this:

if connection.settings_dict['ENGINE'] == 'django.db.backends.mysql':

[hagsteel]

I've thought about it for a bit now and it would be clever to check the DB engine, however the timeout is posing a bit of a problem.
Since the timeout of MySql can differ from environments it's something that needs to be set by the user.

I wrote up a short blog post about it (http://swampdragon.net/blog/mysql-server-has-gone-away/) instead.

Good suggestion though