[ 问题咨询 ]Lost connection to MySQL server during query

[Aaron199]

问题描述

如下图所示的4次”执行异常“，4次均为为alter改表结构语句，每次从开始执行到结束执行都是2小时

archery日志如下:

[2022-06-04 01:02:13,118][MainThread:139741195802432][task_id:default][goinception.py:146][WARNING]- goInception语句执行报错，错误信息Traceback (most recent call last):
  File "/opt/archery/sql/engines/goinception.py", line 135, in query
    effect_row = cursor.execute(sql)
  File "/opt/venv4archery/lib/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/opt/venv4archery/lib/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/opt/venv4archery/lib/python3.9/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query')

goinception日志如下, 从开始执行到报错时间来看，第一条间隔2分钟，第二条3分钟，第三条11分钟，第四条4分钟

time="2022/05/29 23:35:28.309" level=error msg="con:15953 read packet error, close this connection read tcp 10.7.1.48:4000->10.7.2.107:54382: read: connection reset by peer\ngithub.com/pingcap/errors.AddStack\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.0/errors.go:174\ngithub.com/pingcap/errors.Trace\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.0/juju_adaptor.go:12\ngithub.com/hanchuanchuan/goInception/server.(*packetIO).readOnePacket\n\t/go/src/github.com/hanchuanchuan/goInception/server/packetio.go:70\ngithub.com/hanchuanchuan/goInception/server.(*packetIO).readPacket\n\t/go/src/github.com/hanchuanchuan/goInception/server/packetio.go:90\ngithub.com/hanchuanchuan/goInception/server.(*clientConn).readPacket\n\t/go/src/github.com/hanchuanchuan/goInception/server/conn.go:205\ngithub.com/hanchuanchuan/goInception/server.(*clientConn).Run\n\t/go/src/github.com/hanchuanchuan/goInception/server/conn.go:445\ngithub.com/hanchuanchuan/goInception/server.(*Server).onConn\n\t/go/src/github.com/hanchuanchuan/goInception/server/server.go:327\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1337" file=conn.go func=Run line=450

time="2022/05/31 00:03:10.954" level=error msg="con:16210 read packet error, close this connection read tcp 10.7.1.48:4000->10.7.2.107:48368: read: connection reset by peer\ngithub.com/pingcap/errors.AddStack\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.0/errors.go:174\ngithub.com/pingcap/errors.Trace\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.0/juju_adaptor.go:12\ngithub.com/hanchuanchuan/goInception/server.(*packetIO).readOnePacket\n\t/go/src/github.com/hanchuanchuan/goInception/server/packetio.go:70\ngithub.com/hanchuanchuan/goInception/server.(*packetIO).readPacket\n\t/go/src/github.com/hanchuanchuan/goInception/server/packetio.go:90\ngithub.com/hanchuanchuan/goInception/server.(*clientConn).readPacket\n\t/go/src/github.com/hanchuanchuan/goInception/server/conn.go:205\ngithub.com/hanchuanchuan/goInception/server.(*clientConn).Run\n\t/go/src/github.com/hanchuanchuan/goInception/server/conn.go:445\ngithub.com/hanchuanchuan/goInception/server.(*Server).onConn\n\t/go/src/github.com/hanchuanchuan/goInception/server/server.go:327\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1337" file=conn.go func=Run line=450

time="2022/05/31 00:11:18.827" level=error msg="con:16209 read packet error, close this connection read tcp 10.7.1.48:4000->10.7.2.107:48362: read: connection reset by peer\ngithub.com/pingcap/errors.AddStack\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.0/errors.go:174\ngithub.com/pingcap/errors.Trace\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.0/juju_adaptor.go:12\ngithub.com/hanchuanchuan/goInception/server.(*packetIO).readOnePacket\n\t/go/src/github.com/hanchuanchuan/goInception/server/packetio.go:70\ngithub.com/hanchuanchuan/goInception/server.(*packetIO).readPacket\n\t/go/src/github.com/hanchuanchuan/goInception/server/packetio.go:90\ngithub.com/hanchuanchuan/goInception/server.(*clientConn).readPacket\n\t/go/src/github.com/hanchuanchuan/goInception/server/conn.go:205\ngithub.com/hanchuanchuan/goInception/server.(*clientConn).Run\n\t/go/src/github.com/hanchuanchuan/goInception/server/conn.go:445\ngithub.com/hanchuanchuan/goInception/server.(*Server).onConn\n\t/go/src/github.com/hanchuanchuan/goInception/server/server.go:327\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1337" file=conn.go func=Run line=450

time="2022/06/03 23:04:59.378" level=error msg="con:16958 read packet error, close this connection read tcp 10.7.1.48:4000->10.7.2.107:58356: read: connection reset by peer\ngithub.com/pingcap/errors.AddStack\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.0/errors.go:174\ngithub.com/pingcap/errors.Trace\n\t/go/pkg/mod/github.com/pingcap/errors@v0.11.0/juju_adaptor.go:12\ngithub.com/hanchuanchuan/goInception/server.(*packetIO).readOnePacket\n\t/go/src/github.com/hanchuanchuan/goInception/server/packetio.go:70\ngithub.com/hanchuanchuan/goInception/server.(*packetIO).readPacket\n\t/go/src/github.com/hanchuanchuan/goInception/server/packetio.go:90\ngithub.com/hanchuanchuan/goInception/server.(*clientConn).readPacket\n\t/go/src/github.com/hanchuanchuan/goInception/server/conn.go:205\ngithub.com/hanchuanchuan/goInception/server.(*clientConn).Run\n\t/go/src/github.com/hanchuanchuan/goInception/server/conn.go:445\ngithub.com/hanchuanchuan/goInception/server.(*Server).onConn\n\t/go/src/github.com/hanchuanchuan/goInception/server/server.go:327\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1337" file=conn.go func=Run line=450

版本信息

• 应用版本/分支： Release v1.8.4
• 部署方式：Docker

”Lost connection to MySQL server during query“通常解决办法是修改” wait_timeout“和” max_allowed_packet“，我司设置分别为”8h“和”1G“,从goinception的报错时间来看应该和这2个参数没有关系，是否是因为goinception和archery之间由于长时间没有交互数据导致，请大佬提供一些帮助建议

[hhyo]

两个日志的时间貌似对应不上，可以找一下工单执行失败的时间，goInception是否有相关日志

从日志来看就是Archery和goInception的链接异常断开了，goInception本身的waitTimeout是28800s，所以不是正常的超时退出，可以查看看goInception是否有异常重启记录

同时确认下goInception操作的远端DDL语句执行是否已经正常结束了，理论上执行链接终止，goInception会发起kill的操作，并不会继续执行