MySQL to Snowflake Normalization Issue (All Latest Versions)

Connector Questions & Issues
, ,
1
  • Is this your first time deploying Airbyte?: No
  • OS Version / Instance: Ubuntu
  • Memory / Disk: 64Gb
  • Deployment: Docker
  • Airbyte Version: 0.40.14
  • Source name/version: MySQL 1.0.3
  • Destination name/version: Snowflake 0.4.38
  • Step: First sync, normalization issue
  • Description: Numeric value 'false' is not recognized

How does this happen with GA connectors? This is a fresh new deployed airbyte instance, this is the latest source and destination versions and the first sync for this table.

d39daee2_294b_4e75_8522_740f14df7112_logs_48424_txt.txt (167.8 KB)

2

It seems the staging view (the automatically generated staging view as part of the incremental + dedupe normalisation process) is the source of the issue. I cannot view it in snowflake

3

Hi @danieldiamond, I think this is due to new updates to the MySQL connector and how it handles boolean values. I’ll have more info for you in a bit below!

4

First a few questions:

  1. What is the field type for the value causing the error, is it Boolean or TINYINT?

  2. What version of the MySQL connector were you using before this that did not cause errors?

And some information:

  1. There is a GitHub issue for this here, it is not resolved yet:
    https://github.com/airbytehq/airbyte/issues/17510

  2. We changed how the MySQL source connector returns booleans some time between 0.6.1 and 1.0.1.

  3. A potential workaround would be to roll back the version to 0.6.15.

  4. Another two workarounds are described here:
    https://github.com/airbytehq/airbyte/issues/17510#issuecomment-1266107416

This is actively being worked on :slight_smile: let me know if any of those 3 workarounds pan out for you!

5
  1. The field in MySQL source is tinyint(1) unsigned and in the staging view it’s
cast(MY_FIELD as 
    bigint
) as MY_FIELD

and in the RAW AIRBYTE table the JSON is "MY_FIELD": false

  1. i think the MySQL connector version was 0.6.12
6

Got it, thanks!

Could you try rolling back to a pre 1.x.x version, using one of the work arounds listed in the link below, or doing a total reset? The reset obviously would be a last resort option.

https://github.com/airbytehq/airbyte/issues/17510#issuecomment-1266107416

7

Is the suggestion to migrate to pre 1.X OR reset the connector? Because this is a brand new connector, never been synced. Resetting doesn’t resolve this issue

8

Oh, I see I wasn’t aware that there was already a discussion happening on GitHub! I see that one of our engineers is looking for more info for you here:
https://github.com/airbytehq/airbyte/pull/14424

Sorry for the confusion - in this case please try rolling back to 0.6.15 or below, that should be the temporary workaround. Let me know if that helps! Otherwise I’d say to keep an eye on that GitHub thread!

9

Issue has been reproduced and is being resolved here: mysql-source:fix tinyint unsigned handling by subodh1810 · Pull Request #18619 · airbytehq/airbyte · GitHub

10

Hey Daniel, it looks like the issue has been solved! Let me know if you need any further info!

11

Yes @natalyjazzviolin, subodh crushed it. He was extremely responsive, investigated the issue thoroughly and provided a timely and solid fix. Big fan, airbyte team lucky to have him.