Uploaded image for project: 'Debezium'
  1. Debezium
  2. DBZ-741

MySQL DATETIME Value Incorrectly Snapshotted

    Details

    • Steps to Reproduce:
      Hide

      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.

      Show
      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.

      Description

      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.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                jlevin2 Joseph Levin
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: