Ubuntu下SQLAlchemy+pyodbc连接Docker中SQL Server报错原因排查

我在Ubuntu系统中使用SQLAlchemy通过pyodbc访问Docker容器内的SQL Server，使用的SQLAlchemy连接URL为：

'mssql+pyodbc:///?odbc_connects="Driver=;SERVER=;DATABASE=;UID=;PWD=;port=;TDS_Version=8.0"'

执行语句result = session.query(User).filter(User.username == username).first()时，触发如下错误：

(pyodbc.OperationalError) ('08S01', '[08S01] [FreeTDS][SQL Server]Read from the server failed (20004) (SQLExecDirectW)')
[SQL: SELECT TOP 1 [user].user_id AS user_user_id, [user].username AS user_username, [user].password AS user_password, [user].first_name AS user_first_name, [user].last_name AS user_last_name, [user].designation AS user_designation, [user].last_login AS user_last_login
FROM [user]
WHERE [user].username = ?]
[parameters: ('device1',)]
(Background on this error at: http://sqlalche.me/e/e3q8)
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1249, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
pyodbc.OperationalError: ('08S01', '[08S01] [FreeTDS][SQL Server]Read from the server failed (20004) (SQLExecDirectW)')

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

Traceback (most recent call last):
  File "./database1.py", line 43, in get_user_details
    result = session.query(User).filter(User.username == username).first()
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3232, in first
    ret = list(self[0:1])
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3018, in __getitem__
    return list(res)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3334, in __iter__
    return self._execute_and_instances(context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3359, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
    distilled_params,
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1253, in _execute_context
    e, statement, parameters, cursor, context
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1473, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1249, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('08S01', '[08S01] [FreeTDS][SQL Server]Read from the server failed (20004) (SQLExecDirectW)')
[SQL: SELECT TOP 1 [user].user_id AS user_user_id, [user].username AS user_username, [user].password AS user_password, [user].first_name AS user_first_name, [user].last_name AS user_last_name, [user].designation AS user_designation, [user].last_login AS user_last_login
FROM [user]
WHERE [user].username = ?]
[parameters: ('device1',)]
(Background on this error at: http://sqlalche.me/e/e3q8)

后续执行所有查询时，每次都会触发不同错误，示例如下：

sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('01000', '[01000] [FreeTDS][SQL Server]Unexpected EOF from the server (20017) (SQLExecDirectW)')

pyodbc.OperationalError: ('08S01', '[08S01] [FreeTDS][SQL Server]Communication link failure (0) (SQLExecDirectW)')

sqlalchemy.exc.ResourceClosedError: This result object does not return rows. It has been closed automatically.

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY000', '[HY000] [FreeTDS][SQL Server]Unknown error (0) (SQLExecDirectW)')

pyodbc.OperationalError: ('08S01', '[08S01] [FreeTDS][SQL Server]Bad token from the server: Datastream processing out of sync (20020) (SQLExecDirectW)')

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY008', '[HY008] [FreeTDS][SQL Server]Operation was cancelled (0) (SQLExecDirectW)')

2023-01-10 16:38:20,010 - ERROR - sqlalchemy.pool.impl.QueuePool - Exception during reset or similar
dbapi_connection.rollback()
pyodbc.Error: ('HY000', 'The driver did not supply an error!')

更新
SQL Server版本信息（执行select @@version返回）：

Microsoft SQL Server 2017 (RTM-CU22) (KB4577467) - 14.0.3356.20 (X64)
Aug 20 2020 22:33:27
Copyright (C) 2017 Microsoft Corporation
Express Edition (64-bit) on Linux (Ubuntu 16.04.7 LTS)

请问该错误的原因是什么？

这些错误核心是数据库连接链路异常，后续各类错误都是初始连接中断后，SQLAlchemy连接池和事务处理逻辑混乱引发的连锁反应，具体诱因可从以下几点排查：

1. TDS版本不兼容 #

你指定的TDS_Version=8.0对应SQL Server 2000的协议版本，但实际使用的是SQL Server 2017，协议版本不匹配会导致通信解析错误，直接触发"Read from the server failed"这类底层传输问题。SQL Server 2017对应的TDS版本应为7.4，调整这个参数是首要解决点。

2. FreeTDS驱动配置缺失或版本问题 #

• 连接URL中的Driver=字段为空，未明确指定FreeTDS驱动的实际路径（例如Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so），会导致pyodbc无法正确加载驱动，引发通信异常。
• FreeTDS版本过旧，对SQL Server 2017的兼容性不足，建议升级到最新稳定版。

3. Docker网络连通性故障 #

• 容器端口映射是否正确？Ubuntu主机可通过telnet <容器IP> <端口>或nc -zv <容器IP> <端口>测试端口连通性，确认网络链路是否通畅。
• Docker bridge模式下的端口转发可能存在丢包、延迟问题，导致TCP连接中断，引发"Communication link failure"这类错误。

4. SQLAlchemy连接池配置不合理 #

初始连接中断后，连接池中的无效连接未被及时清理，后续查询复用这些无效连接时就会出现"Can't reconnect until invalid transaction is rolled back"等异常。可调整连接池的pool_recycle参数，设置合理的超时时间，让连接池自动回收过期或无效连接。

5. 事务状态未正确处理 #

第一次查询失败后，事务处于未提交/未回滚的异常状态，后续操作复用该事务时会触发各类错误。需确保捕获数据库异常时，主动回滚事务并关闭当前会话，避免污染连接池。

内容的提问来源于stack exchange，提问作者Vishak Raj