Styxis
Company
Knowledge

MySQL snapshot shifts: separate DATETIME fields from the JVM calendar

Separate stored fields of a timezone-free DATETIME from the JVM calendar conversion.

Preserve stored components first

Separate stored fields of a timezone-free DATETIME from the JVM calendar conversion. Compare snapshot and streaming year, month, day and time against the stored components. The Truthound reference is the agreed local value, not an invented UTC instant; attaching a timezone can obscure the original problem. [1, 2, 3]

Distinguish calendar shifts

Debezium #2921 reports MySQL and MariaDB snapshot shifts through a lenient Calendar, including a DST gap, historical dates and invalid-date handling that differs from streaming. It covers main and specified driver configurations. UTC can avoid the DST case but does not establish a remedy for the historical calendar shift. Invalid-date fail and warn paths need separate handling. [1]

Compare phases

Insert the same DATETIME before and after snapshot, compare JVM timezone conditions, and isolate initial-load rows whose source fields moved. Distinguish DATETIME from TIMESTAMP and retain phase, JVM zone, driver and time.precision.mode. Keep original components rather than only a normalized result. [1, 3]

An independent 02:30:00 reference

The illustrative stored components are 2024-03-10T02:30:00. This contract does not convert them into an instant, so the expected components remain unchanged. Preserving 02:30:00 meets the condition; shifting to 03:30:00 fails. The fixture does not define a new admission policy for nonexistent local instants. [1, 3]

Preparation before equality

Truthound Core Python SDK 3.3.2 can compare non-missing stored_local_datetime and snapshot_local_datetime representations through ColumnPairValidator with relationship="==". The operator extracts components without loss and checks calendar validity separately. The SDK does not repair JDBC Calendar behavior or choose invalid-date handling. [1, 2, 3]

Different contracts for instants and fields

Test a supported LocalDateTime read path against preserved source components, and handle invalid dates under the approved fail or warn policy. A TIMESTAMP representing an actual UTC instant needs an instant contract instead of component equality. Removing timezones indiscriminately would introduce a different error. [1, 3]

Application conditions

Distinguish DATETIME from TIMESTAMP and retain raw components, JVM zone, driver and time.precision.mode.

Alternatives

Test a supported LocalDateTime read correction and isolate invalid dates under the approved policy.

Limitations

  • The SDK does not repair JDBC Calendar or invalid-date policy; UTC alone does not cover the historical calendar case.

Evidence

Event checked
Subject checked
Modified