pymysql.err.OperationalError-查询期间与MySQL服务器的连接断开 [英] pymysql.err.OperationalError - Lost connection to MySQL server during query

问题描述

我正在使用Python脚本将记录插入到MySQL数据库表中.该脚本失败，并显示以下错误消息.

MySQL版本是8.0.17 ， Python版本3.6.5

(pymysql.err.OperationalError)(2013，'在查询过程中丢失了与MySQL服务器的连接([WinError 10060]连接尝试失败，因为一段时间后被连接方未正确响应，或者由于连接而建立连接失败主机未能响应)')

(此错误的背景位于:

解决方案

MySQL自动关闭在特定时间段内处于空闲状态的连接(对于非交互式连接，则为 wait_timeout ).因此，如果空闲时间过多，并且由于服务器重新启动而导致连接没有更新或连接无效，则可能会关闭您的连接.

SQL炼金术在如何处理池断开连接.

您应该查看的两个选项是 pool_pre_ping 参数，该参数在每个查询之前添加 SELECT 1 ，以检查连接是否仍然有效，否则，连接将被回收.

另一个选项是 pool_recycle 时间，该时间应始终小于您的mysql wait_timeout .此时间过后，连接将自动回收以使其不在 wait_timeout 中运行.

您可以使用以下命令在MySQL中检查您的连接

  SHOW PROCESSLIST; 

您应该在其中看到所有打开的连接以及它们所处的状态.

I am using Python script to insert records into MySQL database table. The script fails with the following error message.

MySQL version is 8.0.17 ,Python version 3.6.5

(pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query ([WinError 10060] A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond)')

(Background on this error at: http://sqlalche.me/e/e3q8)

The issue is for only few tables.

解决方案

MySQL automatically closes connections that have been idle for a specific period of time (wait_timeout for non-interactive connections). Therefore it may happen, that your connections are closed if there is too much idle time and connections are not renewed or connections are invalidated because of server restarts.

SQL-Alchemy mentions several strategies on how to tackle the issue of automatic disconnects and database restarts in its documentation on how to deal with pool disconnects.

Two options that you should have a look at are the pool_pre_ping parameter that adds a SELECT 1 before each query to check if the connection is still valid, otherwise the connection will be recycled.

The other option is pool_recycle time that should always be less then your mysql wait_timeout. After this time the connection is automatically recycled to not run in the wait_timeout.

You can check your connections in MySQL using the command

SHOW PROCESSLIST;

where you should see all open connection an the status they are in.

这篇关于pymysql.err.OperationalError-查询期间与MySQL服务器的连接断开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！