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

Incorrect value for datetime field for '0001-01-01 00:00:00'

    Details

      Description

      1. Start the connector with the config -

      {
        "name": "demo-mysql-connector",
        "config": {
          "connector.class": "io.debezium.connector.mysql.MySqlConnector",
          "database.history.kafka.bootstrap.servers": "localhost:9092",
          "database.history.kafka.topic": "debz.schema-changes.demo",
          "database.hostname": "localhost",
          "database.user": "dbarr",
          "database.password": "xxx",
          "database.port": "3306",
          "database.server.id": "999",
          "database.server.name": "debz-demo",
          "name": "demo-mysql-connector",
          "database.whitelist": "demo",
          "tasks.max": "1"
        }
      } 
      

      2. Create a table with json column NOT NULL field.

      CREATE TABLE `ts_test` (
        `str` varchar(100) DEFAULT NULL,
        `ts` datetime DEFAULT NULL
      );
      

      3. Disable mysql strict mode if enabled.

      4. Insert a row with datetime value as '0001-01-01 00:00:00'

      INSERT INTO `demo`.`ts_test` (`str`, `ts`) VALUES ( 'row1', '0001-01-01 00:00:00');
      

      5. Check kafka event

      {"schema":{"type":"struct","fields":[{"type":"struct","fields":[{"type":"string","optional":true,"field":"str"},{"type":"int64","optional":true,"name":"io.debezium.time.Timestamp","version":1,"field":"ts"}],"optional":true,"name":"debz_demo.demo.ts_test3.Value","field":"before"},{"type":"struct","fields":[{"type":"string","optional":true,"field":"str"},{"type":"int64","optional":true,"name":"io.debezium.time.Timestamp","version":1,"field":"ts"}],"optional":true,"name":"debz_demo.demo.ts_test3.Value","field":"after"},{"type":"struct","fields":[{"type":"string","optional":true,"field":"version"},{"type":"string","optional":false,"field":"name"},{"type":"int64","optional":false,"field":"server_id"},{"type":"int64","optional":false,"field":"ts_sec"},{"type":"string","optional":true,"field":"gtid"},{"type":"string","optional":false,"field":"file"},{"type":"int64","optional":false,"field":"pos"},{"type":"int32","optional":false,"field":"row"},{"type":"boolean","optional":true,"default":false,"field":"snapshot"},{"type":"int64","optional":true,"field":"thread"},{"type":"string","optional":true,"field":"db"},{"type":"string","optional":true,"field":"table"},{"type":"string","optional":true,"field":"query"}],"optional":false,"name":"io.debezium.connector.mysql.Source","field":"source"},{"type":"string","optional":false,"field":"op"},{"type":"int64","optional":true,"field":"ts_ms"}],"optional":false,"name":"debz_demo.demo.ts_test3.Envelope"},"payload":{"before":null,"after":{"str":"row5","ts":978307200000},"source":{"version":"0.8.1.Final","name":"debz-demo","server_id":1,"ts_sec":1550216193,"gtid":null,"file":"mysql-bin.000020","pos":2202,"row":0,"snapshot":false,"thread":5,"db":"demo","table":"ts_test3","query":null},"op":"c","ts_ms":1550216193991}}
      

      As you can see, the value for ts = 978307200000. This ts translates to 2001-01-01 00:00:00.

      I checked the mysql documentation for datetime column values and I found that the allowed range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'

      The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
      

      However, I realized that we are able insert values like '0001-01-01 00:00:00' into the table.
      As I can see in the code, when debezium sees such a timestamp, it gets handled as '2001-01-01 00:00:00'.

      See the below screenshot in a debug freeze :

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                jpechanec Jiri Pechanec
                Reporter:
                deepakbarr Deepak Barr
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: