mk-table-checksum fails due to "on update CURRENT_TIMESTAMP"

Polyglot Projects Other Tools
1

I did a sqldump from a slave to another slave. On the master, we’re running mk-table-checksum. The new slave is showing as replicating correctly and from what I can tell it has to do with some tables having timestamp fields with an “on update CURRENT_TIMESTAMP” trigger. This causes the data in the slave to contain different values than the master and the checksum fails.

Is this an accurate assumption?

What is the solution?

One solution would be to run an update query against the master system to update the data in these fields to itself, but we’re dealing with millions of records and this of course will take time.

Is there a better way?

2

Actually, maybe its a different issue… if the servers we have are set up for a different timezone, then the timestamp fields will show a different time and that may be the cause of the difference?

just taking a stab at it here…

3

I was able to fix the timestamp issue by changing the slave’s timezone to match that of the master. Had to restart the slave for the the system timezone to take effect. However, the same issue exists for fields of “datetime” values.

I read somewhere that the datetime field is not affected by the timezone setting of mysql. Obviously it was affected by it during the load / dump of the data.