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)
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'}
Now all the normalized tables are empty, even the ones that have no errors. Before tables without an error were saved normalized.
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.
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.
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)
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
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
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.
