Django数据库报错pymysql.err.OperationalError: (2013, ‘Lost connection to MySQL server during query’)

  • A+
所属分类:python语言

最近写了一个程序去处理一些数据,偶尔会报错丢失了mysql的连接,其实问题原因就是我们在和数据库建立连接后,操作了数据库的超时时间后又去操作数据库就会报这个错,

查看数据库超时时间:

show GLOBAL VARIABLES WHERE Variable_name = 'wait_timeout'

我这里是8个小时,是的,我的程序从数据库取到数据库后,执行了9个小时又需要回写数据库的

021719 0240 Django1 1 - Django数据库报错pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

   
 

知道问题,就好解决了

第一种方法(使用django配置)

首现设置数据库的会话保持时间CONN_MAX_AGE,最好必数据库的小点

021719 0240 Django2 1 - Django数据库报错pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

参考:https://docs.djangoproject.com/en/2.1/ref/settings/#std:setting-CONN_MAX_AGE

光设置了这个可能某些时候还是不够的,应为django释放使用的signals方式,

021719 0240 Django3 1 - Django数据库报错pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

   
 

如果你的应用是这个样的,解决方案是手工执行关闭旧的连接

from django.db import close_old_connections

然后在你执行完自己的操作后,手动关闭连接

   
 

第二种方法,简单粗暴

直接使用try,异常后从新在执行就OK

   
 

Traceback (most recent call last):
File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
return self.cursor.execute(sql, params)
File "/usr/local/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 71, in execute
return self.cursor.execute(query, args)
File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute
result = self._query(query)
File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query
conn.query(q)
File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 517, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 732, in _read_query_result
result.read()
File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 1075, in read
first_packet = self.connection._read_packet()
File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 657, in _read_packet
packet_header = self._read_bytes(4)
File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 707, in _read_bytes
CR.CR_SERVER_LOST, "Lost connection to MySQL server during query")
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

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

Traceback (most recent call last):
File "/var/www/operationalsystem/OPS11/unmap.py", line 297, in <module>
code = un.run()
File "/var/www/operationalsystem/OPS11/unmap.py", line 156, in run
self.update_database(into_database)
File "/var/www/operationalsystem/OPS11/unmap.py", line 201, in update_database
v_info = self.VolInfo.objects.get(vol_name=detail["vol"])
File "/usr/local/lib/python3.6/site-packages/django/db/models/manager.py", line 82, in manager_method
return getattr(self.get_queryset(), name)(*args, **kwargs)
File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 397, in get
num = len(clone)
File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 254, in __len__
self._fetch_all()
File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 1179, in _fetch_all
self._result_cache = list(self._iterable_class(self))
File "/usr/local/lib/python3.6/site-packages/django/db/models/query.py", line 53, in __iter__
results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
File "/usr/local/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1066, in execute_sql
cursor.execute(sql, params)
File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 100, in execute
return super().execute(sql, params)
File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 68, in execute
return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
return executor(sql, params, many, context)
File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
return self.cursor.execute(sql, params)
File "/usr/local/lib/python3.6/site-packages/django/db/utils.py", line 89, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 85, in _execute
return self.cursor.execute(sql, params)
File "/usr/local/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 71, in execute
return self.cursor.execute(query, args)
File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute
result = self._query(query)
File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query
conn.query(q)
File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 517, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 732, in _read_query_result
result.read()
File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 1075, in read
first_packet = self.connection._read_packet()
File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 657, in _read_packet
packet_header = self._read_bytes(4)
File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 707, in _read_bytes
CR.CR_SERVER_LOST, "Lost connection to MySQL server during query")
django.db.utils.OperationalError: (2013, 'Lost connection to MySQL server during query')

   
 

    

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: