mysql - Django 在查询期间失去了与 MySQL 服务器的连接

Question

我有一个 Django 管理应用程序，它定期在各个页面上给出“2013，‘查询期间与 MySQL 服务器失去连接’”或“2027，‘格式错误的数据包’”异常，没有任何明显的原因，例如今天问题发生在/login 页面。我的数据库表相对较小，不到 10k 条记录。我注意到问题和数据库负载之间没有相关性，例如有时问题会发生在晚上没有人使用数据库的时候。

该问题与 https://code.djangoproject.com/ticket/30911 非常相似.

我阅读了多篇描述类似问题的 Stack Overflow 文章，例如Lost connection to MySQL server during query和 Lost connection to MySQL server during query? 。另请阅读:https://dev.mysql.com/doc/refman/5.7/en/error-lost-connection.html .

根据我阅读的有关该问题的多篇文章，我尝试调整 MySQL DB 变量，当前值为:

• 最大允许数据包:4194304
• 网络缓冲区长度:16384
• 连接超时:10
• net_read_timeout:30
• 等待超时:28800

到目前为止，没有任何帮助，问题只是随机出现并自行消失，无需我采取任何措施。我还注意到它更经常发生在服务器上，而很少发生在本地主机上(服务器和本地主机都连接到同一数据库)。

任何想法将不胜感激。

今天来自本地主机的回溯:

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')

Answer 1

通过应用以下技术部分解决了该问题:

1) 通过添加 list_select_lated 优化了发送到数据库的大量查询:它节省了我大量的数据库查询(例如，在某些 ListView 上能够将查询数量从 40 多个减少到至 6-7)。

请参阅 Django 文档:https://docs.djangoproject.com/en/3.0/ref/contrib/admin/#django.contrib.admin.ModelAdmin.list_select_related

2)在settings.py中的数据库设置中添加了以下选项:

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

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

3) 重新启动数据库服务器会在短时间内有所帮助。

上述解决方案有助于降低问题发生的频率，但并不能彻底解决问题。它仍然完全随机出现，我仍然不知道是什么原因造成的。