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

TINYINT(1) value range restricted on snapshot.

XMLWordPrintable

    • Hide

      Create a table:

      CREATE TABLE test.Test (
    id int not null primary key auto_increment,
    a TINYINT(1) not null,
    b TINYINT(1),
    c TINYINT(2) not null,
    d TINYINT(2)
);

      Insert a couple of rows:

      INSERT INTO test.Test VALUES (NULL, 5, 5, 5, 5), (NULL, 6, NULL, 6, NULL);

      Start a debezium connector, it takes a snapshot, and gets the following into kafka:

      Schema:

      "fields": [
  { "name": "id", "type": "int" },
  { "name": "a", "type": { "type": "int", "connect.type": "int16" } },
  { "name": "b", "type": [ "null", { "type": "int", "connect.type": "int16" } ], "default": null },
  { "name": "c", "type": { "type": "int", "connect.type": "int16" } },
  { "name": "d", "type": [ "null", { "type": "int", "connect.type": "int16" } ], "default": null }
]

      Messages:

      { "id": 1, "a": 1, "b": { "int": 1 }, "c": 5, "d": { "int": 5 } }
{ "id": 2, "a": 1, "b": null, "c": 6, "d": null }

      Expected: The TINYINT(1) columns "a" and "b" should be set to 5, {"int": 5} and 6, null respectively, however the numerical values are limited to 1 instead.

      Now that debezium/connect is running, insert another row:

      INSERT INTO test.Test VALUES (NULL, 7, 7, 7, 7);

      And get a new message on the topic:

      { "id": 3, "a": 7, "b": { "int": 7 }, "c": 7, "d": { "int": 7 } }

      Here, after the snapshot, the column values are interpreted correctly.

      Show
      Create a table: CREATE TABLE test.Test ( id int not null primary key auto_increment , a TINYINT (1) not null , b TINYINT (1), c TINYINT (2) not null , d TINYINT (2) ); Insert a couple of rows: INSERT INTO test.Test VALUES ( NULL , 5, 5, 5, 5), ( NULL , 6, NULL , 6, NULL ); Start a debezium connector, it takes a snapshot, and gets the following into kafka: Schema: "fields" : [ { "name" : "id" , "type" : " int " }, { "name" : "a" , "type" : { "type" : " int " , "connect.type" : "int16" } }, { "name" : "b" , "type" : [ " null " , { "type" : " int " , "connect.type" : "int16" } ], " default " : null }, { "name" : "c" , "type" : { "type" : " int " , "connect.type" : "int16" } }, { "name" : "d" , "type" : [ " null " , { "type" : " int " , "connect.type" : "int16" } ], " default " : null } ] Messages: { "id" : 1, "a" : 1, "b" : { " int " : 1 }, "c" : 5, "d" : { " int " : 5 } } { "id" : 2, "a" : 1, "b" : null , "c" : 6, "d" : null } Expected: The TINYINT(1) columns "a" and "b" should be set to 5, {"int": 5 } and 6, null respectively, however the numerical values are limited to 1 instead. Now that debezium/connect is running, insert another row: INSERT INTO test.Test VALUES ( NULL , 7, 7, 7, 7); And get a new message on the topic: { "id" : 3, "a" : 7, "b" : { " int " : 7 }, "c" : 7, "d" : { " int " : 7 } } Here, after the snapshot, the column values are interpreted correctly.

      During the snapshot phase, the values of TINYINT(1) columns that are imported will be constrained to the values of NULL for null columns, int16(0) for zero values or int16(1) for all other values.

      After the snapshot is complete, future updates in the source database are treated correctly and are unconstrained in value.

              jpechane Jiri Pechanec
              norganna Ken Allan (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Created:
                Updated:
                Resolved: