mysql statement based replication - unsafe statements

Question

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 quite quickly. This data is relatively ephemeral and im

I have the option of

• changing the logging mode to "mixed"
• turning off warnings (ignoring these warnings)

Or we can alter the queries. Any advice here?

Answer 1

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.