Status: Closed (View Workflow)
Affects Version/s: 0.7.5
Fix Version/s: 0.8.0.Beta1
Steps to Reproduce:
1) Run Debezium in a different time zone the MySQL.
2) Perform a snapshot on a table with DATETIME values.
3) Modify a row in the table without modifying the DATETIME value.
4) The snapshot entry and binlog entries should have the same DATETIME values but don't.
Git Pull Request:
When running a snapshot of a MySQL database DATETIME values are time shifted based on the timezone Debezium is running in.
In the scenario we hit we are running Debezium in US East Coast. We have a DATETIME value of 1515836907 (01/13/2018 @ 9:48am (UTC)) in the database but we are getting the value 1515818907 (01/13/2018 @ 4:48am (UTC)) in the Snapshot event generated. Modification of the row, without modifying the value, correctly generates a binlog event with both the before and after value equal to 1515836907.
The error, the time shift, appears to occur in Timestamp.toEpochMillis. It's not clear to me what the fix would be but it's not clear why this call needs to be made in this way. Types.TIMESTAMP in MySQL is only ever mapped to DATETIME and in MySQL DATETIME values are returned from the database as is, no adjustment for timezone, so it's not clear if the adjustment for time zone would ever be appropriate for Types.TIMESTAMP in the MySQL use case.
The simplest possible fix appears to be to add Types.TIMESTAMP as an override in MySqlValueConverters.converter (as is done by a few other types) rather then using the default JdbcValueConverters logic to avoid impacting any other databases.