最近项目中事情比较多,也遇到了一些问题,其中有一个是关于连接池的,比较有意思,这里分享下。
一天早上,进入业务系统,点击了一个功能按钮,页面上突然弹出个 MySQL gone away 的错误,我擦,数据库挂了吗,上服务器一看正常的。又点击了一下,又报事务未正常关闭的错误,有点懵。当然,是在测试环境上 :)。仔细想了想,发现是连接池的问题,下面我重现下这个错误并说下自己的一些解决办法。
注意:文中代码测试环境
-
Python 3.6.0
-
PyMySQL 0.9.2
-
SQLAlchemy 1.2.12
-
Flask 1.0.2
便于说明,假设应用程序代码如下
# coding=utf-8
# ====== SQLAlchemy ======
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Numeric
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
engine = create_engine('mysql+pymysql://username:password'
'@host:port/db_name?charset=utf8')
Session = sessionmaker(bind=engine)
session = Session()
class Book(Base):
__tablename__ = 'books'
id = Column(Integer, primary_key=True)
cat_id = Column(Integer)
name = Column('name', String(120))
price = Column('price', Numeric)
# ====== Flask ======
from flask import Flask
app = Flask(__name__)
@app.route('/')
def index():
book_num = session.query(Book).count()
return f'Book num: {book_num}'
if __name__ == '__main__':
app.run(port=8080, host='0.0.0.0', debug=True)
数据表 books 内容如下
+----+--------------------------+
| id | name |
+----+--------------------------+
| 1 | 生死疲劳 |
| 2 | 皮囊 |
| 3 | 半小时漫画中国史 |
| 4 | 耶路撒冷三千年 |
| 5 | 国家宝藏 |
| 6 | 时间简史 |
| 7 | 宇宙简史 |
| 8 | 自然史 |
| 9 | 人类简史 |
| 10 | 万物简史 |
+----+--------------------------+
应用中,我们使用 SQLAlchemy ORM 操作数据库,当 create_engine 使用默认参数的时候,连接池是打开着的。对大部分数据库来说,poolclass 默认为 QueuePool。当一个请求进来,SQLAlchemy 会创建一个数据库连接,执行结束后把连接放回池子里。下一个请求来的时候,就可以直接使用之前的连接。当然,如果同时进来多个不够分配的时候,会创建另外的连接用于使用,执行结束后又放回池子里。池子里的最大连接数是可以配置的。这种方式可以避免频繁创建、销毁连接,从而提高执行效率。
但是,这带来另一个问题。当数据库突然挂掉或者数据库过一定时间清理未活动连接的时候,SQLAlchemy 是不知道的。当一个请求进来时,会被分配一个失效的连接,自然会抛出一些异常。
备注:
MySQL 中使用如下命令查看未活动连接过期时间
show variables like "interactive_timeout";
结果类似这样
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+
单位是秒。
下面,我们重现下错误。
执行应用程序后,我们首先访问下这个链接
http://127.0.0.1:8080
此时,连接池里有一个连接了。
在继续下一步之前,我们先关闭或者重启下数据库,然后请求,抛出了无法连接数据库的错误
sqlalchemy.exc.OperationalError:
(pymysql.err.OperationalError)
(2013, 'Lost connection to MySQL server during query')
因为被分配的连接由于数据库的重启已经失效了嘛。
注意:
这里和我之前在公司测试环境上碰到的 MySQL gone away 的错误不一致,估计是由于示例应用与实际项目的结构、类库版本等方面的差异原因,不过错误的意思都差不多。
我们再执行第三次请求,发现又报了事务相关的错误,这是由于上一个错误导致连接未正常关闭引起的。
sqlalchemy.exc.StatementError:
(sqlalchemy.exc.InvalidRequestError)
Can't reconnect until invalid transaction is rolled back
碰到这种问题,我们该如何解决呢?究其原因,就是连接池的连接在不知情的情况下在数据库服务器上被关掉了。这个大致分为两种情况:
一、数据库异常或者误操作,比如数据库挂掉、重启、有的连接被误操作给 kill 掉了等等。这种情况下只好重启应用程序,让应用重新维护连接池;
二、连接长时间未活动。这又有两种情况:
1、create_engine 时指定的连接池中的连接的回收时间大于数据库配置的未活动连接过期时间,由于连接的回收时间一般都是设置的一两个小时,而数据库的未活动连接过期时间默认是八个小时,所以这种情况一般不会出现;
2、应用程序里的逻辑代码有问题,在进行数据库的写入操作后,缺少 commit、rollback、close 的操作将连接放回连接池,连接池没法管理,当这个连接被数据库回收后,也就出现了上面的异常。这种情况,有以下几个解决办法:
(1)查看相关的业务,补充缺失的连接维护操作(推荐);
(2)可以通过重启应用程序解决,不过指标不治本,运行一段时间后,问题依旧会出现;
(3)SQLAlchemy 开启 autocommit,不过这样就不能手动 rollback 了,在很多插入、更新场景中,不大实用;
(4)彻底一点的,直接不用连接池,在 create_engine 时指定参数 poolclass 为 NullPool。不过连接的使用效率就不如之前了,自己权衡。
最近项目中事情比较多,也遇到了一些问题,其中有一个是关于连接池的,比较有意思,这里分享下。一天早上,进入业务系统,点击了一个功能按钮,页面上突然弹出个 MySQL gone away 的错误,我擦,数据库挂了吗,上服务器一看正常的。又点击了一下,又报事务未正常关闭的错误,有点懵。当然,是在测试环境上 :)。仔细想了想,发现是连接池的问题,下面我重现下这个错误并说下自己的一些解决办法。注意:文中代码测试环境Python 3.6.0 PyMySQL 0.9.2 SQLAlchemy 1.2.
先看这张图,这是从官方网站扒下来的。
Engine 翻译过来就是引擎的意思,汽车通过引擎来驱动,而
SQLAlchemy
是通过 Engine 来驱动,Engine 维护了一个
连接池
(Pool)对象和方言(Dialect)。方言简单而言就是你连的到底是 MySQL 还是 Oracle 或者 PostgreSQL 还是其它数据库,关于方言(Dialect)的介绍在另外一篇文章有介绍,可参考数据库方言dialect。
连接池
很重要,因为每次发送sql查询的时候都需要先建立
连接
,如果程序启动的时候事先就初始化一批
连接
放在
连接池
,每次用完后又放回
连接池
给其它请求使用,就能大大提高查询的效率。
SQLAlchemy
是Python编程语言下的一款ORM框架,该框架建立在数据库API之上,使用关系对象映射进行数据库操作,简言之便是:将对象转换成SQL,然后使用数据API执行SQL并获取执行结果。最近在使用
SQLAlchemy
排序遇到了一个坑,所以想着总结下来,分享给更多的朋友,下面来一起看看吧。
query = db_session.query(UserVideo.vid,
UserVideo.uid,
UserVideo.v_width,
UserVideo.v_height,
UserVideo
这是一个简单的Postgres
连接池
后端,适用于Django 1.4+,由漂亮可爱
SQLAlchemy
提供支持。
使用Django-PostgresPool很简单,只需将django_postgrespool设置为您的
连接
引擎即可:
DATABASES = {
'default': {
'ENGINE': 'django_postgrespool'
如果您使用的是模块:
import dj_database_url
DATABASES = {'default': dj_database_url.config(engine='django_postgrespool')}
如果您使用的是 :
SOUTH_DATABASE_ADAPTERS = {
'default': 'south.db.postgresql
文章目录1. 错误内容:2. 报错原因:3. flask_
sqlalchemy
的变量:4. 解决办法:5. 更多参数配置:
1. 错误内容:
在使用flask_
sqlalchemy
的时候,每当长时间未请求,当再一次使用
连接
的时候,就会报
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')
2. 报错原因:
我们一般使用数据库
连接池
来获取
连接
,
连接池
里的
连接
可能会较长时间不关闭,等待被使用,这
SQLAlchemy
报错 Can’t reconnect until invalid transaction is rolled backs
进行
sqlalchemy
查询,系统报错:存在未回滚的事务
http://einverne.github.io/post/2017/05/
sqlalchemy
-session.html
正确用法是确保 session 在使用完成后用 session.close, session.commit 或者 session.rollback 把
连接
还回 pool。
一、数据库
问题
(
sqlalchemy
.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back
二、代码设计
from
sqlalchemy
.orm import sessionmaker
from
sqlalchemy
.ext.declarative import declarative_base
from
sqlalchemy
import create_engine
class DbMa
raise err.OperationalError(
sqlalchemy
.exc.OperationalError: (pymysql.err.OperationalError) (2006, "MySQL server has gone away (ConnectionAbortedError(10053, '你的主机
中
的软件
中
止了一个已建立的
连接
。', None, 10053, None))")
[SQL: SELECT admin_login_log.id AS admin_login_l...
import pymysql
conn = pymysql.Connect(host='192.168.255.255',user='laicheng',passwd='135246',db='test_sql')
cur = conn.cursor()
cur.execute("select version()")
for i in cur:
print(i)
cur.close()
https://www.jianshu.com/p/c0a8275cce99
Can’t reconnect until invalid transaction is rolled back
查询完以后没有将session放回
连接池
里,在开发过程
中
一定加入session.commit() / session.rollback() / session.close(),将当前会话完结。
This session is in ‘prepared‘ state; no
@ pymysql.err.OperationalError: (2013, ‘Lost connection to MySQL server during query’)
python 使用
sqlalchemy
链接MySQL断链
问题
(2013, ‘Lost connection to MySQL server during query’)
create_engine
中
添加参数:
pool_recycle=3600,
pool_pre_ping=True
SQLAlchemy
1.4 Docu
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')
pymysql.err.InterfaceError: (0, '')
错误原因:
MySQL持久化链接保持时间为8小时(28800秒),过期后断开连。如果数据库没有新建
连接
,则会报此错。
# 测试
连接
with engine.connect() as conn:
result = conn.execute("select 'Hello, Oracle!' from dual")
for row in result:
print(row)
在上面的代码
中
,`user`、`password`、`host`、`port`和`sid`分别表示Oracle数据库的用户名、密码、主机名、端口号和实例名。通过这些信息构造出DSN(Data Source Name),然后使用`create_engine()`函数创建一个引擎对象。使用`engine.connect()`方法可以创建一个数据库
连接
,然后就可以执行SQL语句了。