Mysql 经常宕机 日志显示 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.

最近数据库老是自动宕机挂掉,然后查看mysql.log发现下面的日志:

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.

问题原因
查了下原因,longxibendi这个表上有2个唯一键。则使用 INSERT… ON DUPLICATE KEY UPDATE ,且当前数据库binlog_format是statement格式,这种sql语句就会报unsafe。

查了下手册

INSERT … ON DUPLICATE KEY UPDATE statements on tables with multiple primary or unique keys. When executed against a table that contains more than one primary or unique key, this statement is considered unsafe, being sensitive to the order in which the storage engine checks the keys, which is not deterministic, and on which the choice of rows updated by the Server depends.

An INSERT … ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is marked as unsafe for statement-based replication beginning with mysql 5.6.6. (Bug #11765650, Bug #58637)

http://dev.mysql.com/doc/refman/5.6/en/replication-rbr-safe-unsafe.html

看官方解释,是 server层把数据传给innodb引擎,innodb引擎检查key值比较敏感造成的。
个人理解,可能与server与innodb每次只传1行数据有关。

两种解决办法:
1.修改binlog_format格式为mixed;
登陆mysql,执行 set global binlog_format=MIXED;
2.不要使用这类sql;

搬瓦工,年付$49.99CN2高速线路,1024MB内存/1000GB流量/1GB带宽,电信联通优化KVM,延迟低,速度快,建站稳定,搬瓦工BandwagonHost VPS优惠码BWH26FXH3HIQ,支持<支付宝> 【点击购买】!

Vultr月付$3.5日本节点,512M内存/500G流量/1G带宽,电信联通优化,延迟低,速度快【点击购买】!

搬瓦工1111特惠,年付$29.882核CPU/2048MB内存/1TB流量/1GB带宽,随意切换机房KVM,延迟低,速度快,建站稳定,搬瓦工BandwagonHost VPS优惠码BWH26FXH3HIQ,支持<支付宝> 【点击购买】!

点赞

发表评论

电子邮件地址不会被公开。 必填项已用*标注

Scroll Up