ptrdom opened a new issue, #533: URL: https://github.com/apache/pekko-persistence-r2dbc/issues/533
The MySQL dialect sets the r2dbc-mysql option `connectionTimeZone = SERVER` ([ConnectionFactoryProvider](https://github.com/apache/pekko-persistence-r2dbc/blob/main/core/src/main/scala/org/apache/pekko/persistence/r2dbc/ConnectionFactoryProvider.scala)), so the driver converts `java.time.Instant` to and from the server's local time. When the server time zone observes daylight saving time, the local times in the repeated hour at the end of DST are ambiguous, and `Instant` values in that hour are written and read incorrectly. ## Reproduction MySQL 9.5.0 (the CI image) with `TZ=Europe/Vilnius` (`@@system_time_zone = EET`), r2dbc-mysql 1.4.3. DST ended on 2026-10-25 at 04:00 local time, so 00:30Z and 01:30Z are both 03:30 local time. Rows 1 and 2 are written by binding an `Instant`, as `JournalDao` binds `db_timestamp`. Rows 3 and 4 are the same instants written as UTC literals in a `time_zone = '+00:00'` session, so they are stored correctly. All rows are then read as `Instant`, and `UNIX_TIMESTAMP(ts)` shows the stored value. With `connectionTimeZone = SERVER` (current): | id | written as | expected | stored | read | |---|---|---|---|---| | 1 | bound `Instant` | 00:30Z | **01:30Z** | 00:30Z | | 2 | bound `Instant` | 01:30Z | 01:30Z | **00:30Z** | | 3 | UTC literal | 00:30Z | 00:30Z | 00:30Z | | 4 | UTC literal | 01:30Z | 01:30Z | **00:30Z** | So both directions are affected: an instant in the first occurrence of the repeated hour is stored one hour later, and a value stored in the second occurrence is read back one hour earlier. With `connectionTimeZone = UTC` and `forceConnectionTimeZoneToSession = true`, all four rows are stored and read correctly. ## Impact Only servers whose time zone observes DST are affected, for timestamps in the repeated hour once a year. In that hour `db_timestamp` values of events and durable state can be stored up to one hour later than intended, and read back one hour earlier than stored. Events can therefore be stored out of order relative to each other, which `eventsBySlices` relies on, and the shift is much larger than the backtracking window, so I would expect queries and projections to be able to miss or reorder events in that hour (not verified end to end). Servers running in UTC are not affected. ## Proposed fix Use `connectionTimeZone = UTC` with `forceConnectionTimeZoneToSession = true` for the MySQL driver, so that the session uses UTC, which has no ambiguous local times. The existing comment notes that one of these options is needed as a workaround for https://github.com/asyncer-io/r2dbc-mysql/pull/240. This also makes `NOW(6)` return UTC in plugin sessions, which needs to be checked with the full test suite and documented, as SQL run by users on the plugin's connections (for example in a `ChangeHandler`) would see UTC. The MariaDB dialect in progress uses a UTC session for the same reason, as its driver has no `Instant` support and the plugin converts `Instant` to UTC `LocalDateTime`. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
