背景:帮产品写一个类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/