Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from serverfault.com Jan 3 '13 at 10:01

This question came from our site for professional system and network administrators.

1 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.

share|improve this answer
    
yeah I was thinking to switchto mixed mode anyways... seems the more sensible method. –  Christian Tortorich Jan 2 '13 at 16:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.