Django - OperationalError: (2006, 'MySQL server has gone away')

Question

Bottom line first: How do you refresh the MySQL connection in django?

Following a MySQL server has gone away error I found that MySQL documentation and other sources (here) suggest increasing the wait_timeout MySQL parameter. To me this seems like a workaround rather than a solution. I'd rather keep a reasonable wait_timeout and refresh the connection in the code.

The error:

  File "C:\my_proj\db_conduit.py", line 147, in load_some_model
    SomeModel.objects.update()
  File "C:\Python26\lib\site-packages\django-1.3-py2.6.egg\django\db\models\manager.py", line 177, in update
    return self.get_query_set().update(*args, **kwargs)
  File "C:\Python26\lib\site-packages\django-1.3-py2.6.egg\django\db\models\query.py", line 469, in update
    transaction.commit(using=self.db)
  File "C:\Python26\lib\site-packages\django-1.3-py2.6.egg\django\db\transaction.py", line 142, in commit
    connection.commit()
  File "C:\Python26\lib\site-packages\django-1.3-py2.6.egg\django\db\backends\__init__.py", line 201, in commit
    self._commit()
  File "C:\Python26\lib\site-packages\django-1.3-py2.6.egg\django\db\backends\__init__.py", line 46, in _commit
    return self.connection.commit()
OperationalError: (2006, 'MySQL server has gone away')

Setup: Django 1.3.0 , MySQL 5.5.14 , innodb 1.1.8 , Python 2.6.6, Win7 64bit

Answer 1

The idea of the solution is clear: reconnect to mysql if the current connection is broken.

Please check this out:

def make_sure_mysql_usable():
    from django.db import connection, connections
    # mysql is lazily connected to in django.
    # connection.connection is None means
    # you have not connected to mysql before
    if connection.connection and not connection.is_usable():
        # destroy the default mysql connection
        # after this line, when you use ORM methods
        # django will reconnect to the default mysql
        del connections._connections.default

Answer 2

having the same issue. I need idea how to check connection state for MySQLdb connection in django. i guess it can be achieved by

try:
    cursor.execute(sql)
catch OperationalError:
    reconnect

is anybody have a better idea?

UPDATE

my decision

self.connection.stat()
if self.connection.errno()!=0:

check state of mysqldb connection if error recreate connection

UPDATE AGAIN

you also need to serve case if connection is close

if self.connection.open:
    self.connection.stat()

refresh connection is just recreating it

    db_settings = settings.DATABASES['mysql_db']
    try:
        self.connection = MySQLdb.connect(host=db_settings['HOST'],port=int(db_settings['PORT']),db=db_settings['NAME'],user=db_settings['USER'],passwd=db_settings['PASSWORD'])
    except MySQLdb.OperationalError, e:
        self.connection = None