Skip to main content

Community

Connector Improvement: Keep MySQL TINYINT(1) Data Type

Answered

Please sign in to leave a comment.

Comments

4 comments

  • Official comment

    Hi John and Bryan,

    Thanks for your feedback here. One key part of our product is to automate as much of the data pipeline workflow as possible. In MySQL databases, BOOLEAN is a synonym for TINYINT(1), so if a user creates a BOOLEAN column, they actually get stored in the database as TINYINT(1) with 0's and 1's representing corresponding values.

    This automatic transformation is implemented to keep in line with MySQL's handling of bools. Would it be possible to perform a post-load transformation for this scenario?

    Need this feature to be implemented as it causes confusion and more importantly different data which will hinder how we move forward with a key initiative we have next year.  In my mind.. if it's TINYINT in the source, it should be TINYINT in the target.. otherwise we are losing the basic concept of Extract/Load

    In theory, the sources can choose to use tinyint(1) for real tiny integer, or use it to store TRUE and FALSE as 1 and 0,   If internally fivetran infer the destination type from data value, it looses the consistency and transparency to destination systems. As from destination perspective, we may unexpectedly experience type change that can break downstream transformation logic and often times no good way of knowing it ahead of time.   The harm of save actual BOOLEAN as integer 0s and 1s maybe that it increases the storage cost on destination space, I can't think of any other harms. But  the harm that comes with infer and change type dynamically from data value is quite obvious and hard to have a good workaround.

    Can Fivetran just map tinyint to INTEGER?

    So the official resolution (for now) is to apply a "Transformation" to cast these columns as TINYINT(1)? Or just to cast them as INTEGER?

Didn’t find what you need?

Contact support