https://linen.dev logo
#ask-community-for-troubleshooting
Title
# ask-community-for-troubleshooting
r

Riya Tyagi

07/19/2021, 10:31 AM
check this
s

Subodh (Airbyte)

07/19/2021, 10:46 AM
@Riya Tyagi hey can you share the logs, would be easier to understand what went wrong.
r

Riya Tyagi

07/19/2021, 10:54 AM
s

Subodh (Airbyte)

07/19/2021, 11:08 AM
So you are using mysql destination with normalisation and the normalisation runner fails. You are using 400-700 columns and there are a lots of limits that mysql places on the exact column count allowed for a table. Check this out https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html
You can run the sync without normalisation which would result in your data being stored as JSON blob in the destination. You can then write your own custom transformation query to this data. @Liren Tu (mysql normalisation expert) can you also take a look at the logs and see if we are hitting any database limitation around columns (in terms of storage, etc as per https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html) and if yes, can we have a better exception to highlight the problem.
r

Riya Tyagi

07/19/2021, 12:59 PM
ok thanks..i will check
actually i need normalization
c

Chris (deprecated profile)

07/19/2021, 1:57 PM
400-700 columns and there are a lots of limits that mysql places on the exact column count allowed for a table
In the following test, it seems MySQL was able to go up to 250 columns only before reaching error limits: https://github.com/airbytehq/airbyte/blob/219389b2d175c2444296031951b6f519fd84156d[…]ns/bases/base-normalization/integration_tests/test_ephemeral.py
l

Liren Tu

07/19/2021, 4:56 PM
1. Based on this line of log:
Copy code
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'with __dbt__CTE__riya_sftp_700_ab1 as (
you are probably using a MariaDB server that does not support the
WITH
keyword. https://mariadb.com/kb/en/with/ Our MySQL normalization only works for servers that supports
WITH
(MySQL >= 8.0, MariaDB >= 10.2.1), since it is heavily used in the normalization process. https://docs.airbyte.io/understanding-airbyte/basic-normalization#destinations-that-support-basic-normalization I will update our documentation to clarify the version requirement for MariaDB. 2. It is also highly possible that even if you use a DB server that supports
WITH
, with 700 fields that are mostly strings, it will throw another exception complaining about the row size. I agree with Subodh that running the sync without normalization is the best solution. You can write your own dbt normalization and maybe split the results into multiple tables.
r

Riya Tyagi

07/20/2021, 6:17 AM
thank you @Liren Tu and @Chris (deprecated profile)
j

Jay Patel

07/20/2021, 6:18 AM
@Liren Tu ya we are using mariaDB version 10.3.16, and we have tried upto 400 columns and it works fine with that. So, i think with 700 columns there is an issue with large row size
also have another issue, • after first sync if some new columns added in source table and we update source schema before next sync. • but here we are loosing our existing data in destination table by updating source_schema. can we have any solution for not loosing data here?
Hi @Liren Tu can you check this issue?
l

Liren Tu

07/20/2021, 5:29 PM
@Jay Patel, yes, handling schema change is a big topic. Currently it is in the design phrase. We don’t have an estimated date when it can be resolved. But you can track the progress in this issue: https://github.com/airbytehq/airbyte/issues/1037
j

Jay Patel

07/21/2021, 5:58 AM
oh okay, Thanks a lot @Liren Tu
r

Riya Tyagi

07/23/2021, 9:32 AM
@Liren Tu I have tried to replicate 10gb data from postgres as source to destination mysql but status still in progress ...it has been running since 2 hour. can you tell me what is the reason ?
u

[DEPRECATED] Marcos Marx

07/23/2021, 1:35 PM
@Riya Tyagi could you open a new thread to discuss this problem? Please take a look in the issue template (pinned message in the channel) this will help us debugging and find the root cause quicker
7 Views