CODEWITHSUNDEEP
mysqldatabase-replication
Leo Bosnjak
Leo Bosnjak

Reputation: 33

mysql - invalid replication timestamps

I've setup a master-master replication between two mysql-servers (v8.0.12) that run on two windows machines in the same LAN.

The replication seems to work fine so far. However, there is a problem: The error-log is being filled with these entries:

2019-07-04T11:17:54.694565+01:00 5 [Warning] [MY-010956] [Server] Invalid replication timestamps: original commit timestamp is more recent than the immediate commit timestamp. This may be an issue if delayed replication is active. Make sure that servers have their clocks set to the correct time. No further message will be emitted until after timestamps become valid again.
2019-07-04T11:17:54.696796+01:00 5 [Warning] [MY-010957] [Server] The replication timestamps have returned to normal values.
2019-07-04T11:17:54.702435+01:00 5 [Warning] [MY-010956] [Server] Invalid replication timestamps: original commit timestamp is more recent than the immediate commit timestamp. This may be an issue if delayed replication is active. Make sure that servers have their clocks set to the correct time. No further message will be emitted until after timestamps become valid again.
2019-07-04T11:17:54.704768+01:00 5 [Warning] [MY-010957] [Server] The replication timestamps have returned to normal values.
2019-07-04T11:17:54.711469+01:00 5 [Warning] [MY-010956] [Server] Invalid replication timestamps: original commit timestamp is more recent than the immediate commit timestamp. This may be an issue if delayed replication is active. Make sure that servers have their clocks set to the correct time. No further message will be emitted until after timestamps become valid again.
2019-07-04T11:17:54.713675+01:00 5 [Warning] [MY-010957] [Server] The replication timestamps have returned to normal values.
2019-07-04T11:17:54.716626+01:00 5 [Warning] [MY-010956] [Server] Invalid replication timestamps: original commit timestamp is more recent than the immediate commit timestamp. This may be an issue if delayed replication is active. Make sure that servers have their clocks set to the correct time. No further message will be emitted until after timestamps become valid again.
2019-07-04T11:17:54.718956+01:00 5 [Warning] [MY-010957] [Server] The replication timestamps have returned to normal values.

Server 1 acts as windows time-server for Server 2, and as far as I can see both servers have the same time (and timezone of course). As they are in the same LAN, there is no obvious network lag between them (ping < 1ms). @@global.time_zone variable returns 'SYSTEM'. However, I've not set any timezone in the my.ini file. SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP) returns '02:00:00'. I've NOT setup any replication delay, because I'm interested in a fast propagation of changes.

I'm pretty desperate because the error-logs fill up to a size > 5 GB in a months time. Has anybody an idea how to further analyze the problem (eg any mysql internal table that shows the current time delay)?

Thanks for any hints, Leo

Upvotes: 3

Views: 5000

Answers (1)

Paul Lupa
Paul Lupa

Reputation: 46

MySQL is sensitive to very minor clock differences. See Bug/Enhancement here:

https://bugs.mysql.com/bug.php?id=95422

You should verify that your clocks are syncronized, and take the warning as such a warning.

Upvotes: 3

Related Questions