1

When using master-master/master-slave replication in a MySQL environment, there's a possibility of creating inconsistent data when SQL statements use the sysdate instead of a hardcoded time in their SQL statements.

The issue that could potentially crop up is that if replication is halted for whatever reason (network blip, rebooting one of the hosts, replication thread halted, etc.) is that the SQL that involves sysdate will be turned into a real time at the time of the SQL being applied to the database. If replication is halted for meaningful time (1 hour to 1 day), the 2 databases will no longer be entirely synchronous. A row with a timestamp may begin to escape application workflows based on date-time boundaries.

What potential workarounds/remedies are there for finding such rows and updating them accordingly as a post-step to restoring replication between the databases? I'm almost certain we could use Veridata but that would require quite a bit of overhead, and I'm looking for a more elegant/simple solution to the issue.

1

Virtually everything that is replicated is frozen before replication. For example, RAND, NOW, UNIX_TIMESTAMP are all passed through the replication stream so that the Slave will have exactly what the Master has.

But... SYSDATE is an exception. You should not use it if you want identical data.

Or... "sysdate-is-now" is an option (either on the server commandline or via some setting). This makes SYSDATE act like NOW.

What version are you running? See http://bugs.mysql.com/bug.php?id=15101

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.