遇到问题
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')
建立的 pymysql 的连接
# mysql connect engine = create_engine("mysql+{driver}://{username}:{password}@{server}/{database}?charset={charset}" .format(driver=MYSQL_DRIVER, username=MYSQL_USERNAME, password=MYSQL_PASSWORD, server=MYSQL_SERVER, database=DB_NAME, charset=DB_CHARSET), pool_size=100, max_overflow=100, # pool_recycle=7200, pool_recycle=10, echo=False) engine.execute("SET NAMES {charset};".format(charset=DB_CHARSET)) MapBase = declarative_base(bind=engine) DBSession = sessionmaker(bind=engine)
运行具体报错
Connected to pydev debugger (build 191.6183.50) init mysql_db success ai access running... {'recordId': 'e2d432da51214c54aa2bb4d43b513835', 'carImg1Data': '/9j/4AAQSkZJRgABAQAAAQABAAD/2wBDAAI {'recordId': 'e2d432da51214c54aa2bb4d43b513835', 'carImg1Data': '/9j/4AAQSkZJRgABAQAAAQABAAD/2wBDAAI exception occurs when get data! Traceback (most recent call last): File "/home/ubuntu/.virtualenvs/access_py3/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context cursor, statement, parameters, context File "/home/ubuntu/.virtualenvs/access_py3/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute cursor.execute(statement, parameters) File "/home/ubuntu/Desktop/data_access_py3/camera-coll-ai/packages/pymysql/cursors.py", line 170, in execute result = self._query(query) File "/home/ubuntu/Desktop/data_access_py3/camera-coll-ai/packages/pymysql/cursors.py", line 328, in _query conn.query(q) File "/home/ubuntu/Desktop/data_access_py3/camera-coll-ai/packages/pymysql/connections.py", line 517, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "/home/ubuntu/Desktop/data_access_py3/camera-coll-ai/packages/pymysql/connections.py", line 732, in _read_query_result result.read() File "/home/ubuntu/Desktop/data_access_py3/camera-coll-ai/packages/pymysql/connections.py", line 1075, in read first_packet = self.connection._read_packet() File "/home/ubuntu/Desktop/data_access_py3/camera-coll-ai/packages/pymysql/connections.py", line 657, in _read_packet packet_header = self._read_bytes(4) File "/home/ubuntu/Desktop/data_access_py3/camera-coll-ai/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')
分析
配置了 pool_recycle = 10，导致池在给定的秒数过后再循环连接，连接池 100 连接不够用了，导致上述错误
解决方案
配置 pool_recycle = 1，或者自动回收（设置为 True 是可以的，貌似与设置为 1 一样），而默认等于 -1，表示不回收
实践例子
设置 pool_recycle=3，然后设置 wait_timeout = 2，在程序执行了第二次的过程中，就会触发异常
import time from sqlalchemy.engine import create_engine url = 'mysql+pymysql://user:pass@127.0.0.1:3306/db' engine = create_engine(url, pool_recycle=3).connect() query = 'SELECT NOW();' while True: print('Q1', engine.execute(query).fetchall()) # time.sleep(2) print('Q2', engine.execute(query).fetchall())
设置 wait_timeout = 2
mysql> set global wait_timeout = 2; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like '%timeout%'; +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | have_statement_timeout | YES | | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | rpl_stop_slave_timeout | 31536000 | | slave_net_timeout | 60 | | wait_timeout | 2 | +-----------------------------+----------+ 13 rows in set (0.04 sec)
好了，更多 create_engine() 函数的连接参数说明，可以去官网查看，以便更好的理解