Status: Closed (View Workflow)
Debian Wheezy, amd64
I have a MariaDB 10.1.22 server as a row-based replication slave from a MariaDB 5.5.55 server. I believe that the problem is internal to the MariaDB 10.1.22 server.
A few rows on the slave have a TIMESTAMP column which is storing the wrong value.
Both systems have their system timezones set to America/Chicago. My application will set the MariaDB session timezone appropriately for whatever it's doing. This ranges across the timezones in the USA, for example, EST5EDT, CST6CDT, MST, etc. The affected rows all would have had their session timezones set to PST8PDT.
Percona's pt-table-checksum identified these inconsistent rows. At first glance, there is no problem. I'll take as my example the row with ID 88809874. Selecting the "created" column (type TIMESTAMP) for that row shows '2016-11-06 01:01:27' both on the master and the slave. Fine.
But that's with the default timezone (Central). When I switch my session to PST8PDT and look again, I see: '2016-11-06 00:01:27' on the master and '2016-11-05 23:01:27' on the slave. [The master's version, shortly after midnight Pacific time, is correct.]
UNIX_TIMESTAMP(created) gives 1478415687 on the master and 1478412087 on the slave. One hour off.
I attempted to fix it by updating the master to increment the created timestamp by one second. Since I'm doing row-based replication, that should cause the right value to propagate to the slave. But it doesn't: on both master and slave the value is incremented by one second, but the hour offset remains.
I checked the master binary log both on the server and the relay log on the slave. In both logs, the created column's value goes from 1478415687 to 1478415688. But SELECT UNIX_TIMESTAMP(created) on the slave still gives 1478412088.
This is why I believe the problem is not related to the master: the relay log does have the correct UNIX timestamp.
The system's timezone (America/Chicago) was undergoing a Daylight Saving shift during that timestamp. '2016-11-06 01:01:28' is ambiguous in the system's timezone: it could refer to either 1478415688 or 1478412088. But in the session timezone, PST8PDT, the correct UNIX time (1478415688) is '2016-11-06 00:01:28', which is unambiguous.
It looks as though the fact that the system's time was ambiguous is "leaking" into the handling of the UNIX timestamp given via replication. But now I'm into the realm of speculation.
This is only happening to a handful of rows out of countless rows that would seem to fit all the criteria. It currently seems to be an issue in 7 rows of one table, and 27 rows of another. I could fix all these problem by setting the timestamp manually on the slave, but I have no problem leaving them as-is in case it's helpful to track down what's going on.
Please let me know what other information I can provide.
- relates to
MDEV-11895 NO_ZERO_DATE affects timestamp values without any warnings