Django Lost connection to MySQL server during query

Question

I have a Django Admin application that periodically gives "2013, 'Lost connection to MySQL server during query'" or "2027, 'Malformed packet'" exception at various pages without any obvious reason, e.g. today the problem happens at /login page. My DB tables are relatively small, below 10k records. I noticed no correlation between the problem and database load, e.g. sometimes the problem happens at night when no one is using the database.

The issue closely resembles https://code.djangoproject.com/ticket/30911.

I read multiple Stack Overflow articles describing a similar issues, e.g. Lost connection to MySQL server during query and Lost connection to MySQL server during query?. Also read this: https://dev.mysql.com/doc/refman/5.7/en/error-lost-connection.html.

Based on multiple articles I read on the issue, I tried to adjust MySQL DB variables, current values are:

max_allowed_packet: 4194304
net_buffer_length: 16384
connect_timeout: 10
net_read_timeout: 30
wait_timeout: 28800

So far nothing helps, the problem just appears randomly and disappears by itself without me doing anything. I also noticed that it more often happens on the server while rarely happens on the localhost (both server and localhost connecting to the same database).

Any ideas would be very appreciated.

Today's traceback from localhost:

Environment:


Request Method: POST
Request URL: http://127.0.0.1:8000/login/?next=/

Django Version: 2.1.7
Python Version: 3.7.4
Installed Applications:
['adminapp.apps.AdminappConfig',
 'middleware.apps.MiddlewareConfig',
 'adminactions',
 'corsheaders',
 'django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.staticfiles',
 'storages',
 's3direct',
 'simple_history',
 'import_export',
 'rangefilter',
 'debug_toolbar',
 'tinymce']
Installed Middleware:
['corsheaders.middleware.CorsMiddleware',
 'debug_toolbar.middleware.DebugToolbarMiddleware',
 'django.middleware.security.SecurityMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware',
 'simple_history.middleware.HistoryRequestMiddleware']



Traceback:

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/db/backends/utils.py" in _execute
  85.                 return self.cursor.execute(sql, params)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/db/backends/mysql/base.py" in execute
  71.             return self.cursor.execute(query, args)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/MySQLdb/cursors.py" in execute
  209.         res = self._query(query)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/MySQLdb/cursors.py" in _query
  315.         db.query(q)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/MySQLdb/connections.py" in query
  226.         _mysql.connection.query(self, query)

