从服务器读取失败 (20004) (SQLExecDirectW)从服务器读取失败 (20004) (SQLExecDirectW)

小能豆

从服务器读取失败 (20004) (SQLExecDirectW)从服务器读取失败 (20004) (SQLExecDirectW)

我正在使用 sqlalchemy 在 Ubuntu OS 中使用 pyodbc 访问 sql 服务器

SQL Server 在 Docker 容器中运行

我在 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 服务器版本select @@verion–

Microsoft SQL Server 2017 (RTM-CU22) (KB4577467) - 14.0.3356.20 (X64) 2020 年 8 月 20 日 22:33:27 版权所有 (C) 2017 Microsoft Corporation Linux 上的 Express Edition（64 位）（Ubuntu 16.04.7 LTS）

阅读 12

2024-12-11

共1个答案

小能豆

这个错误 (pyodbc.OperationalError 和与 FreeTDS 相关的错误) 通常指示着 SQL Server 与客户端（在你的情况下是通过 pyodbc 和 FreeTDS 连接）之间的通信问题。由于 SQL Server 运行在 Docker 容器中，可能有几个潜在的原因导致这种情况。以下是一些可能的原因和解决方法：

1. FreeTDS 配置问题

FreeTDS 是一个用于与 SQL Server 和 Sybase 进行通信的开源库，可能存在与 SQL Server 版本或配置的兼容性问题。尤其是在 SQL Server 2017 上运行时，FreeTDS 可能需要一些额外的配置。

解决方法：

确保 FreeTDS 配置文件（通常是 /etc/freetds/freetds.conf）的设置正确，特别是关于 TDS 版本的配置。你可以尝试设置 TDS 版本为 7.3 或 8.0，这取决于你的 SQL Server 版本。

编辑 /etc/freetds/freetds.conf 文件，确保类似如下内容：

```ini
[global]
tds version = 8.0 # 或者 7.3，依据 SQL Server 版本调整

[your_server]
host = your_sql_server_ip_or_hostname
port = 1433
```

然后重新启动相关服务，确保配置生效。

2. SQL Server 配置问题

有时，SQL Server 配置可能会导致连接问题，特别是在容器化环境中运行时。

解决方法：

启用 TCP/IP 连接：确保 SQL Server 已启用 TCP/IP 连接。如果你使用 Docker 启动 SQL Server，检查 SQL Server 容器的网络设置和端口映射，确保外部请求可以通过端口 1433 正常连接。
检查防火墙设置：如果 SQL Server 容器运行在虚拟机或 Docker 内，确保防火墙没有阻止来自宿主机或客户端的 TCP 连接。你可以通过运行以下命令查看是否有端口限制：
bash sudo ufw status
配置 SQL Server 认证：确保 SQL Server 配置为接受 SQL 身份验证而不是 Windows 身份验证，特别是如果你使用用户名和密码连接。

3. ODBC 驱动版本

在某些情况下，pyodbc 和 SQL Server 之间的通信会受到驱动版本的影响。确保安装的 ODBC 驱动程序与 SQL Server 版本兼容。

解决方法：

确保安装了最新的 FreeTDS 驱动和 pyodbc 包。你可以通过以下命令更新它们：
bash sudo apt-get update sudo apt-get install freetds-dev pip install --upgrade pyodbc

4. 连接 URL 和端口设置

检查连接 URL 配置，尤其是是否在连接字符串中指定了正确的 SERVER、DATABASE、UID 和 PWD。

你的连接字符串看起来有一些小问题，odbc_connects 参数应该是 "odbc_connect"，并且 URL 中的参数需要用 & 连接而不是 ;。更新的连接字符串应该像这样：

'mssql+pyodbc:///?odbc_connect=Driver={FreeTDS};Server=your_server_ip;Database=your_db;UID=your_user;PWD=your_password;Port=1433;TDS_Version=8.0'

请根据你的实际环境替换 your_server_ip, your_db, your_user, your_password 等参数。

5. 网络问题（Docker 环境）

如果 SQL Server 在 Docker 容器中运行，可能有一些网络配置问题，尤其是 Docker 的端口映射或者网络模式配置不当可能会导致连接失败。

解决方法：

确保 Docker 容器的端口 1433 正确映射到宿主机，检查 docker run 命令或 Docker Compose 配置文件中的端口映射：

bash docker run -p 1433:1433 ...

如果 Docker 容器运行在不同的网络模式下（如 bridge 网络），尝试将容器连接到宿主机的网络，以确保可以直接通过 localhost 或宿主机的 IP 地址访问 SQL Server。

6. 数据库查询优化

Read from the server failed 错误可能还与查询本身有关，尤其是在处理大量数据时。虽然不常见，但如果你使用的是一个大的数据集，尝试减少查询的复杂性或分页查询，以降低负载。

解决方法：

使用分页查询，避免一次性加载大量数据，尝试按批次处理数据。

7. 重启 SQL Server 容器和清理连接池

容器化的 SQL Server 有时可能会遇到连接池问题，尤其是在频繁的查询或断开连接之后。

解决方法：

尝试重启 SQL Server 容器，清理可能存在的连接池问题。

bash docker restart your_sql_server_container

在 SQLAlchemy 中，清理会话连接池可以帮助解决一些由于连接断开引起的问题：

```python
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()

# 清理已关闭的会话
session.remove()
```

总结：

以上步骤涉及配置 FreeTDS 驱动、检查 SQL Server 配置、确保 ODBC 驱动版本兼容以及排查网络问题。确保你的 Docker 配置正确，端口映射和防火墙设置没有阻碍连接。调整连接字符串，并确保 SQL Server 可以通过 TCP/IP 正常通信。如果问题仍然存在，可以尝试从 SQLAlchemy 端口清理会话池或重启 SQL Server 容器。

2024-12-11