关于mysql连接异常警告的疑问

Isatiger · 发表于 2014-8-8 15:05

测试环境：
os rhel5.4 x86_64
db 5.1.32 x86_64
开启了log-warning=2，然后重启。

ERROR log：
40806 11:45:14 [Warning] Aborted connection 94 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:50:08 [Warning] Aborted connection 104 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:50:08 [Warning] Aborted connection 105 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:50:08 [Warning] Aborted connection 106 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:50:15 [Warning] Aborted connection 107 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:50:15 [Warning] Aborted connection 108 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:50:15 [Warning] Aborted connection 109 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:55:08 [Warning] Aborted connection 121 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:55:08 [Warning] Aborted connection 122 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:55:08 [Warning] Aborted connection 123 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:55:14 [Warning] Aborted connection 124 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:55:14 [Warning] Aborted connection 125 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:55:14 [Warning] Aborted connection 126 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 12:00:08 [Warning] Aborted connection 133 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)

同时在db主机上抓取包：
# tcpdump tcp -s0 -w /tmp/$(date +%Y-%m-%d.%H%M).dump port 3311 -i any
解析包：
# tcpdump tcp  -r 2014-08-06.1154.dump  > /tmp/t.txt
#more /tmp/t.txt
.......
11:55:02.376530 IP 192.168.35.45.36329 > psweb0.mcns-tel-ret: P 2772:2971(199) ack 22954 win 501 <nop,nop,timestamp 2077641432 1206895725>
11:55:02.376641 IP psweb0.mcns-tel-ret > 192.168.35.45.36329: P 22954:24290(1336) ack 2971 win 138 <nop,nop,timestamp 1206895726 2077641432>
11:55:02.377002 IP 192.168.35.45.36329 > psweb0.mcns-tel-ret: P 2971:3112(141) ack 24290 win 501 <nop,nop,timestamp 2077641433 1206895726>
11:55:02.377094 IP psweb0.mcns-tel-ret > 192.168.35.45.36329: P 24290:24786(496) ack 3112 win 146 <nop,nop,timestamp 1206895726 2077641433>
11:55:02.377431 IP 192.168.35.45.36329 > psweb0.mcns-tel-ret: P 3112:3463(351) ack 24786 win 501 <nop,nop,timestamp 2077641433 1206895726>
11:55:02.377593 IP psweb0.mcns-tel-ret > 192.168.35.45.36329: . 24786:26234(1448) ack 3463 win 155 <nop,nop,timestamp 1206895727 2077641433>
11:55:02.377597 IP psweb0.mcns-tel-ret > 192.168.35.45.36329: P 26234:27307(1073) ack 3463 win 155 <nop,nop,timestamp 1206895727 2077641433>
11:55:02.377745 IP 192.168.35.45.36329 > psweb0.mcns-tel-ret: . ack 27307 win 501 <nop,nop,timestamp 2077641434 1206895727>
11:55:02.378121 IP 192.168.35.45.36329 > psweb0.mcns-tel-ret: P 3463:3635(172) ack 27307 win 501 <nop,nop,timestamp 2077641434 1206895727>
11:55:02.378221 IP psweb0.mcns-tel-ret > 192.168.35.45.36329: P 27307:28055(748) ack 3635 win 163 <nop,nop,timestamp 1206895727 2077641434>
11:55:02.378547 IP 192.168.35.45.36329 > psweb0.mcns-tel-ret: P 3635:3818(183) ack 28055 win 501 <nop,nop,timestamp 2077641434 1206895727>
11:55:02.378658 IP psweb0.mcns-tel-ret > 192.168.35.45.36329: P 28055:28906(851) ack 3818 win 171 <nop,nop,timestamp 1206895728 2077641434>
11:55:02.379007 IP 192.168.35.45.36329 > psweb0.mcns-tel-ret: P 3818:3989(171) ack 28906 win 501 <nop,nop,timestamp 2077641435 1206895728>
11:55:02.379099 IP psweb0.mcns-tel-ret > 192.168.35.45.36329: P 28906:29640(734) ack 3989 win 180 <nop,nop,timestamp 1206895728 2077641435>
11:55:02.379416 IP 192.168.35.45.36329 > psweb0.mcns-tel-ret: P 3989:4160(171) ack 29640 win 501 <nop,nop,timestamp 2077641435 1206895728>
.........
前提这是局域网，ntpdate都是相同的。
11:55分err log出现楼主提示的错误，同时解析包数据出现上面的数据，说明db主机35.39在向client端35.45相互发送大量的数据，但是db主机的发送的数据包远远大于client发送的数据包。这有可能导致client请求db主机的packet出现损坏，解析时候报错，这种错误其实很底层的错误，不影响，可以忽略。

修改my.cnf参数：
[mysqld]
max_allowed_packet = 256M
重启生效

或者
SET GLOBAL max_allowed_packet = 1024 * 1024 * 256;
此时发现err log中依然报错以上错误。

