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

MySQL Connector Fails to Capture Setting NOT NULL Column to DEFAULT NULL

    XMLWordPrintable

Details

    • Bug
    • Resolution: Unresolved
    • Major
    • under-triaging
    • 1.9.6.Final
    • mysql-connector
    • None
    • False
    • None
    • False

    Description

      Debezium MySQL connector is unable to capture the transition of fields from {column}

      {dataType}

      NOT NULL to DEFAULT NULL.

      This has been observed for both VARCHAR and BLOB data types.

      What Debezium connector do you use and what version?

      1.9.6.Final

      What is the connector configuration?

       

      {
        "name": "my-sql-connector",
        "connector.class": "io.debezium.connector.mysql.MySqlConnector",
        "database.serverTimezone": "America/Los_Angeles",
        "snapshot.mode": "schema_only",
        "offset.binlog.table": "binlog_offsets",
        "offset.downstream.topic.table": "downstream_topic_to_offset_map",
        "offset.flush.interval.ms": "60000",
        "offset.flush.timeout.ms": "45000",
        "internal.key.converter": "org.apache.kafka.connect.json.JsonConverter",
        "internal.value.converter": "org.apache.kafka.connect.json.JsonConverter",
        "datatype.propagate.source.type": ".+\\.(.*)CHAR,.+\\.(.*)VARCHAR,.+\\.CHARACTER.*,.+\\.BINARY,.+\\.VARBINARY,.+\\.TINYBLOB,.+\\.TINYTEXT,.+\\.MEDIUMBLOB,.+\\.MEDIUMTEXT,.+\\.LONGBLOB,.+\\.LONGTEXT,.+\\.BLOB,.+\\.TEXT,.+\\.FLOAT.*,.+\\.DOUBLE.*,.+\\.REAL.*,.+\\.DATE.*,.+\\.TIME.*,.+\\.TIMESTAMP.*,.+\\.YEAR.*,.+\\.ENUM.*,.+\\.SET.*,.+\\.([A-Z])*INT.*,.+\\.DATETIME.*",
        "converters": "int,float,real,datetime,boolean,timestamp,year",
        "snapshot.lock.timeout.ms": "30000"
      }
       

       

       

      What is the captured database version and mode of depoyment?

      MySQL version 5.7.23-log deployed on public cloud infra.

      What behaviour do you expect?

       

      -- blob table
      CREATE TABLE simple_blob_table (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `blob_column` blob NOT NULL,
          PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
      
      insert into simple_blob_table (blob_column) values (_binary 'Hic sunt dracones.');
      
      ALTER TABLE simple_blob_table MODIFY `blob_column` blob DEFAULT NULL;
      
      insert into simple_blob_table (id) values (2);
      
      -- varchar table 
      CREATE TABLE simple_varchar_table (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `varchar_column` varchar(132) COLLATE utf8_unicode_ci NOT NULL,
          PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
      
      insert into simple_varchar_table (varchar_column) values ('Hic sunt dracones.');
      
      ALTER TABLE simple_varchar_table MODIFY `varchar_column` varchar(132) DEFAULT NULL;
      
      insert into simple_varchar_table (id) values (2);

      I expect this to produce an avro schema with blob_column being a union field of null and bytes (or string for varchar field).

       

       

      // expected blob field avro schema
      {
        "default": null,
        "name": "blob_column",
        "doc": "Default field doc string",
        "type": [
          "null",
          "bytes"
        ],
        "maxlen": 65535
      }

       

      // expected varchar field avro schema
      {
        "default": null,
        "name": "varchar_column",
        "doc": "Default field doc string",
        "type": [
          "null",
          "string"
        ],
        "maxlen": 132
      } 

       

       

       

      What behaviour do you see?

       

      // actual blob field avro schema
      {
        "name": "blob_column",
        "doc": "Default field doc string",
        "type": "bytes",
        "maxlen": 65535
      }

       

       

      // actual varchar field avro schema 
      {
        "name": "varchar_column",
        "doc": "Default field doc string",
        "type": "string",
        "maxlen": 132
      }

       

       

      Attachments

        Activity

          People

            Unassigned Unassigned
            serenity-now Abiodun Ogunmolu
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated: