Uploaded image for project: 'Apache NiFi'
  1. Apache NiFi
  2. NIFI-7785

CaptureChangeMySQL processor captures enum values as "INDEX of those values" from Mysql DB"

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.11.4
    • Fix Version/s: None
    • Component/s: Tools and Build
    • Labels:
    • Environment:
      Ubuntu EC2 instance with 8 GB ram
    • Flags:
      Important

      Description

      CaptureChangeMySQL processor captures enum values as "INDEX of those values" rather than the values specified.

      for example:

      A table has columns (id int, fruit enum ('apple','pears','orange'), price int)

      On doing an insert:

      insert into (1,'apple',45)

      insert into (2,'pears',56)

      I have used CaptureChangeMySql processor to capture the CDC changes, the process does the capture but captures the enum column alone based on its index like the sample below:

      for 1st insert:

       

      {
      "type":"insert",
      "timestamp":1599004442000,
      "binlog_filename":"mysql-bin-changelog.000039",
      "binlog_position":1537835,
      "database":"sample",
      "table_name":"sample",
      "table_id":82,
      "columns":[

      { "id":1, "name":"id", "column_type":-5, "value":139 }

      ,

      { "id":2, "name":"fruit", "column_type":12, "value":0 }

      ,

      { "id":3, "name":"price", "column_type":12, "value":45 }

      ]
      }

       

      for 2nd insert:

       

      {
      "type":"insert",
      "timestamp":1599004442000,
      "binlog_filename":"mysql-bin-changelog.000039",
      "binlog_position":1537835,
      "database":"sample",
      "table_name":"sample",
      "table_id":82,
      "columns":[

      { "id":1, "name":"id", "column_type":-5, "value":139 }

      ,

      { "id":2, "name":"fruit", "column_type":12, "value":1 }

      ,

      { "id":3, "name":"price", "column_type":12, "value":56 }

      ]
      }

       

       

      So the above has 0 and 1 in place of apple and pears respectively.

       

      Could you of you help me on this, if there are folks who have faced similar kinda issue

       

       

        Attachments

        1. flow.xml.gz
          3 kB
          Roberto Garcia
        2. sample_with_enum.xml
          26 kB
          zeyk
        3. Screenshot from 2020-09-15 05-29-48.png
          167 kB
          zeyk
        4. Screenshot from 2020-09-15 08-05-33.png
          376 kB
          zeyk
        5. sqlcmdReturn.png
          26 kB
          zeyk

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              utra zeyk
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: