我最近设置了基于语句的复制,我的错误日志正在填补此错误

121231 21:10:55 [Warning] Unsafe statement written to the binary log
using statement format since BINLOG_FORMAT = STATEMENT. INSERT… ON
DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is
unsafe Statement: INSERT INTO bunching (route_number, vehicle_name,
time, status, direction, eta) VALUES (11, ‘223’, -1.0, 1, -1, 99999.0)
ON DUPLICATE KEY UPDATE route_number = 11, time = -1.0, status =1,
direction = -1, eta = 99999.0

和

121231 21:10:55 [Warning] Unsafe statement written to the binary log
using statement format since BINLOG_FORMAT = STATEMENT. INSERT… ON
DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is
unsafe Statement: INSERT INTO vehicleETA (routes_id, vehicleNum,
relEta, relStopID, lat, lng, bearing, online, msg, lastReport,
direction, inETA) VALUES (18, 2012, 39.70092857098426, 7, 29.970915,
-90.09537, 2.2320573776159844, 1, ”, ‘2012-12-31 21:10:55’,1, 1 ) ON DUPLICATE KEY UPDATE routes_id = 18, relEta = 39.70092857098426,
relStopID = 7, lat = 29.970915, lng = -90.09537, bearing =
2.2320573776159844, online = 1, msg = ”, lastReport = ‘2012-12-31 21:10:55’, direction =1, inETA = 1, GMT = ‘031052’, lastPass = 0

Mysql Docs声明这是不安全的,因为：

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 MySQL Server depends.

我根本不明白这意味着什么.谁可以给我解释一下这个？我想知道这是一个巨大的问题,会导致奴隶数据差异或我可以忽略的警告.不幸的是,这些错误很快就填满了日志.这个数据是相对短暂的和即时通讯

我可以选择

>将记录模式更改为“混合”
>关闭警告(忽略这些警告)

或者我们可以改变查询.有什么建议吗？

解决方法:

从长远来看,它可能会导致奴隶的差异.我有相同的警告(但INSERT DELAYED,所以你的里程可能会有所不同),并且在几个写密集表上,数据每周会有几次不同(导致长度转储和导入).

某些东西的排序在每个mysql服务器上都不一定相同,因此您可能会以不同的顺序得到相同的数据.这就是“不确定”意味着,您执行完全相同的查询,但最终可能会得到不同的结果.

如果您的mysql版本支持binlogging的MIXED模式,我肯定会使用它.
正如您在http://dev.mysql.com/doc/refman/5.1/en/binary-log-formats.html上看到的那样,默认情况下它不是MIXED的唯一原因是向后兼容性：

Support for row-based logging was added in MySQL 5.1.5. Mixed logging
is available beginning with MySQL 5.1.8. In MySQL 5.1.12, MIXED become
the default logging mode; in 5.1.29, the default was changed back to
STATEMENT for compatibility with MySQL 5.0.

MIXED模式使用STATEMENT模式binlogging(默认值),除非它知道查询是非确定性的,然后它将切换到这些查询的ROW模式.使用ROW模式,它只是发送已更改的行(如果有的话),这样再次确定.