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

MySQL treats the BOOLEAN synonym differently when processed in snapshot vs streaming phases.


    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major Major
    • 2.2.0.Beta1
    • 2.1.3.Final, 2.2.0.Alpha3
    • mysql-connector
    • None

      MySQL treats the BOOLEAN data type as a synonym for TINYINT(1).

      During the snapshot phase of the connector, if a table already exists with such a column, the connector resolves the column's data type and JDBC type from the JDBC metadata. The driver returns this column as a TINYINT type with a length of 1, mapping the final value as an INT16 data type in the event's schema.

      During the streaming phase, if the table existed previously during the snapshot where the Table in-memory model was prepared from the JDBC driver metadata, then the streaming phase will emit the value as it was during the snapshot.

      However, the problem arises if the table is created after streaming has started and the include/exclude filters allow the table to be captured. In this case, the table's structure and JDBC type metadata are evaluated from the DDL event itself, and the ANTLR parser maps a BOOLEAN to a JDBC type of BOOLEAN rather than SMALLINT. This means that the final event will specify the schema type as BOOLEAN and the column type as BOOLEAN; which is different than if the table had been captured during snapshot.

      To reproduce, start the MySQL connector with the table.include.list allowing the "test_boolean_table". Then, once the connector has started the streaming phase, create the following table, insert a row into the table, and note the field "b1" schema. Then repeat the process except where the following table is created before the connector starts with an existing row. Then, compare the schema of "b1" to the previous schema when the table was created during streaming, and they differ drastically.

      CREATE TABLE test_boolean_table ( b1 boolean );

      Regardless of when the table is created, BOOLEAN data types should be treated as a TINYINT(1) to conform with the JDBC driver and the database as MySQL does not technically have BOOLEAN data type support and treats these explicitly as TINYINT(1) internally.

            Unassigned Unassigned
            ccranfor@redhat.com Chris Cranford
            0 Vote for this issue
            4 Start watching this issue