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

Timestamp / datetime data types do not handle zero dates properly

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Unresolved
    • Icon: Critical Critical
    • under-triaging
    • 1.8.1.Final
    • mysql-connector
    • None
    • False
    • None
    • False
    • Hide

      CREATE TABLE `t3` (
        `c1` int DEFAULT NULL,
        `c2` varchar(255) DEFAULT NULL,
        `c3` datetime DEFAULT CURRENT_TIMESTAMP,
        `c4` timestamp NULL DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
      SET SQL_MODE='';
      insert into test.t3 values (1,'abc', '0000-00-00', '0000-00-00');
      Query OK, 1 row affected (0.00 sec)
      mysql> select * from test.t3 where c4 = '0000-00-00';
      ------------------------------------------------+

      c1        c2      c3                            c4                           

      ------------------------------------------------+
       |    1    | abc    | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 
      ------------------------------------------------+

      You will notice that the datetime gives a value of 0, which I interpret as '0000-00-00' but I think you are treating that zero as a unix timestamp.

      Show
      CREATE TABLE `t3` (   `c1` int DEFAULT NULL,   `c2` varchar(255) DEFAULT NULL,   `c3` datetime DEFAULT CURRENT_TIMESTAMP,   `c4` timestamp NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; SET SQL_MODE=''; insert into test.t3 values (1,'abc', '0000-00-00', '0000-00-00'); Query OK, 1 row affected (0.00 sec) mysql> select * from test.t3 where c4 = '0000-00-00'; ----- ---- ------------------- --------------------+ c1        c2      c3                            c4                            ----- ---- ------------------- --------------------+  |    1    | abc    | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |  ----- ---- ------------------- --------------------+ You will notice that the datetime gives a value of 0, which I interpret as '0000-00-00' but I think you are treating that zero as a unix timestamp.

      When SQL_MODE='' (any SQL mode without NO_ZERO_IN_DATETIME basically) it is possible to insert "zero" dates.  These dates are interpreted as the unixtime epoch (1970-01-01) instead of the "zero" date they are supposed to be.

      Here is the debug output from my connector:
      Mar 21, 2022 10:02:54 PM com.github.shyiko.mysql.binlog.BinaryLogClient connect
      INFO: Connected to 127.0.0.1:3306 at 3b0f51ee-a08e-11ec-86ff-000c29221572:1-34 (sid:8192, cid:150)
      SQL: START TRANSACTION;
      RECORD:
      Struct{after=Struct

      {c1=1,c2=abc,c4=1970-01-01T00:00:00Z}

      Field{name=c4, index=3, schema=Schema{io.debezium.time.ZonedTimestamp:STRING}}
      SQL: INSERT INTO `leapdb`.`mvlog_e1effbbe63d0a9b6aa4378aac07f29c8` VALUES (1, @fv_uow_id, 999, 160, 1 , 'abc' , '0000-00-00' , '1970-01-01');
      SQL: COMMIT;

      Data in change data capture table is wrong:
      mysql> select * from mvlog_e1effbbe63d0a9b6aa4378aac07f29c8;
      --------------------------------------------------------------------------------+

      dml_type uow_id fv$server_id fv$gsn c1   c2   c3                   c4                  

      --------------------------------------------------------------------------------+

             1     50          999    175    1 abc   0000-00-00 00:00:00 1970-01-01 00:00:00

      --------------------------------------------------------------------------------+
      1 row in set (0.00 sec)

            Unassigned Unassigned
            greenlion@gmail.com Justin Swanhart (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated: