免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 1785 | 回复: 4

Mysql错误导致业务无法应用 [复制链接]

论坛徽章:
0
发表于 2012-05-08 12:04 |显示全部楼层
mysql错误日志如下:
120508  1:00:17 [Warning] Statement may not be safe to log in statement format. Statement: delete from history where itemid=117137 limit 500
120508  1:00:17 [Warning] Statement may not be safe to log in statement format. Statement: delete from history where itemid=117138 limit 500
120508  1:00:18 [Warning] Statement may not be safe to log in statement format. Statement: delete from history where itemid=117139 limit 500
120508  1:00:18 [Warning] Statement may not be safe to log in statement format. Statement: delete from history where itemid=117140 limit 500
120508  1:00:18 [Warning] Statement may not be safe to log in statement format. Statement: delete from history where itemid=117141 limit 500
120508  1:00:18 [Warning] Statement may not be safe to log in statement format. Statement: delete from history where itemid=117142 limit 500
120508  1:00:18 [Warning] Statement may not be safe to log in statement format. Statement: delete from history where itemid=117143 limit 500
120508  1:00:18 [Warning] Statement may not be safe to log in statement format. Statement: delete from history where itemid=117149 limit 500
120508  2:00:18 [Warning] Aborted connection 37 to db: 'n_hist' user: 'slave' host: 'localhost' (Got an error reading communication packets)
120508  2:00:18 [Warning] Aborted connection 18 to db: 'n_hist' user: 'slave' host: 'localhost' (Got an error reading communication packets)
120508  2:00:18 [Warning] Aborted connection 46 to db: 'n_hist' user: 'slave' host: 'localhost' (Got an error reading communication packets)
120508  2:00:18 [Warning] Aborted connection 25 to db: 'n_hist' user: 'slave' host: 'localhost' (Got an error reading communication packets)
120508  2:00:18 [Warning] Aborted connection 52 to db: 'n_hist' user: 'slave' host: 'localhost' (Got an error reading communication packets)
120508  2:00:18 [Warning] Aborted connection 11 to db: 'n_hist' user: 'slave' host: 'localhost' (Got an error reading communication packets)
120508  2:00:18 [Warning] Aborted connection 17 to db: 'n_hist' user: 'slave' host: 'localhost' (Got an error reading communication packets)
120508  2:00:18 [Warning] Aborted connection 36 to db: 'n_hist' user: 'slave' host: 'localhost' (Got an error reading communication packets)
120508  2:00:18 [Warning] Aborted connection 34 to db: 'n_hist' user: 'slave' host: 'localhost' (Got an error reading communication packets)
120508  2:00:18 [Warning] Aborted connection 16 to db: 'n_hist' user: 'slave' host: 'localhost' (Got an error reading communication packets)
120508  2:00:18 [Warning] Aborted connection 9 to db: 'n_hist' user: 'slave' host: 'localhost' (Got an error reading communication packets)
120508  2:00:18 [Warning] Aborted connection 33 to db: 'n_hist' user: 'slave' host: 'localhost' (Got an error reading communication packets)
120508  2:00:18 [Warning] Aborted connection 28 to db: 'n_hist' user: 'slave' host: 'localhost' (Got an error reading communication packets)
120508  2:00:18 [Warning] Aborted connection 39 to db: 'n_hist' user: 'slave' host: 'localhost' (Got an error reading communication packets)
120508  2:00:18 [Warning] Aborted connection 5 to db: 'n_hist' user: 'slave' host: 'localhost' (Got an error reading communication packets)


这个时候前台应用(LAMP环境)无法登录,我只能重启数据库后恢复正常,请问大家,这两错误类型那种影响数据库的运行状态导致前台应用无法使用??

论坛徽章:
9
每日论坛发贴之星
日期:2016-01-04 06:20:00数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00每日论坛发贴之星
日期:2016-01-04 06:20:00数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00IT运维版块每日发帖之星
日期:2016-01-04 06:20:00IT运维版块每日发帖之星
日期:2016-01-04 06:20:00综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00数据库技术版块每周发帖之星
日期:2016-03-07 16:30:25
发表于 2012-05-08 13:00 |显示全部楼层
本帖最后由 cenalulu 于 2012-05-08 13:01 编辑

120508  1:00:18 [Warning] Statement may not be safe to log in statement format. Statement: delete from history where itemid=117149 limit 500
第一种warning,没啥影响,就是提示你delete+limit的方式用statement 复制是可能会导致主从不一致的。

120508  2:00:18 [Warning] Aborted connection 5 to db: 'n_hist' user: 'slave' host: 'localhost' (Got an error reading communication packets)
第二种warning,没有遇到过,字面意思上是网络丢包,不过从报错来看host= localhost 应该不是根本原因

论坛徽章:
0
发表于 2012-05-08 13:47 |显示全部楼层
回复 2# cenalulu


第一种情况,我将binlog_format修改成mixed后,主从同步就报错,错误代码为:1032


第二种情况,它如果出现过多会影响前台业务访问,这是最要命的

论坛徽章:
2
摩羯座
日期:2014-05-29 17:38:40数据库技术版块每日发帖之星
日期:2016-08-05 06:20:00
发表于 2012-05-08 13:49 |显示全部楼层
第一个,如果不解决,你的主从会变得没有意义。
第二个,和用户权限有关,奇怪的是你的slave是从本地连接的?

论坛徽章:
9
每日论坛发贴之星
日期:2016-01-04 06:20:00数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00每日论坛发贴之星
日期:2016-01-04 06:20:00数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00IT运维版块每日发帖之星
日期:2016-01-04 06:20:00IT运维版块每日发帖之星
日期:2016-01-04 06:20:00综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00数据库技术版块每周发帖之星
日期:2016-03-07 16:30:25
发表于 2012-05-08 14:06 |显示全部楼层
回复 3# oulinhl


    Q1: 那说明你的主从极有可能已经不同步了,在业务低峰时用pt-table-checksum做个主从比较,或者手工比较。
    Q2: 查看下那个时段的/var/log/message确认系统是否有报错,或者看看网络是否有抖动,亦或者client有主动的强制kill thread
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP