mysql statement based replication - unsafe statements

I have recently set up statement based replication and my error logs are filling up with this error


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

and


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 state that this is unsafe because:


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.

I simply don't understand what this means. Can someone explain this to me? I'm wondering if this is a huge problem that will cause data differences on the slave or a warning I can simply ignore. Unfortunately these errors are filling the logs qui

It will probably cause differences on the slave in the long run. I've had the same warnings (but with INSERT DELAYED, so your mileage may vary), and on a couple of write intensive tables the data would be different a couple of times a week (leading to a lenghty dump and import).

The ordering of some things is not necessarily the same on every mysql server, so you might end up with the same data in a different order. This is what "not deterministic" means, you execute the exact same query, but you might end up with different results.

If your mysql version supports the MIXED mode for binlogging, I'd definitely use that.
As you can see on http://dev.mysql.com/doc/refman/5.1/en/binary-log-formats.html, the only reason it's not MIXED by default is backward compatibility:


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 mode uses STATEMENT mode binlogging (the default), unless it knows the query is non deterministic, then it'll switch to ROW mode for those queries. With ROW mode, it simply sends the changed rows if any, so that's deterministic again.

StackExchange Database Administrators Q#31344, answer score: 3