Django MySQL server has gone away

Question

I have a django site the was using sqlite for the backend, recently we upgraded to MySQL and now are receiving intermittent errors:

'MySQL server has gone away'

After starting the site it loads fine, clicking around to change pages will result in the error usually after viewing 1 to 6 pages. The page it occurs on appears to be irrelevant, the same page may load fine the first time but throw the error the 2nd time.

Here's my environment:

• Nginx running on host machine as reverse proxy
• Docker container running Nginx inside, Django 1.8, uwsgi, and Python 3.4.
• Django is using the mysqlclient db driver.
• Google Cloud MySQL, 2nd generation

I've tried using a local MySQL server rather than the Google Cloud MySQL, it didn't make a difference. I also tried using the MySQL Connector/Python DB driver instead of mysqlclient. It produced a different (but similar) error message and traceback.

If I go back to SQLite, it works fine. Running under the django development server rather than nginx also works fine.

I've seen posts for this error stating that the django CONN_MAX_AGE should be less than the MySQL wait_timeout, but I'm using the default CONN_MAX_AGE setting of 0. According to the django docs, setting this to 0 (default) causes django to create a new database connection for each request. A value greater than 0 will define how long the connection remains open so it can be reused by another request. If I set it to a value greater than 0 the error goes away, but I'm concerned that I'm just deferring the error until later after the persistent connection expires. Also the django development web server creates a new connection for each request (similar to CONN_MAX_AGE=0), but I don't get the error using the dev server. Django versions prior to 1.6 did not support persistent connections, so it seems like I should be able to keep the default CONN_MAX_AGE=0 setting.

I had to enable logging for uwsgi, it's error log shows the following:

