Random Error "Lost connection to MySQL server during query" with Django 1.3

Question

This thread doesn't quite help: Lost connection to MySQL server during query.

I increased the max_allowed_packet to be 16M and max_connections to be 9999. However, I still get this error:

  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/db/models/query.py", line 107, in _result_iter
    self._fill_cache()
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/db/models/query.py", line 772, in _fill_cache
    self._result_cache.append(self._iter.next())
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/db/models/query.py", line 273, in iterator
    for row in compiler.results_iter():
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 680, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 735, in execute_sql
    cursor.execute(sql, params)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/django/db/backends/mysql/base.py", line 86, in execute
    return self.cursor.execute(query, args)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.6-intel.egg/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.6-intel.egg/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (2013, 'Lost connection to MySQL server during query')

It happened kinda randomly, can anyone shed some light on it?

I really dislike mysql these days, if you have the option now, consider using postgresql. — Josh Smeaton, Mar 27 '11 at 04:26
Have you ran mysqlcheck on all of the tables? Are you connecting to a remote or local MySQL server? Can you reproduce the error? — Micah Carrick, Mar 29 '11 at 17:51

score 5 · Answer 1 · edited May 23 '17 at 12:26

Some possibilities (gleaned in part from https://serverfault.com/questions/29597/what-does-mysql-error-2013-mean/30036#30036):

Due to load conditions the query is taking a longer than normal (thus random) and is exceeding the server's wait_timeout (unlikely) or net_wait_timeout (likely).
You are using a sql proxy, and that proxy is killing connections before they're done.
Your network connection to the mysql server is flaky. Try pinging it for a while to see if you have any packet loss.

Another possibility is that the query itself is very expensive due to lack of indexes, a huge dataset or limitations in the table engine you are using (probably either InnoDB or MyISAM). A good way to determine this is to find the QuerySet that is causing the issue, getting the raw sql from the QuerySet (see Getting the SQL from a Django QuerySet) and then running the query with EXPLAIN prepended in your mysql shell of choice.

I checked the checkboxes on the timeout settings in MySQL Workbench. It seems MySQL was not using the default value until I checked on checkboxes. Are those values grayed out in the text boxes the recommended values rather than the default value? — zs2020, Mar 31 '11 at 13:35
Some Cloud providers like Azure have proxies that kills idle connections at certain fixed time. Try using a connection pooling library with recycle property (like SQLAlchemy) with recycle timing lower than the proxy timeout killing. In case of azure, 240s. — danius, Oct 02 '14 at 10:32

score 3 · Answer 2 · answered Mar 21 '13 at 22:00

3

I had the exact same error on a multi-threaded python script. The problem as passing the connection id as a parameter for the threads.

This went away when I started creating the mysql connections within the the threads themselves.

answered Mar 21 '13 at 22:00

Morris

169
8

score 0 · Answer 3 · answered Mar 30 '11 at 17:44

I see 2 possible reasons (if mysql server is on the same server and no network problems):

too much time has passed since last mysql query, so the server dropped the connection
you lost mysql resourse id of the connection (overwritten the id variable)

if you don't query for long time the server it's good to ping the server from time to time using mysql_ping().

MySQL C api has this option MYSQL_OPT_RECONNECT and it's set using this function:

int mysql_options(MYSQL *mysql, enum mysql_option option, const char *arg)

I don't know if it's available for python.

If frequent pinging doesn't help it's possible that you've overwritten the mysql link id.

Also in case the mysql server is not on the same server you should implement "my_mysql_query()" function that checks if mysql_errno() is CR_SERVER_GONE_ERROR or CR_SERVER_LOST then reconnect and retry query.

score -4 · Accepted Answer · answered Apr 19 '11 at 18:16

-4

The solution is do sleep between the adjacent database calls.

answered Apr 19 '11 at 18:16

zs2020

53,766
29
154
219

Random Error "Lost connection to MySQL server during query" with Django 1.3

4 Answers4