mysql 断开连接解决方法 (2013, 'Lost connection to MySQL server during query') (mysql have gone away)
1,出现该问题的原因,代码里导入了mysql的一个连接,但是长时间没有操作,超过了mysql的awiat_timeout 时间(默认8小时)
2,解决思路:
1,修改mysql 的await_timeout (不建议)
2,django 中使用 close_old_connections() 关闭旧的连接 (from django.db import close_old_connections)
(该方法一般在django 的脚本中使用)
from django.db import close_old_connections def index(): close_old_connections() s = Student.objects.all()
3,django 的视图函数中使用可以定义中间件 (请求开始时连接,请求结束后断开) (使用的可能性不大)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
# middleware.py from my_blog.db import database # Import the peewee database instance. def PeeweeConnectionMiddleware(get_response): def middleware(request): database.connect() try: response = get_response(request) finally: if not database.is_closed(): database.close() return response return middleware
4,flask 中解决思路:和django中一样,在请求来时连接,在请求结束后断开
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
# app.py from bottle import hook #, route, etc, etc. from peewee import * db = SqliteDatabase('my-bottle-app.db') @hook('before_request') def _connect_db(): db.connect() @hook('after_request') def _close_db(): if not db.is_closed(): db.close() # Rest of your bottle app goes here.
5,如果是使用peewee ,在模型类连接时修改继承类,后面正常使用就ok (可能性比较大)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
from peewee import * from peewee import __exception_wrapper__ class RetryOperationalError(object): def execute_sql(self, sql, params=None, commit=True): try: cursor = super(RetryOperationalError, self).execute_sql(sql, params, commit) except OperationalError: if not self.is_closed(): self.close() with __exception_wrapper__: cursor = self.cursor() cursor.execute(sql, params or ()) if commit and not self.in_transaction(): self.commit() return cursor class RetryMySQLDatabase(RetryOperationalError, MySQLDatabase): pass database = RetryMySQLDatabase('sql_test', **{'charset': 'utf8', 'sql_mode': 'PIPES_AS_CONCAT', 'use_unicode': True, 'host': '127.0.0.1', 'port': 3306, 'user': 'root', 'password': 'mysql'}) class UnknownField(object): def __init__(self, *_, **__): pass class BaseModel(Model): class Meta: database = database class Teacher(BaseModel): depart = CharField() prof = CharField(null=True) tbirthday = DateTimeField(null=True) tname = CharField() tno = CharField(primary_key=True) tsex = CharField() class Meta: table_name = 'teacher' class Course(BaseModel): cname = CharField() cno = CharField() tno = ForeignKeyField(column_name='tno', field='tno', model=Teacher) class Meta: table_name = 'course' class DjangoMigrations(BaseModel): app = CharField() applied = DateTimeField() name = CharField() class Meta: table_name = 'django_migrations' class Emp(BaseModel): deptno = CharField(null=True) duty = CharField(null=True) empid = AutoField() name = CharField(null=True) sal = CharField(null=True) class Meta: table_name = 'emp'
6,tornado 解决方法如下
from tornado.web import RequestHandler db = SqliteDatabase('my_db.db') class PeeweeRequestHandler(RequestHandler): def prepare(self): db.connect() return super(PeeweeRequestHandler, self).prepare() def on_finish(self): if not db.is_closed(): db.close() return super(PeeweeRequestHandler, self).on_finish()