The above exception ((2013, 'Lost connection to MySQL server during query')) was the direct cause of the following exception:

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/core/handlers/exception.py" in inner
  34.             response = get_response(request)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/core/handlers/base.py" in _get_response
  126.                 response = self.process_exception_by_middleware(e, request)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/core/handlers/base.py" in _get_response
  124.                 response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/views/decorators/cache.py" in _wrapped_view_func
  44.         response = view_func(request, *args, **kwargs)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/contrib/admin/sites.py" in the login
  398.         return LoginView.as_view(**defaults)(request)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/views/generic/base.py" in view
  68.             return self.dispatch(request, *args, **kwargs)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/utils/decorators.py" in _wrapper
  45.         return bound_method(*args, **kwargs)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/views/decorators/debug.py" in sensitive_post_parameters_wrapper
  76.             return view(request, *args, **kwargs)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/utils/decorators.py" in _wrapper
  45.         return bound_method(*args, **kwargs)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/utils/decorators.py" in _wrapped_view
  142.                     response = view_func(request, *args, **kwargs)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/utils/decorators.py" in _wrapper
  45.         return bound_method(*args, **kwargs)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/views/decorators/cache.py" in _wrapped_view_func
  44.         response = view_func(request, *args, **kwargs)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/contrib/auth/views.py" in dispatch
  61.         return super().dispatch(request, *args, **kwargs)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/views/generic/base.py" in dispatch
  88.         return handler(request, *args, **kwargs)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/views/generic/edit.py" in post
  141.         if form.is_valid():

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/forms/forms.py" in is_valid
  185.         return self.is_bound and not self.errors

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/forms/forms.py" in errors
  180.             self.full_clean()

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/forms/forms.py" in full_clean
  382.         self._clean_form()

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/forms/forms.py" in _clean_form
  409.             cleaned_data = self.clean()

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/contrib/auth/forms.py" in clean
  196.             self.user_cache = authenticate(self.request, username=username, password=password)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/contrib/auth/__init__.py" in authenticate
  73.             user = backend.authenticate(request, **credentials)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/contrib/auth/backends.py" in authenticate
  16.             user = UserModel._default_manager.get_by_natural_key(username)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/contrib/auth/base_user.py" in get_by_natural_key
  44.         return self.get(**{self.model.USERNAME_FIELD: username})

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/db/models/manager.py" in manager_method
  82.                 return getattr(self.get_queryset(), name)(*args, **kwargs)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/db/models/query.py" in getting
  393.         num = len(clone)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/db/models/query.py" in __len__
  250.         self._fetch_all()

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/db/models/query.py" in _fetch_all
  1186.             self._result_cache = list(self._iterable_class(self))

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/db/models/query.py" in __iter__
  54.         results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/db/models/sql/compiler.py" in execute_sql
  1065.             cursor.execute(sql, params)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/debug_toolbar/panels/sql/tracking.py" in execute
  192.         return self._record(self.cursor.execute, sql, params)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/debug_toolbar/panels/sql/tracking.py" in _record
  126.             return method(SQL, params)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/db/backends/utils.py" in execute
  100.             return super().execute(sql, params)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/db/backends/utils.py" in execute
  68.         return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/db/backends/utils.py" in _execute_with_wrappers
  77.         return executor(sql, params, many, context)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/db/backends/utils.py" in _execute
  85.                 return self.cursor.execute(sql, params)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/db/utils.py" in __exit__
  89.                 raise dj_exc_value.with_traceback(traceback) from exc_value

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/db/backends/utils.py" in _execute
  85.                 return self.cursor.execute(sql, params)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/django/db/backends/mysql/base.py" in execute
  71.             return self.cursor.execute(query, args)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/MySQLdb/cursors.py" in execute
  209.         res = self._query(query)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/MySQLdb/cursors.py" in _query
  315.         db.query(q)

File "/Users/natalya/dev/django-app/venv/lib/python3.7/site-packages/MySQLdb/connections.py" in query
  226.         _mysql.connection.query(self, query)

Exception Type: OperationalError at /login/
Exception Value: (2013, 'Lost connection to MySQL server during query')

sergodeeva · Answer 1 · 2020-01-10T06:09:39.277

Partially solved the problem by applying the following techniques:

1) Optimised a number of queries sent to the database by adding list_select_related: it saved me a number of DB queries (e.g. on some list views was able to reduce a number of queries from 40+ to 6-7).

Refer to Django documentation: https://docs.djangoproject.com/en/3.0/ref/contrib/admin/#django.contrib.admin.ModelAdmin.list_select_related

2) Added the following options to DB settings in settings.py:

'OPTIONS': {
  'init_command': "SET sql_mode='STRICT_TRANS_TABLES', innodb_strict_mode=1;",
}

Refer to https://django-mysql.readthedocs.io/en/latest/checks.html#django-mysql-w002-innodb-strict-mode

3) Restarting database server helps for short time.

Above solutions helped to reduce the frequency of issue, but did not solve it completely. It is still appearing totally randomly, and I still have no clue what is causing it.

score 0 · Answer 2 · answered Jun 12 '21 at 14:15

What's the version of MySQL-client on your local and server?

Usually, a Malformed Packet error occurs when the MySQL client can't understand the packets sent by the MySQL server.

Try disabling query-caching on MySQL database it should work. There is a bug reported in MySQL with MySQL-client and MySQL-server versions reported here.

In your case, because you are hitting the same MySQL Server (DB) from two different client machines (local and server), so, with query-caching enabled, whenever you run the same query with both of the client machines, you will get this error.

Both of these solutions should work for you:-

Disabling the query-caching.
By installing the same MySQL-client version on both of the client machines (now it'll work with query-caching enabled as well).

Django Lost connection to MySQL server during query

2 Answers2