python db end-of-file 记录

1. 异常信息

sqlalchemy.exc.OperationalError: (cx_Oracle.OperationalError) ORA-03113: end-of-file on communication channel

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-03150: end-of-file on communication channel for database link

2. 涉及的 module

Flask-SQLAlchemy     2.4.4

SQLAlchemy           1.3.22

3. 猜测

猜测 1： 怀疑连接池没起作用，即每次 db 操作都会创建一个新的连接，而我们项目中连接 db 又比较多

       事先声明一下项目之前是正常的，没有遇到过 end-of-file 问题，且之前连接 db 的方式是在 config.py 中将变量通过 env.get ('xxx')

       写入 ProdConfig (Config) 类（该类继承自 Config (Object)）中，这里变量是通过一个.cfg 的配置文件集中配置的。

       大致加载过程如下：

       由于项目集成了 gunicorn 服务，部署到 linux 上是以脚本方式启动，即 run.sh 包含下面部分内容：

           basedir=$(dirname $0)

	       app_home=$(cd $basedir && pwd -L) || exit 1

	       CONFIG_FILE=${app_home}/app.cfg

	       [[ -e ${CONFIG_FILE} ]] && {

    	           echo "Export config file."

    	           source $CONFIG_FILE || exit 1

	       }

       app.cfg 中 配置 sqlalchemy 连接参数： 

	       export SQLALCHEMY_DATABASE_URI=oracle://user:psw@1.1.1.1:2222/?service_name=xx.xx.com

       ProdConfig 类中执行：

	       from os import environ as env

	       SQLALCHEMY_DATABASE_URI = env.get ('SQLALCHEMY_DATABASE_URI')

       flask 项目入口 create_app 方法内引入：

	       db = SQLAlchemy ()   # 这里 db 是用于 orm 即 sqlalchemy 以 models 方式查询

           engine = create_engine (ProdConfig.SQLALCHEMY_DATABASE_URI)  # 这里是直接使用 engine 以人为写 sql 方式查询

	       def create_app (config_file)

	           app = Flask (__name__)

	           app.config.from_object (ProdConfig)

       dao 层直接引入 db 或者 engine 执行 db 操作（引入 db 和引入 engine 是两种不同的方式，区别是 db 走的是 orm）

	       from dashboard import db

	       User.query.get (username)  # User 继承 db.Model

	       from dashboard import engine

	       df = pd.read_sql_query ('select * from USER', engine)  # pandas db 查询

       SQLALCHEMY_DATABASE_URI 是 Flask-SQLAlchemy 约定的一个配置参数，具体可以去这个网站查看文档

       https://flask-sqlalchemy.palletsprojects.com/en/2.x/

       该插件还提供了一些其他连接参数，比如：SQLALCHEMY_POOL_SIZE（注意很多在 2.4 版本都是 Deprecated 的，在 2.4 新增

       SQLALCHEMY_ENGINE_OPTIONS，很多之前的配置参数移动到这里了）

       上面这些都是之前的正常情况下的配置，后面因为项目里集成了 xxl-job，xxl-job 是以脚本方式执行任务，而 python

       脚本执行是和 flask web app context 是隔离的，即脚本访问不到 web 容器内的变量，而我们又想在两边统一获取变量

       入口，方便管理，这导致很多配置变量在 web 端通过 env.get 可以获取到，但是在 xxl-job 端就无法获取到。最后我们采

       取了修改变量获取方式，即不在使用.cfg 文件存储运行时变量，而是新建了 app.ini 文件，将变量迁移到 ini 后缀的文

       件下面。当然，改过后对于 web 的特有的变量还是依然走 ProdConfig 的，即仅对于两边都需要的变量做了统一迁移。

       下面是变更配置后的改动

       config.py 文件中新增 NewConfig 类，直接继承 Object，

       将 SQLALCHEMY_DATABASE_URI 移动到 NewConfig，当然 ProdConfig 内之前的也是保留的。

           class NewConfig (object):

	       config = configparser.ConfigParser () if six.PY2 else configparser.RawConfigParser ()

    	   config.read (basedir + '/app.ini')

	       SQLALCHEMY_DATABASE_URI = config.get ('APP', 'SQLALCHEMY_DATABASE_URI')

       flask 项目入口 create_app 方法内将

           engine = create_engine (ProdConfig.SQLALCHEMY_DATABASE_URI)

       改为

           engine = create_engine (NewConfig.SQLALCHEMY_DATABASE_URI)

       其他不变，但是事实证明上述改动后依然出现 EOF 问题

