Postgres target cant handle \u0000(NULL) from json

Connector Questions & Issues
,
1

Hi community!

it’s my first post here. First tryarounds with airbyte were great. Now I ran into a first issue and could need some help.

  • Is this your first time deploying Airbyte?: Yes
  • OS Version / Instance: Ubuntu LTS 20.04
  • Memory / Disk: 32Gb / 200 Gb
  • Deployment: docker-compose
  • Airbyte Version: VERSION=0.39.5-alpha
  • Source name/version: MSSQL Server 2021
  • Destination name/version: postgres 13.4
  • Step: during sync
  • Description:

When syncing a table for the first time, airbyte fetches the source table succesfully but fails when pushing the JSON blob to the target postgres DB.
Error from the logs:

ERROR: unsupported Unicode escape sequence
  Detail: \u0000 cannot be converted to text.

As far as I understand it, the problem is that NULL values from the source table are encoded as \u0000 in the JSON and then fail to be decoded when pushed to the postgres target.

I found in the Changelog of v0.29.17 (Changelog | Airbyte Documentation) that this issue has already been fixed. Any ideas why this pops up in the latest version again?

Edit: This seems to be the code that was added back then to fix it: 🐛 Destination Postgres: fix \u0000(NULL) value processing (#5336) · airbytehq/airbyte@b18bd43 · GitHub

2

Hi @jstettnerbc,
I re-opened the issue that was linked to the PR.
Could you please share your catalog (instructions here) and the exact version of your source and destination postgres connector?

3

Hi @alafanechere ,
thanks for answering so quickly.

Here’s my source_catalog.json

{"streams":[{"stream":{"name":"airbytesync_GAM","json_schema":{"type":"object","properties":{"ID":{"type":"number"},"XS":{"type":"boolean"},"EAN":{"type":"string"},"BCEK":{"type":"number"},"XSXA":{"type":"string"},"Farbe":{"type":"string"},"bc VK":{"type":"number"},"GAM_ID":{"type":"number"},"ExaktID":{"type":"string"},"Groesse":{"type":"string"},"AnzahlFrei":{"type":"number"},"Fachklasse":{"type":"number"},"AnzahlLager":{"type":"number"},"bc_vk_netto":{"type":"number"},"XA_CreateDate":{"type":"string"},"cursor_ts_rowversion":{"type":"string","contentEncoding":"base64"}}},"supported_sync_modes":["full_refresh","incremental"],"default_cursor_field":[],"source_defined_primary_key":[],"namespace":"dbo"},"sync_mode":"incremental","cursor_field":["cursor_ts_rowversion"],"destination_sync_mode":"append_dedup","primary_key":[["GAM_ID"]]}]}%

In the meantime, I found out that the issue seems to related to the sync mode. If full-refresh → overwrite is chosen, it works fine. Maybe that helps tracking it down.

Shall we switch over to github and continue the discussion under the mentioned ticket or stick here? I’m happy either way, just asking because I dont know your defaults.

4

Let’s move over to the GitHub issue now that we have an engineer in the loop.

5

Hi there from the Community Assistance team.
We’re letting you know about an issue we discovered with the back-end process we use to handle topics and responses on the forum. If you experienced a situation where you posted the last message in a topic that did not receive any further replies, please open a new topic to continue the discussion. In addition, if you’re having a problem and find a closed topic on the subject, go ahead and open a new topic on it and we’ll follow up with you. We apologize for the inconvenience, and appreciate your willingness to work with us to provide a supportive community.