mysql> select @@session.max_allowed_packet, @@global.max_allowed_packet ;
+------------------------------+-----------------------------+
| @@session.max_allowed_packet | @@global.max_allowed_packet |
+------------------------------+-----------------------------+
|                   268435456 |                268435456 |
+------------------------------+-----------------------------+
1 row in set (0.00 sec)

此时session级别的max_allowed_packet 继承了global的max_allowed_packet大小，都是256M大小。

mysql> select RPAD('hi',100000000,'a');
ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes

但是这里查询100000000/1024/1024=94M大小的返回包就照样报错。
由此可见，cli的mysql的max_allowed_packet不受global 的影响。
如果cli要设置max_allowed_packet大小，请用 mysql --max-allowed_packet=xxxxx -u -p

mysql> select @@session.max_allowed_packet, @@global.max_allowed_packet ;
+------------------------------+-----------------------------+
| @@session.max_allowed_packet | @@global.max_allowed_packet |
+------------------------------+-----------------------------+
|                   268435456 |                268435456 |
+------------------------------+-----------------------------+
1 row in set (0.00 sec)

mysql> select RPAD('hi',100000000,'a');
ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes

mysql> select @@session.max_allowed_packet, @@global.max_allowed_packet；
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 70
Current database: test

此时db connect断开，这个错误就是err log的错误，但是因为mysql内部的机制，会自动重连。

# mysql -u  -p  -h192.168.35.39 -P3311  -Dtest --max-allowed-packet=268435456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 131

mysql> select @@session.max_allowed_packet, @@global.max_allowed_packet ;
+------------------------------+-----------------------------+
| @@session.max_allowed_packet | @@global.max_allowed_packet |
+------------------------------+-----------------------------+
|                   268435456 |                268435456 |
+------------------------------+-----------------------------+
1 row in set (0.00 sec)

mysql> pager cat > /dev/null
PAGER set to 'cat > /dev/null'

mysql> select RPAD('hi',100000000,'a');
1 row in set (1.05 sec)
可以返回结果。

dsf15665 · 发表于 2014-8-9 22:53

办本科报到证【【QQ:510164166】】研究生报到证样本
专业办理大学报到证【【QQ:510164166】】学位证
报到证样本【QQ:510164166】办本科研究生报到证
英语四六级成绩单、【QQ:510164166】专四专八
英语四六级证【QQ:510164166】英语专业四八级
专四专八证书【QQ:510164166】公共英语三级证书
计算机等级证,大专、本科、硕士毕业证【QQ:510164166】
学士、硕士学位证,本科、研究生报到证【QQ:510164166】
需要办理的朋友,
可以加我在线【QQ:510164166】, 价钱合理!
制作工艺一流!欢迎来电或是在线QQ咨询办理

Isatiger · 发表于 2014-8-12 16:26

本帖最后由 Isatiger 于 2014-8-12 16:27 编辑

测试环境：
os rhel5.4 x86_64
db 5.1.32 x86_64
开启了log-warning=2，然后重启。

ERROR log：
40806 11:45:14 [Warning] Aborted connection 94 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:50:08 [Warning] Aborted connection 104 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:50:08 [Warning] Aborted connection 105 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:50:08 [Warning] Aborted connection 106 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:50:15 [Warning] Aborted connection 107 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:50:15 [Warning] Aborted connection 108 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:50:15 [Warning] Aborted connection 109 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:55:08 [Warning] Aborted connection 121 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:55:08 [Warning] Aborted connection 122 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:55:08 [Warning] Aborted connection 123 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:55:14 [Warning] Aborted connection 124 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:55:14 [Warning] Aborted connection 125 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 11:55:14 [Warning] Aborted connection 126 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)
140806 12:00:08 [Warning] Aborted connection 133 to db: 'areagz0' user: 'btir' host: '192.168.35.45' (Got an error reading communication packets)

同时在db主机上抓取包：
# tcpdump tcp -s0 -w /tmp/$(date +%Y-%m-%d.%H%M).dump port 3311 -i any
解析包：
# tcpdump tcp  -r 2014-08-06.1154.dump  > /tmp/t.txt
#more /tmp/t.txt
.......
11:55:02.376530 IP 192.168.35.45.36329 > psweb0.mcns-tel-ret: P 2772:2971(199) ack 22954 win 501 <nop,nop,timestamp 2077641432 1206895725>
11:55:02.376641 IP psweb0.mcns-tel-ret > 192.168.35.45.36329: P 22954:24290(1336) ack 2971 win 138 <nop,nop,timestamp 1206895726 2077641432>
11:55:02.377002 IP 192.168.35.45.36329 > psweb0.mcns-tel-ret: P 2971:3112(141) ack 24290 win 501 <nop,nop,timestamp 2077641433 1206895726>
11:55:02.377094 IP psweb0.mcns-tel-ret > 192.168.35.45.36329: P 24290:24786(496) ack 3112 win 146 <nop,nop,timestamp 1206895726 2077641433>
11:55:02.377431 IP 192.168.35.45.36329 > psweb0.mcns-tel-ret: P 3112:3463(351) ack 24786 win 501 <nop,nop,timestamp 2077641433 1206895726>
11:55:02.377593 IP psweb0.mcns-tel-ret > 192.168.35.45.36329: . 24786:26234(1448) ack 3463 win 155 <nop,nop,timestamp 1206895727 2077641433>
11:55:02.377597 IP psweb0.mcns-tel-ret > 192.168.35.45.36329: P 26234:27307(1073) ack 3463 win 155 <nop,nop,timestamp 1206895727 2077641433>
11:55:02.377745 IP 192.168.35.45.36329 > psweb0.mcns-tel-ret: . ack 27307 win 501 <nop,nop,timestamp 2077641434 1206895727>
11:55:02.378121 IP 192.168.35.45.36329 > psweb0.mcns-tel-ret: P 3463:3635(172) ack 27307 win 501 <nop,nop,timestamp 2077641434 1206895727>
11:55:02.378221 IP psweb0.mcns-tel-ret > 192.168.35.45.36329: P 27307:28055(748) ack 3635 win 163 <nop,nop,timestamp 1206895727 2077641434>
11:55:02.378547 IP 192.168.35.45.36329 > psweb0.mcns-tel-ret: P 3635:3818(183) ack 28055 win 501 <nop,nop,timestamp 2077641434 1206895727>
11:55:02.378658 IP psweb0.mcns-tel-ret > 192.168.35.45.36329: P 28055:28906(851) ack 3818 win 171 <nop,nop,timestamp 1206895728 2077641434>
11:55:02.379007 IP 192.168.35.45.36329 > psweb0.mcns-tel-ret: P 3818:3989(171) ack 28906 win 501 <nop,nop,timestamp 2077641435 1206895728>
11:55:02.379099 IP psweb0.mcns-tel-ret > 192.168.35.45.36329: P 28906:29640(734) ack 3989 win 180 <nop,nop,timestamp 1206895728 2077641435>
11:55:02.379416 IP 192.168.35.45.36329 > psweb0.mcns-tel-ret: P 3989:4160(171) ack 29640 win 501 <nop,nop,timestamp 2077641435 1206895728>
.........
前提这是局域网，ntpdate都是相同的。
11:55分err log出现楼主提示的错误，同时解析包数据出现上面的数据，说明db主机35.39在向client端35.45相互发送大量的数据，但是db主机的发送的数据包远远大于client发送的数据包。这有可能导致client请求db主机的packet出现损坏，解析时候报错，这种错误其实很底层的错误。

修改my.cnf参数：
[mysqld]
max_allowed_packet = 256M
重启生效

或者
SET GLOBAL max_allowed_packet = 1024 * 1024 * 256;
此时发现err log中依然报错以上错误。

mysql> select @@session.max_allowed_packet, @@global.max_allowed_packet ;
+------------------------------+-----------------------------+
| @@session.max_allowed_packet | @@global.max_allowed_packet |
+------------------------------+-----------------------------+
|                   268435456 |                268435456 |
+------------------------------+-----------------------------+
1 row in set (0.00 sec)

此时session级别的max_allowed_packet 继承了global的max_allowed_packet大小，都是256M大小。

mysql> select RPAD('hi',100000000,'a');
ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes

但是这里查询100000000/1024/1024=94M大小的返回包就照样报错。
由此可见，cli的mysql的max_allowed_packet不受global 的影响。
如果cli要设置max_allowed_packet大小，请用 mysql --max-allowed_packet=xxxxx -u -p

mysql> select @@session.max_allowed_packet, @@global.max_allowed_packet ;
+------------------------------+-----------------------------+
| @@session.max_allowed_packet | @@global.max_allowed_packet |
+------------------------------+-----------------------------+
|                   268435456 |                268435456 |
+------------------------------+-----------------------------+
1 row in set (0.00 sec)

mysql> select RPAD('hi',100000000,'a');
ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes

mysql> select @@session.max_allowed_packet, @@global.max_allowed_packet；
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 70
Current database: test

此时db connect断开，这个错误就是err log的错误，但是因为mysql内部的机制，会自动重连。

# mysql -u  -p  -h192.168.35.39 -P3311  -Dtest --max-allowed-packet=268435456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 131

mysql> select @@session.max_allowed_packet, @@global.max_allowed_packet ;
+------------------------------+-----------------------------+
| @@session.max_allowed_packet | @@global.max_allowed_packet |
+------------------------------+-----------------------------+
|                   268435456 |                268435456 |
+------------------------------+-----------------------------+
1 row in set (0.00 sec)

mysql> pager cat > /dev/null
PAGER set to 'cat > /dev/null'

mysql> select RPAD('hi',100000000,'a');
1 row in set (1.05 sec)
可以返回结果。

ftc007 · 发表于 2014-8-15 17:20

mysql --max-allowed_packet=268435456 -uroot -p*********
我用这个命令设这个参数，好像也是无效，运行这个命令是直接至mysql>模式而已，也没提示修改成功，我的是MYSQL5.5,不知有没一些不同之处