猜测 2： oracle db 之前有做 cutover，即升级为 19c 版本，不过在升级前就已经出现了 end-of-file，并且升级后

       其他 team 连接 db 没有出现 end-of-file，所以这个和 db 升级有关的猜测应该可以排除，属于我们自身问题

猜测 3： vm 网络问题，如果是网络问题的话，我们的 app 应该在某个时段或者所有时段一直出现该问题，然后事实是

       大部分情况下 db 操作都是正常无误的，仅偶尔会出现几次 end-of-file，所以网络问题先暂时排除，这个可以使用

       tcpdump 抓包测试 - 未尝试

猜测 4： 防火墙切断数据库空闲连接

       起初不知道有这回事，这个是从 github 上提了一个 discussion 后，cx-oracle 的 developer 回复中提及防火墙 kill idle

       连接，然后网上查了下，确有其事。

       Git discussion：https://github.com/sqlalchemy/sqlalchemy/discussions/6049#discussioncomment-492159

       转自：https://blog.csdn.net/haozhongjun/article/details/78770073，未验证

       转自：https://www.cnblogs.com/zhilu-doc/p/6094424.html，未验证

       如果你的系统不是总有人访问的话，那么连接迟早会因为空闲而被切断（别让防火墙中断数据库连接）

       通常的 Web 应用都是无状态的连接，一般来说对于防火墙是非常友好的。但是，大多数 JavaEE 应用服务器都有连接池的概念。

       为了提高性能，应用服务器会预先打开并保持一些和后台数据库服务器、LDAP 服务器或其他服务器的连接。这些连接通常会

       将 TCP 的连接永久保持，除非发生了意外的情况。

       我有几个项目发生的问题都是应用服务器和数据库之间由于安全级别的问题，设有不同的防火墙。在系统运行的时候，偶尔

       会发生数据库连接无法获得的错误（在系统很闲的时候）。客户怀疑应用服务器的不稳定性。通过各种工具的分析（snoop），

       发现当应用服务器的数据库连接出现问题的时候，数据库这端没有任何问题，所有的 Session 连接都在，但是从应用服务器发

       过来的 TCP 请求没有到达数据库服务器。是中间的防火墙阻断了数据库的连接。在一个空闲的 TCP 连接上，可以很长时间没有

       任何的数据流，许多 TCP/IP 的初学者都对此感到惊奇。

       因此当数据库连接长时间不用（这种情况很常见，例如连接池中有 10 个连接，由于负载很小，一直只用到前几个）。一般来说，

       防火墙软件都会定时检查空闲的连接，并将它们阻断，来保证一些异常的中断连接被清除。

       这样，我们对数据库连接问题的原因找到了，由于空闲的数据库连接长时间不用被防火墙给阻断是主要的原因。被阻断了的

       数据库连接在使用的时候不会自动重新连接吗？大多数应 用服务器所使用的专业的数据库连接池都会有自动重新连接来解决

       这个问题。也有一些客户使用自己的连接池，或者一些简单的开源的方案，没有自动重联的功能， 导致应用不可用。就算有

       的连接池能够自动重连中断的连接，也不会时时刻刻都去检查这些在连接池中的连接是否被可用。

       因为这种检查非常消耗时间，还会影响应用，因此会每隔几分钟检查一次。在间隔时间内发生的连接中断仍然会引起系统的错误。

       另外的解决方案 就是从数据库服务器端进行配置，保证连接的畅通。例如在 Oracle 中可以设置 SQLNET.EXPIRE_TIME 小于防火墙

       的中断时间，就能够是 Oracle 的数据库连接在没有数据交换的情况下，由服务器端自动发出探测的数据报，使得防火墙不再认

       为数据库连接是空闲的连接。

      （dead connections）

4. 查阅

（1）sqlalchemy 官方 doc。

地址：https://docs.sqlalchemy.org/en/13/core/pooling.html#using-connection-pools-with-multiprocessing

The Engine returned by the create_engine () function in most cases has a QueuePool integrated, 

pre-configured with reasonable pooling defaults

可以看到官方默认集成了连接池，也就是 QueuePool，并且预先设定了一些参数，比如 pool size=5 等等

可以通过打印日志验证：logger.info (engine.pool.status ())

（2）因为我们是 gunicorn server，会启动 16 个进程（后改为 9 个进程，即 2n+1，n 为 cpu 数量），每个进程会持有一个 pool，

并且 sqlalchemy 创建 db 连接池的方式并不是预先创建好，可以理解为懒创建而不是预创建，比如当项目刚启动时其实是

没有连接池的，只有当后端接收到一个请求需要连接 db 时才会去对应的相应请求的 worker 下面创建一个隶属于该 worker

的 TCP 连接，而且并不会根据你设置的最小连接数直接创建多个连接，而是根据需要先创建 1 个，后续根据实际的 db 并发连接数

陆续增加 TCP 连接，并在这个连接中建立 session 执行 db 操作，stackoverflow 对于 gunicorn db connection pool 的连接描述

地址：https://stackoverflow.com/questions/60233495/choosing-db-pool-size-for-a-flask-sqlalchemy-app-running-on-gunicorn

the maximum number of database connections is (number of workers) * N where N = pool_size + max_overflow

（3）flask-sqlalchemy db connection pool 配置

地址：https://flask-sqlalchemy.palletsprojects.com/en/2.x/config/

可以看到 SQLALCHEMY_POOL_SIZE、SQLALCHEMY_POOL_TIMEOUT 等很多参数在 v2.4 已经 deprecated 了，但是 2.4 新增了

一个参数 SQLALCHEMY_ENGINE_OPTIONS，可以在这个参数里指定一些 pool 属性

    SQLALCHEMY_ENGINE_OPTIONS = {

        'pool': QueuePool,

        'pool_recycle': 280,  # which simply recycles connections after the given number of seconds

        'pool_size': 20,

        'pool_timeout': 100,

        'pool_pre_ping': True,

        'connect_timeout': 3,

        'echo': False,

        'echo_pool': False,

        "max_overflow": 5

    }

当 flask web 启动后会直接初始化该配置参数并生效。

（4）获取 oracle 当前某个 machine 的活跃连接及查询脚本

select t1.machine, t1.prev_exec_start, t1.logon_time, t1.sid, t1.serial#,

       t1.username, t1.status, t1.osuser, t1.port,

       t1.service_name, t1.terminal, t1.sql_id, t2.sql_text

  from v$session t1, v$sql t2 where t1.username='xxx' and machine='xxx' and t1.status!='INACTIVE';

（5）尝试使用 cx-oracle 自身实现的 pool，禁用 sqlalchemy 实现的 pool

cx-oracle 是 python DBAPI（可以理解为 java jdbc），而 sqlalchemy 是 python orm 框架（可以理解为 Hibernate），

所以 sqlalchemy 底层是基于 cx-oracle 的。

如果不想用 sqlalchemy 的 pool，需要在实例化 sqlalchemy 时绑定 poolClass 为 NullPool，因为 sqlalchemy 默认是

使用 QueuePool 的。使用 cx-oracle 自身的 pool 的方式如下：

    dsn = "(DESCRIPTION =" \

          "(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = xxxx))" \

          "(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = xxxx))" \

          "(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = xxxx))" \

          "(LOAD_BALANCE = YES)" \

          "(CONNECT_DATA = (SERVER = DEDICATED)" \

          "(SERVICE_NAME = xxx.xxx.com)" \

          "(FAILOVER_MODE = (TYPE = SELECT)" \

          "(METHOD = BASIC)" \

          "(RETRIES = 3)" \

          "(DELAY = 5))))"

    pool = cx_Oracle.SessionPool (

        user="xxx",

        password="xxx",

        dsn=dsn,

        min=10,

        max=20,

        increment=1,

        threaded=True

    )

    engine = create_engine ("oracle://", creator=pool.acquire, poolclass=NullPool, echo=True, echo_pool=True)

    sql = "select * from table"

    pd.read_sql_query (sql, engine)

（6）TCP 连接检测

因为猜测可能是 db server 端因为防火墙等原因断开和我们 app client 端的连接，所以这里有做监测。并且在监测时多次发现

每当周末过后，比如周一刚上班时，会发现部分 TCP 连接处于 CLOSE_WAIT 状态，并且过一会如果有用户从 web 访问 db 时，就会

出现 oracle EOF 问题。

简单查看服务器 python 进程对应 TCP 连接状态：

    ps -efl|grep python    （看 python pid）

    netstat -tnap|grep 1202   （看每个 pid 对应的 TCP 连接状态，1202 为 db server 端口）

    netstat -tnap|grep 1202|wc -l  （TCP 连接数）

参考：https://www.cnblogs.com/Serverlessops/articles/9367534.html

CLOSE_WAIT：在 TCP 四次挥手期间，被动关闭端收到 FIN 包后，进入 CLOSE_WAIT 状态

四次挥手大致过程

Client: 服务端大哥，我事情都干完了，准备撤了，这里对应的就是客户端发了一个 FIN

Server：知道了，但是你等等我，我还要收收尾，这里对应的就是服务端收到 FIN 后回应的 ACK

经过上面两步之后，服务端就会处于 CLOSE_WAIT 状态。过了一段时间 Server 收尾完了

Server：小弟，哥哥我做完了，撤吧，服务端发送了 FIN

Client：大哥，再见啊，这里是客户端对服务端的一个 ACK

到此服务端就可以跑路了，但是客户端还不行。为什么呢？客户端还必须等待 2MSL 个时间，这里为什么客户端还不能直接

跑路呢？主要是为了防止发送出去的 ACK 服务端没有收到，服务端重发 FIN 再次来询问，如果客户端发完就跑路了，那么

服务端重发的时候就没人理他了。并且这个等待的时间长度也很讲究。

Maximum Segment Lifetime 报文最大生存时间，它是任何报文在网络上存在的最长时间，超过这个时间报文将被丢弃。

（7）sqlalchemy Connection 类描述

     The Connection object represents a single DBAPI connection checked out 

     from the connection pool. In this state, the connection pool has no affect 

     upon the connection, including its expiration or timeout state. For the 

     connection pool to properly manage connections, connections should be 

     returned to the connection pool (i.e. ``connection.close ()``) whenever the

     connection is not in use.

（8）flask 日志格式定义

     $(name) s: Logger 的名字 (root):l = logging.getLogger ();l.name

     %(levelno) d: 打印日志级别的数字

     %(levelname) s: 打印日志级别名称

     %(pathname) s: 当前执行程序的路径，相当于 sys.argv [0]

     %(filename) s: 当前执行程序文件名

     %(funcName) s: 打印日志的当前函数名

     %(lineno) d: 打印日志的当前行号

     %(asctime) s: 打印日志的时间

     %(thread) d: 打印线程 ID

     %(threadName) s: 打印线程名字

     %(process) d: 打印进程 ID

     %(message) s: 日志信息【重点要记录的信息】

（9）sqlalchemy-collectd

sqlalchemy 连接监控模块，也是 sqlalchemy 团队自己研发的，但是因为缺少文档，我没有 build 成功

（10）tnsnames.ora 说明文档（曾怀疑是 TNS 连通性问题）

https://docs.oracle.com/database/121/NETRF/tnsnames.htm#NETRF282

http://www.dba-oracle.com/t_troubleshooting_sql_net_connectivity_errors.htm

5. 解决

最开始因为没有太多时间细察这个问题，我们做了一些保护，就是一旦遇到 EOF 问题后，服务器自动通

过 API 重启 app（最开始是自己重启自己，后面是通过外部服务启动问题 app）。

后面我们认为是 pool 没有生效，所以尝试显式配置 QueuePool，指定最小连接 10，最大连接 20，最大允许超出 5 个连接，

并且指定每次自增连接数量为 1，部署后未生效，还是会出现 EOF 问题。

中间也有找 sqlalchemy 开发讨论，他们其实在测试时也遇到了很多 EOF 问题，对于具体的 rootcause 也不

是很了解，因为 EOF 毕竟涉及的比较多，然后找 cx-oracle 的开发一起帮忙看了一下，后续我们将目光转

移到连接池的 TCP 连接上。

目前我们尝试在创建 engine 时，通过 dsn 配置，使其 HA（我们 db 是多台且 LB，我们之前只使用了其一）

SQLALCHEMY_DATABASE_URI = oracle://user:psw@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.x.xxx)(PORT = xxx))(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = xx)) (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = xx)) (LOAD_BALANCE = YES) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = xxx.xxx.xxx) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 3)(DELAY = 5))))

在使用 orm 执行 db 操作时，将 poolClass 置为 NullPool，不再使用 orm 的连接池

SQLALCHEMY_ENGINE_OPTIONS = {'poolclass': NullPool}

目前还处于观测中，后续如有问题及时更新

7. 参考：

https://blogs.oracle.com/opal/how-to-use-python-flask-with-oracle-database

https://pypi.org/project/DBUtils/  （pip install DBUtils，未尝试）

https://www.cnblogs.com/Serverlessops/articles/9367534.html