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
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?