ansaurus

Question

binlog_format = STATEMENT and CURRENT_TIMESTAMP with MySQL replication

Answer 1

+1 A:

Your approach is perfectly safe in statement level replication. The TIMESTAMP is written to the binary log, so the value for CURRENT_TIMESTAMP will be consistent across the master and the slave even if the slave is behind. You can also use the NOW() function safely for the same reason.

The function to avoid is SYSDATE(), which will not use the TIMESTAMP from the binary log, and therefore the slave's value will represent when the statement ran on the slave, rather than when the statement ran on the master.

Ike Walker 2010-05-18 23:48:32

Ok. Got it. All non-deterministic functions (whatever we name a function) is bad when using STATEMENT based replication. If I read it right, we should go for either MIXED or full ROW replication. The former has my preference though since it can combine pros of both worlds. Thank you !

Xavier Maillard 2010-05-19 21:48:53

There are ways to make non-deterministic functions work with statement-level replication in some cases. I use MySQL 5.0, so statement-level replication is my only option. If I want to insert a value from a non-deterministic function, I just assign it to a local variable first, and it is replicated properly. For example, instead of `insert into test.uuids values(uuid());` I do `set @uuid = uuid(); insert into test.uuids values(@uuid);` to get the same value on the master and slave DB.

Ike Walker 2010-05-20 14:54:40

ansaurus

tags:

views:

answers:

binlog_format = STATEMENT and CURRENT_TIMESTAMP with MySQL replication

related questions