*** Starting uWSGI 2.0.13.1 (64bit) on [Fri Nov 18 22:15:33 2016] ***
compiled with version: 4.8.4 on 18 May 2016 17:48:05
os: Linux-4.4.0-45-generic #66-Ubuntu SMP Wed Oct 19 14:12:37 UTC 2016
nodename: 083c33814e43
machine: x86_64
clock source: unix
detected number of CPU cores: 8
current working directory: /
detected binary path: /usr/local/bin/uwsgi
!!! no internal routing support, rebuild with pcre support !!!
uWSGI running as root, you can use --uid/--gid/--chroot options
*** WARNING: you are running uWSGI as root !!! (use the --uid flag) ***
chdir() to /srv/ftc
your memory page size is 4096 bytes
detected max file descriptor number: 65536
lock engine: pthread robust mutexes
thunder lock: disabled (you can enable it with --thunder-lock)
uwsgi socket 0 bound to UNIX address /srv/ftc/ftc.sock fd 3
Python version: 3.4.3 (default, Oct 14 2015, 20:31:36)  [GCC 4.8.4]
Set PythonHome to /srv/env/ftc
*** Python threads support is disabled. You can enable it with --enable-threads ***
Python main interpreter initialized at 0x22a73d0
your server socket listen backlog is limited to 100 connections
your mercy for graceful operations on workers is 60 seconds
mapped 1476189 bytes (1441 KB) for 10 cores
*** Operational MODE: preforking ***
Loading configuration from /srv/ftc/data/settings_local.py
WSGI app 0 (mountpoint='') ready in 2 seconds on interpreter 0x22a73d0 pid: 9 (default app)
*** uWSGI is running in multiple interpreter mode ***
spawned uWSGI master process (pid: 9)
spawned uWSGI worker 1 (pid: 17, cores: 1)
spawned uWSGI worker 2 (pid: 18, cores: 1)
spawned uWSGI worker 3 (pid: 19, cores: 1)
spawned uWSGI worker 4 (pid: 20, cores: 1)
spawned uWSGI worker 5 (pid: 21, cores: 1)
spawned uWSGI worker 6 (pid: 22, cores: 1)
spawned uWSGI worker 7 (pid: 23, cores: 1)
spawned uWSGI worker 8 (pid: 24, cores: 1)
spawned uWSGI worker 9 (pid: 25, cores: 1)
spawned uWSGI worker 10 (pid: 26, cores: 1)
[pid: 21|app: 0|req: 1/1] 192.168.1.15 () {38 vars in 637 bytes} [Fri Nov 18 17:15:39 2016] GET /faq/ => generated 13018 bytes in 3021 msecs (HTTP/1.1 200) 8 headers in 412 bytes (1 switches on core 0)
[pid: 21|app: 0|req: 2/2] 192.168.1.15 () {40 vars in 707 bytes} [Fri Nov 18 17:16:20 2016] GET /find-local-food/ => generated 20480 bytes in 1510 msecs (HTTP/1.1 200) 7 headers in 290 bytes (2 switches on core 0)
[pid: 21|app: 0|req: 3/3] 192.168.1.15 () {40 vars in 711 bytes} [Fri Nov 18 17:16:23 2016] GET /my-programs/ => generated 9297 bytes in 1454 msecs (HTTP/1.1 200) 7 headers in 290 bytes (2 switches on core 0)
[pid: 21|app: 0|req: 4/4] 192.168.1.15 () {40 vars in 683 bytes} [Fri Nov 18 17:16:26 2016] GET / => generated 13562 bytes in 3037 msecs (HTTP/1.1 200) 8 headers in 412 bytes (2 switches on core 0)
[pid: 21|app: 0|req: 5/5] 192.168.1.15 () {40 vars in 683 bytes} [Fri Nov 18 17:16:30 2016] GET /about/ => generated 10064 bytes in 1586 msecs (HTTP/1.1 200) 7 headers in 290 bytes (2 switches on core 0)
[pid: 21|app: 0|req: 6/6] 192.168.1.15 () {40 vars in 685 bytes} [Fri Nov 18 17:16:33 2016] GET /faq/ => generated 13018 bytes in 1145 msecs (HTTP/1.1 200) 8 headers in 412 bytes (2 switches on core 0)
[pid: 21|app: 0|req: 7/7] 192.168.1.15 () {40 vars in 691 bytes} [Fri Nov 18 17:16:35 2016] GET /contact/ => generated 8734 bytes in 1352 msecs (HTTP/1.1 200) 7 headers in 290 bytes (2 switches on core 0)
[pid: 21|app: 0|req: 8/8] 192.168.1.15 () {40 vars in 711 bytes} [Fri Nov 18 17:16:38 2016] GET /find-local-food/ => generated 20480 bytes in 1484 msecs (HTTP/1.1 200) 7 headers in 290 bytes (2 switches on core 0)
[pid: 21|app: 0|req: 9/9] 192.168.1.15 () {40 vars in 699 bytes} [Fri Nov 18 17:16:41 2016] GET /about/ => generated 10064 bytes in 1723 msecs (HTTP/1.1 200) 7 headers in 290 bytes (2 switches on core 0)
Traceback (most recent call last):
  File "/srv/env/ftc/lib/python3.4/site-packages/django/core/urlresolvers.py", line 393, in urlconf_module
    return self._urlconf_module
