Thesql statement based replication – unsafe statements

linuxMySQLreplication

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?

Best Answer

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.

Related Solutions

MySQL Replication – Introduce new Slave to replication

Is this an issue with the fact now() is being in a query, which common sense tells me would cause issues if the inserts on that table were frequent enough??!?!

Yes, I believe this is the cause of your errors. Your method for introducing the new slave seems to be correct. It is quite strange in my opinion to define a table with a DATETIME field as the Primary Key. As you've quite rightly pointed out, the slave gets the replicated queries from the master and they will use the now() keyword in the queries, which will grab the timestamp from the local server.

Really, the table should be defined with some other data type for the PK (such as INT or BIGINT) which can be guaranteed to be unique, unlike a timestamp inserted with now().

Mysql – the safest way to switch the binlog format at runtime

Since a binlog will have a specific format at the moment you do this, you may decide not to gamble with the two formats together although MySQL (eh Oracle [still can't roll off my tongue]) built this feature.

To play it totally safe without a mysql restart, try the following:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

This will leave the last binlog in the 'MIXED' format. The penultimiate (next to last) binlog exists merely bring closure the last binlog that was in the previous format.

All existing sessions prior to the first FLUSH LOGS; will start writing in the last binlog once UNLOCK TABLES; is executed.

Give it a Try !!!

CAVEAT

Giving credit where credit is due, my answer is really piggybacking off of @Jonathan's answer. I just close and open binlogs on top of that. He gets a +1 for bringing this out first.

UPDATE 2011-10-12 13:58 EDT

If you do this to an active Master and there are one or more Slaves replicating from that Master, you need to be concerned about the relay logs being in the new format as well. Here is what you can do:

On the Slave, run STOP SLAVE;

On the Master run these:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

On the Slave, run START SLAVE;

Running STOP SLAVE; and START SLAVE; rotates the relay logs and causes the new entries to be replicated whichever format it comes. You may want to apply the binlog_format change in the slave as well.

Best Answer

Related Solutions

MySQL Replication – Introduce new Slave to replication

Mysql – the safest way to switch the binlog format at runtime

Related Question