CODEWITHSUNDEEP
mysqlreplication
Xavier Maillard
Xavier Maillard

Reputation: 958

binlog_format = STATEMENT and CURRENT_TIMESTAMP with MySQL replication

can a MySQL slave instance have different row values for the same ID when binlog_format is set to STATEMENT and we insert something like:

insert into foo values(CURRENT_TIMESTAMP)

As I understand it, the slave read the SQL statement and execute it thus, if the replication is lagging, could lead to differences for the same row. Right or wrong ?

How can I avoid this situation ?

Thank you.

Upvotes: 4

Views: 2967

Answers (1)

Ike Walker
Ike Walker

Reputation: 65537

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.

Upvotes: 5

Related Questions