AttributeError: 'RegexURLResolver' object has no attribute '_urlconf_module'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/srv/env/ftc/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/srv/env/ftc/lib/python3.4/site-packages/django/db/backends/mysql/base.py", line 124, in execute
    return self.cursor.execute(query, args)
  File "/srv/env/ftc/lib/python3.4/site-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/srv/env/ftc/lib/python3.4/site-packages/MySQLdb/connections.py", line 42, in defaulterrorhandler
    raise errorvalue
  File "/srv/env/ftc/lib/python3.4/site-packages/MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "/srv/env/ftc/lib/python3.4/site-packages/MySQLdb/cursors.py", line 411, in _query
    rowcount = self._do_query(q)
  File "/srv/env/ftc/lib/python3.4/site-packages/MySQLdb/cursors.py", line 374, in _do_query
    db.query(q)
  File "/srv/env/ftc/lib/python3.4/site-packages/MySQLdb/connections.py", line 270, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/srv/env/ftc/lib/python3.4/site-packages/django/core/handlers/wsgi.py", line 170, in __call__
    self.load_middleware()
  File "/srv/env/ftc/lib/python3.4/site-packages/django/core/handlers/base.py", line 52, in load_middleware
    mw_instance = mw_class()
  File "/srv/env/ftc/lib/python3.4/site-packages/django/middleware/locale.py", line 24, in __init__
    for url_pattern in get_resolver(None).url_patterns:
  File "/srv/env/ftc/lib/python3.4/site-packages/django/core/urlresolvers.py", line 401, in url_patterns
    patterns = getattr(self.urlconf_module, "urlpatterns", self.urlconf_module)
  File "/srv/env/ftc/lib/python3.4/site-packages/django/core/urlresolvers.py", line 395, in urlconf_module
    self._urlconf_module = import_module(self.urlconf_name)
  File "/srv/env/ftc/lib/python3.4/importlib/__init__.py", line 109, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 2254, in _gcd_import
  File "<frozen importlib._bootstrap>", line 2237, in _find_and_load
  File "<frozen importlib._bootstrap>", line 2226, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 1200, in _load_unlocked
  File "<frozen importlib._bootstrap>", line 1129, in _exec
  File "<frozen importlib._bootstrap>", line 1471, in exec_module
  File "<frozen importlib._bootstrap>", line 321, in _call_with_frames_removed
  File "./ftc/urls.py", line 23, in <module>
    url(r'^', include('areas.public.urls')),
  File "/srv/env/ftc/lib/python3.4/site-packages/django/conf/urls/__init__.py", line 33, in include
    urlconf_module = import_module(urlconf_module)
  File "/srv/env/ftc/lib/python3.4/importlib/__init__.py", line 109, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 2254, in _gcd_import
  File "<frozen importlib._bootstrap>", line 2237, in _find_and_load
  File "<frozen importlib._bootstrap>", line 2226, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 1200, in _load_unlocked
  File "<frozen importlib._bootstrap>", line 1129, in _exec
  File "<frozen importlib._bootstrap>", line 1471, in exec_module
  File "<frozen importlib._bootstrap>", line 321, in _call_with_frames_removed
  File "./areas/public/urls.py", line 36, in <module>
    url(r'^paypal/', include('areas.public.paypal.urls')),
  File "/srv/env/ftc/lib/python3.4/site-packages/django/conf/urls/__init__.py", line 33, in include
    urlconf_module = import_module(urlconf_module)
  File "/srv/env/ftc/lib/python3.4/importlib/__init__.py", line 109, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 2254, in _gcd_import
  File "<frozen importlib._bootstrap>", line 2237, in _find_and_load
  File "<frozen importlib._bootstrap>", line 2226, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 1200, in _load_unlocked
  File "<frozen importlib._bootstrap>", line 1129, in _exec
  File "<frozen importlib._bootstrap>", line 1471, in exec_module
  File "<frozen importlib._bootstrap>", line 321, in _call_with_frames_removed
  File "./areas/public/paypal/urls.py", line 5, in <module>
    from . import payment_complete
  File "./areas/public/paypal/payment_complete.py", line 9, in <module>
    PublicCommon = PublicCommon()
  File "./areas/public/common/__init__.py", line 34, in __init__
    self.login_page = self.get_cms_page('pu_login')
  File "./areas/public/common/__init__.py", line 27, in get_cms_page
    return get_object_or_404(Page, reverse_id=reverse_id, publisher_is_draft=False)
  File "/srv/env/ftc/lib/python3.4/site-packages/django/shortcuts.py", line 155, in get_object_or_404
    return queryset.get(*args, **kwargs)
  File "/srv/env/ftc/lib/python3.4/site-packages/django/db/models/query.py", line 328, in get
    num = len(clone)
  File "/srv/env/ftc/lib/python3.4/site-packages/django/db/models/query.py", line 144, in __len__
    self._fetch_all()
  File "/srv/env/ftc/lib/python3.4/site-packages/django/db/models/query.py", line 965, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/srv/env/ftc/lib/python3.4/site-packages/django/db/models/query.py", line 238, in iterator
    results = compiler.execute_sql()
  File "/srv/env/ftc/lib/python3.4/site-packages/django/db/models/sql/compiler.py", line 840, in execute_sql
    cursor.execute(sql, params)
  File "/srv/env/ftc/lib/python3.4/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/srv/env/ftc/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/srv/env/ftc/lib/python3.4/site-packages/django/db/utils.py", line 98, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/srv/env/ftc/lib/python3.4/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/srv/env/ftc/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/srv/env/ftc/lib/python3.4/site-packages/django/db/backends/mysql/base.py", line 124, in execute
    return self.cursor.execute(query, args)
  File "/srv/env/ftc/lib/python3.4/site-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/srv/env/ftc/lib/python3.4/site-packages/MySQLdb/connections.py", line 42, in defaulterrorhandler
    raise errorvalue
  File "/srv/env/ftc/lib/python3.4/site-packages/MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "/srv/env/ftc/lib/python3.4/site-packages/MySQLdb/cursors.py", line 411, in _query
    rowcount = self._do_query(q)
  File "/srv/env/ftc/lib/python3.4/site-packages/MySQLdb/cursors.py", line 374, in _do_query
    db.query(q)
  File "/srv/env/ftc/lib/python3.4/site-packages/MySQLdb/connections.py", line 270, in query
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: (2006, 'MySQL server has gone away')
[pid: 20|app: 0|req: 1/10] 192.168.1.15 () {40 vars in 701 bytes} [Fri Nov 18 17:16:43 2016] GET /my-programs/ => generated 0 bytes in 258 msecs (HTTP/1.1 500) 0 headers in 0 bytes (1 switches on core 0)
...brutally killing workers...
worker 1 buried after 1 seconds
worker 2 buried after 1 seconds
worker 3 buried after 1 seconds
worker 4 buried after 1 seconds
worker 5 buried after 1 seconds
worker 6 buried after 1 seconds
worker 7 buried after 1 seconds
worker 8 buried after 1 seconds
worker 9 buried after 1 seconds
worker 10 buried after 1 seconds
binary reloading uWSGI...
chdir() to /
closing all non-uwsgi socket fds > 2 (max_fd = 65536)...
found fd 3 mapped to socket 0 (/srv/ftc/ftc.sock)
running /usr/local/bin/uwsgi
[uWSGI] getting INI configuration from /srv/ftc/conf/uwsgi.ini

Thanks in advance for any help!

Answer 1

For Django >= 4.1 you can enable persistent connections with health-checks

DATABASES = {
   'default': {
       'CONN_HEALTH_CHECKS': True,
       'CONN_MAX_AGE': 60, 
   }
}

Answer 2

I found the solution. I added lazy-apps to uwsgi.ini. See ningx, uwsgi, python permanent mysql error after some time from starting application

I'm not using sql alchemy but this did fix the mysql server has gone away error.

Answer 3

This is usually a mysql configuration problem. The settings in particular that you want to adjust for your website are:

• max_allowed_packet: http://dev.mysql.com/doc/refman/5.7/en/packet-too-large.html
• wait_timeout: http://dev.mysql.com/doc/refman/5.7/en/gone-away.html

But if you’ll look at your log, the mysql gone away problem is only during the handling of an exception that is being caused somewhere else. I would try and solve this exception first before trying to tackle the mysql issue

Traceback (most recent call last):
  File "/srv/env/ftc/lib/python3.4/site-packages/django/core/urlresolvers.py", line 393, in urlconf_module
    return self._urlconf_module
AttributeError: 'RegexURLResolver' object has no attribute '_urlconf_module'