Search code examples
mysqltimestampdatabase-triggerdatabase-cluster

Current_Timestamp in Trigger with Statement Based Replication

We currently use a trigger on our MySQL database that sets a "last-modified" timestamp to CURRENT_TIMESTAMP. It is called on update.

We also need to use statement based reproduction for the cluster.

Is there a way to modify the trigger so that the propagated CURRENT_TIMESTAMP is identical on every cluster instance?

Currently the statement based reproduction calls the statement for every cluster instance, resulting in slightly different timestamps.

Solution

  • You must switch to mixed binlog format to save inside the binlog not only the statement but also the data for non deterministic writes.

    You can do that without service disruption with:

    SET GLOBAL binlog_format = 'MIXED';

    On the master server generating the binlog.