Python调用_mysql_connector报错“MySQLInterfaceError: Lost connection to MySQL server during query”修复笔记

背景：帮产品写一个类OLAP的报表脚本。

场景：Python连接业务库MySQL读取原始数据并计算后将结果写入新的报表库MySQL。

问题：DQL和DDL手动执行都没有任何问题但是放到Python脚本中跑一段时间后会报错。

Python的Traceback报错信息

_mysql_connector.MySQLInterfaceError: Lost connection to MySQL server during query

MySQL的错误日志报错信息

...omitted...
2020-03-05 14:39:31 4667 [Warning] Aborted connection 4584234 to db: 'test' user: 'ops' host: '127.0.0.1' (Got an error reading communication packets)
2020-03-05 14:39:31 4667 [Warning] Aborted connection 4584233 to db: 'test' user: 'ops' host: '127.0.0.1' (Got an error writing communication packets)
2020-03-05 14:47:18 4667 [Warning] Aborted connection 4584395 to db: 'test' user: 'ops' host: '127.0.0.1' (Got timeout writing communication packets)
2020-03-05 14:57:08 4667 [Warning] Aborted connection 4584478 to db: 'test' user: 'root' host: '127.0.0.1' (Got an error reading communication packets)
2020-03-05 15:16:27 4667 [Warning] Aborted connection 4585100 to db: 'test' user: 'ops' host: '127.0.0.1' (Got timeout writing communication packets)
2020-03-05 19:40:08 4667 [Warning] Aborted connection 4591855 to db: 'test' user: 'ops' host: '127.0.0.1' (Got timeout writing communication packets)
...omitted...

原因分析：一方面，查询场景是对数据量较大且包含大文本字段的日志记录的解析计算，查询比较耗时；另一方面，数据库是部署在云端而查询脚本是在本地跑，跨网络数据包传输。

解决方式：调起连接后首先调大读写连接超时参数和数据包参数，再执行具体的SQL。

con.query("SET net_read_timeout=28800;")
con.query("SET net_write_timeout=28800;")
con.query("SET GLOBAL max_allowed_packet=1073741824;")

 

 

参考

http://mysql.taobao.org/monthly/2017/05/04/

https://www.percona.com/blog/2016/05/16/mysql-got-an-error-reading-communication-packet-errors/