"invalid input syntax for type" normalization ASP -> Postgres/Mysql

aschmidt · September 21, 2022, 3:33pm

Is this your first time deploying Airbyte?: Yes
OS Version / Instance: Windows
Memory / Disk:
Deployment: Local Docker Containers
Airbyte Version: 0.40.4
Source name/version: Amazon Seller Partner
Destination name/version: Postgres 15.0 or MYSQL 8.0.30
Step: The issue is happening during sync
Description: I tried to normalize the amazon seller partner data in a mysql database first and then moved to postgress for another try. I can fetch the data in raw json with not problem but i need them normalized.
Edit: The given errors are the same on both database types

This is the main error in logs:

21 of 56 ERROR creating table model public.listfinancialeventgroups_beginningbalance.................................... [ERROR in 0.19s]
22 of 56 ERROR creating table model public.listfinancialeventgroups_convertedtotal...................................... [ERROR in 0.20s]
23 of 56 ERROR creating table model public.listfinancialeventgroups_originaltotal....................................... [ERROR in 0.18s]
Database Error in model get_merchant_listings_all_data (models/generated/airbyte_tables/public/get_merchant_listings_all_data.sql)
  invalid input syntax for type double precision: ""
  compiled SQL at ../build/run/airbyte_utils/models/generated/airbyte_tables/public/get_merchant_listings_all_data.sql
Database Error in model listfinancialeventgroups_beginningbalance (models/generated/airbyte_tables/public/listfinancialeventgroups_beginningbalance.sql)
  invalid input syntax for type bigint: "0.0"
  compiled SQL at ../build/run/airbyte_utils/models/generated/airbyte_tables/public/listfinancialeventgroups_beginningbalance.sql
Database Error in model listfinancialeventgroups_convertedtotal (models/generated/airbyte_tables/public/listfinancialeventgroups_convertedtotal.sql)
  invalid input syntax for type bigint: "693.09"
  compiled SQL at ../build/run/airbyte_utils/models/generated/airbyte_tables/public/listfinancialeventgroups_convertedtotal.sql
Database Error in model listfinancialeventgroups_originaltotal (models/generated/airbyte_tables/public/listfinancialeventgroups_originaltotal.sql)

logs-142.txt (278.7 KB)

marcosmarxm · September 21, 2022, 5:18pm

Can you update the schema of connection in Replication Tab?

aschmidt · September 21, 2022, 6:38pm

You mean this button?

I’ll try and edit the result.
Edit: same error

aschmidt · September 26, 2022, 1:14am

Is there anything else i can do or is this a problem within the connector?

marcosmarxm · September 26, 2022, 7:34pm

Sorry the delay here Alexander. Looks for FinancialGroups stream all amount/numeric values are integer:
https://github.com/airbytehq/airbyte/blob/master/airbyte-integrations/connectors/source-amazon-seller-partner/source_amazon_seller_partner/schemas/ListFinancialEventGroups.json

It’ll need a fix to update the values to numeric. I escalated your issue to Github https://github.com/airbytehq/airbyte/issues/17163
I’ll return to you when the issue is fixed.

marcosmarxm · September 29, 2022, 5:41pm

A fix was released for Amazon Seller Partner version 0.2.26 correcting the types for ListFinancialEventGroup stream.

aschmidt · October 6, 2022, 3:15pm

Hey, thank you guys for you quick fix

I updated Airbyte to Version 0.40.12 and set up a new connection and now i get an error right at the first table (GET_AMAZON_FULFILLED_SHIPMENTS_DATA_GENERAL).

Logs say it cant handle the incoming stream:

2022-10-06 14:46:20 source > Encountered an exception while reading stream GET_AMAZON_FULFILLED_SHIPMENTS_DATA_GENERAL
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/airbyte_cdk/sources/abstract_source.py", line 113, in read
    yield from self._read_stream(
  File "/usr/local/lib/python3.9/site-packages/airbyte_cdk/sources/abstract_source.py", line 182, in _read_stream
    for record in record_iterator:
  File "/usr/local/lib/python3.9/site-packages/airbyte_cdk/sources/abstract_source.py", line 285, in _read_full_refresh
    for record in records:
  File "/airbyte/integration_code/source_amazon_seller_partner/streams.py", line 359, in read_records
    raise Exception(f"Unknown response for stream `{self.name}`. Response body {report_payload}")
Exception: Unknown response for stream `GET_AMAZON_FULFILLED_SHIPMENTS_DATA_GENERAL`. Response body {'reportType': 'GET_AMAZON_FULFILLED_SHIPMENTS_DATA_GENERAL', 'processingStatus': 'IN_PROGRESS', 'marketplaceIds': ['A1PA6795UKMFR9'], 'reportId': '3874370019271', 'dataEndTime': '2022-10-06T14:37:17+00:00', 'createdTime': '2022-10-06T14:37:17+00:00', 'processingStartTime': '2022-10-06T14:37:23+00:00', 'dataStartTime': '2022-09-16T00:00:00+00:00'}

EDIT: 75163712_cfa4_4f06_88be_82fab7cb88db_logs_158_txt.txt (303.3 KB)

Now all the normalized tables are empty, even the ones that have no errors. Before tables without an error were saved normalized.

marcosmarxm · October 6, 2022, 5:27pm

The root cause error is:

  File "/usr/local/lib/python3.9/site-packages/requests/models.py", line 1021, in raise_for_status raise HTTPError(http_error_msg, response=self)requests.exceptions.HTTPError: 403 Client Error: Forbidden for url: https://sellingpartnerapi-eu.amazon.com/reports/2021-06-30/reports

Please check with amazon if you have the correct permissions.

aschmidt · October 6, 2022, 8:22pm

That 403 response seems to be from “GET_SALES_AND_TRAFFIC_REPORT”, which is one of the newly added schemas i forgot to turn off.

I tried a sync with only “GET_AMAZON_FULFILLED_SHIPMENTS_DATA_GENERAL” and in the
logs (57.3 KB) for that i cant find an error with the 403 statuscode.

We used “GET_AMAZON_FULFILLED_SHIPMENTS_DATA_GENERAL” before, so permissions should not be an issue. Right before setting up the new connection this table was synced successfully with the old one. I double checked the credentials, there are correct and the same as before.
But i will let the permissions be checked, just to be save.

marcosmarxm · October 7, 2022, 12:23pm

The traceback:

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/airbyte_cdk/sources/abstract_source.py", line 113, in read
    yield from self._read_stream(
  File "/usr/local/lib/python3.9/site-packages/airbyte_cdk/sources/abstract_source.py", line 182, in _read_stream
    for record in record_iterator:
  File "/usr/local/lib/python3.9/site-packages/airbyte_cdk/sources/abstract_source.py", line 285, in _read_full_refresh
    for record in records:
  File "/airbyte/integration_code/source_amazon_seller_partner/streams.py", line 359, in read_records
    raise Exception(f"Unknown response for stream `{self.name}`. Response body {report_payload}")
Exception: Unknown response for stream `GET_AMAZON_FULFILLED_SHIPMENTS_DATA_GENERAL`. Response body {'reportType': 'GET_AMAZON_FULFILLED_SHIPMENTS_DATA_GENERAL', 'processingStatus': 'IN_PROGRESS', 'marketplaceIds': ['A1PA6795UKMFR9'], 'reportId': '3875174019271', 'dataEndTime': '2022-10-06T19:56:32+00:00', 'createdTime': '2022-10-06T19:56:32+00:00', 'processingStartTime': '2022-10-06T19:56:36+00:00', 'dataStartTime': '2022-09-16T00:00:00+00:00'}

Reading the code looks the timeout exceed before the report was finished. My suggestion is to increase the parameter: Max wait time for reports (in seconds)

aschmidt · October 7, 2022, 3:34pm

Ah yes, that worked.
Thank you so far, have a nice weekend

aschmidt · October 11, 2022, 10:57am

Unfortunately the table “get_merchant_listings_all_data” still throws an error while normalazing

 1 of 1 START table model mszweirad.get_merchant_listings_all_data....................................................... [RUN]
2022-10-11 10:48:09 normalization > 1 of 1 ERROR creating table model mszweirad.get_merchant_listings_all_data.............................................. [ERROR in 0.11s]
2022-10-11 10:48:09 normalization > Finished running 1 table model in 0.31s.
2022-10-11 10:48:09 normalization > Completed with 1 error and 0 warnings:
2022-10-11 10:48:09 normalization > Database Error in model get_merchant_listings_all_data (models/generated/airbyte_incremental/mszweirad/get_merchant_listings_all_data.sql)
2022-10-11 10:48:09 normalization >   1292 (22007): Truncated incorrect CHAR(1050) value: 'Der Ständer wird aufgrund seiner standfesten Spreizung besonders für E-Räder, Transporträder, Tandems und Räder mit Kinders'
2022-10-11 10:48:09 normalization >   compiled SQL at ../build/run/airbyte_utils/models/generated/airbyte_incremental/mszweirad/get_merchant_listings_all_data.sql
2022-10-11 10:48:09 normalization > Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

75163712_cfa4_4f06_88be_82fab7cb88db_logs_167_txt.txt (130.6 KB)

marcosmarxm · October 17, 2022, 12:05pm

Sorry the delay here Alexander, this errors is because there is a string being casted to a wrong data type. Could you search and check what field has this value?

aschmidt · October 18, 2022, 5:45pm

No worries, the field containing this value is “item-description”:

I also updated to 0.40.15 and get a new error for the field"_airbyte_data":

2022-10-18 17:33:49 normalization > Completed with 1 error and 0 warnings:
2022-10-18 17:33:49 normalization > Database Error in model get_merchant_listings_all_data (models/generated/airbyte_tables/mszweirad/get_merchant_listings_all_data.sql)
2022-10-18 17:33:49 normalization >   1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAR) as asin1,
2022-10-18 17:33:49 normalization >       json_value(_airbyte_data, 
2022-10-18 17:33:49 normalization >       '$."asin2"' RETURNING CHAR) a' at line 14
2022-10-18 17:33:49 normalization >   compiled SQL at ../build/run/airbyte_utils/models/generated/airbyte_tables/mszweirad/get_merchant_listings_all_data.sql
2022-10-18 17:33:49 normalization > Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

75163712_cfa4_4f06_88be_82fab7cb88db_logs_174_txt.txt (51.0 KB)

marcosmarxm · October 19, 2022, 5:16pm

Alexander can you try to update the schema of Amazon Seller Partner too and try with Pg destination?

aschmidt · October 19, 2022, 8:11pm

Yes refreshing the schema seemed to solve the latest problem (the error in “_airbyte_data”).
But with either source or destination structure the truncated error stays.

Topic		Replies	Views
Database Error - invalid input syntax for type - Pipedrive Source Connector Questions & Issues normalization , data-loading , connectors	0	213	June 14, 2023
Salesforce- data type is invalid Connector Questions & Issues normalization	5	448	September 5, 2022
Postgres to BigQuery logical replication errors out with invalid timestamp (repost) Connector Questions & Issues normalization	1	205	July 14, 2022
Invalid Timestamp in basic normalization (source-postgres) Connector Questions & Issues source-postgres , normalization	6	311	July 14, 2022
JSON schema validation failed Amazon Seller Partner Connector Questions & Issues normalization , connectors	5	491	July 14, 2022

"invalid input syntax for type" normalization ASP -> Postgres/Mysql

Related Topics