\u0000 cannot be converted to text - Postgres Destination #3476

ShawnSack · 2021-05-19T00:13:51Z

Logs

2021-05-18 23:43:30 INFO (/tmp/workspace/4/0) DefaultAirbyteStreamFactory(lambda$create$0):73 - 2021-05-18 23:43:30 �[32mINFO�[m i.a.i.s.j.AbstractJdbcSource(lambda$createReadIterator$8):308 - {} - Reading stream processes. Records read: 160000
2021-05-18 23:43:32 INFO (/tmp/workspace/4/0) DefaultAirbyteStreamFactory(lambda$create$0):73 - 2021-05-18 23:43:32 �[32mINFO�[m i.a.i.s.j.StateDecoratingIterator(computeNext):79 - {} - State Report: stream name: AirbyteStreamNameNamespacePair{name='processes', namespace='labtech'}, original cursor field: null, original cursor null, cursor field: Last_date, new cursor: 2021-05-18T23:42:27Z
2021-05-18 23:43:32 INFO (/tmp/workspace/4/0) DefaultAirbyteStreamFactory(lambda$create$0):73 - 2021-05-18 23:43:32 �[32mINFO�[m i.a.i.s.j.AbstractJdbcSource(lambda$queryTableFullRefresh$29):523 - {} - Preparing query for table: software
2021-05-18 23:43:32 INFO (/tmp/workspace/4/0) DefaultAirbyteStreamFactory(lambda$create$0):73 - 2021-05-18 23:43:32 �[32mINFO�[m i.a.i.s.j.AbstractJdbcSource(lambda$queryTableFullRefresh$29):528 - {} - Executing query for table: software
2021-05-18 23:43:33 INFO (/tmp/workspace/4/0) LineGobbler(voidCall):69 - 2021-05-18 23:43:33 �[32mINFO�[m i.a.i.b.FailureTrackingAirbyteMessageConsumer(close):77 - {} - hasFailed: true.
2021-05-18 23:43:33 INFO (/tmp/workspace/4/0) LineGobbler(voidCall):69 - 2021-05-18 23:43:33 �[1;31mERROR�[m i.a.i.d.b.BufferedStreamConsumer(close):187 - {} - executing on failed close procedure.
2021-05-18 23:43:33 INFO (/tmp/workspace/4/0) LineGobbler(voidCall):69 - 2021-05-18 23:43:33 �[32mINFO�[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):203 - {} - Cleaning tmp tables in destination started for 12 streams
2021-05-18 23:43:33 INFO (/tmp/workspace/4/0) LineGobbler(voidCall):69 - 2021-05-18 23:43:33 �[32mINFO�[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):207 - {} - Cleaning tmp table in destination started for stream automate-h_processes. schema labtech, tmp table name: _airbyte_1621380413832__airbyte_raw_automate_h_processes
2021-05-18 23:43:33 INFO (/tmp/workspace/4/0) LineGobbler(voidCall):69 - 2021-05-18 23:43:33 �[32mINFO�[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):207 - {} - Cleaning tmp table in destination started for stream automate-processes. schema labtech, tmp table name: _airbyte_1621380413832__airbyte_raw_automate_processes
2021-05-18 23:43:33 INFO (/tmp/workspace/4/0) LineGobbler(voidCall):69 - 2021-05-18 23:43:33 �[32mINFO�[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):207 - {} - Cleaning tmp table in destination started for stream automate-software. schema labtech, tmp table name: _airbyte_1621380413832__airbyte_raw_automate_software
2021-05-18 23:43:33 INFO (/tmp/workspace/4/0) LineGobbler(voidCall):69 - 2021-05-18 23:43:33 �[32mINFO�[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):207 - {} - Cleaning tmp table in destination started for stream automate-v_alerts. schema labtech, tmp table name: _airbyte_1621380413832__airbyte_raw_automate_v_alerts
2021-05-18 23:43:33 INFO (/tmp/workspace/4/0) LineGobbler(voidCall):69 - 2021-05-18 23:43:33 �[32mINFO�[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):207 - {} - Cleaning tmp table in destination started for stream automate-v_clients. schema labtech, tmp table name: _airbyte_1621380413832__airbyte_raw_automate_v_clients
2021-05-18 23:43:33 INFO (/tmp/workspace/4/0) LineGobbler(voidCall):69 - 2021-05-18 23:43:33 �[32mINFO�[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):207 - {} - Cleaning tmp table in destination started for stream automate-v_computers. schema labtech, tmp table name: _airbyte_1621380413832__airbyte_raw_automate_v_computers
2021-05-18 23:43:33 INFO (/tmp/workspace/4/0) LineGobbler(voidCall):69 - 2021-05-18 23:43:33 �[32mINFO�[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):207 - {} - Cleaning tmp table in destination started for stream automate-v_computerusage. schema labtech, tmp table name: _airbyte_1621380413832__airbyte_raw_automate_v_computerusage
2021-05-18 23:43:33 INFO (/tmp/workspace/4/0) LineGobbler(voidCall):69 - 2021-05-18 23:43:33 �[32mINFO�[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):207 - {} - Cleaning tmp table in destination started for stream automate-v_detectedroles. schema labtech, tmp table name: _airbyte_1621380413832__airbyte_raw_automate_v_detectedroles
2021-05-18 23:43:33 INFO (/tmp/workspace/4/0) LineGobbler(voidCall):69 - 2021-05-18 23:43:33 �[32mINFO�[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):207 - {} - Cleaning tmp table in destination started for stream automate-v_locations. schema labtech, tmp table name: _airbyte_1621380413832__airbyte_raw_automate_v_locations
2021-05-18 23:43:33 INFO (/tmp/workspace/4/0) LineGobbler(voidCall):69 - 2021-05-18 23:43:33 �[32mINFO�[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):207 - {} - Cleaning tmp table in destination started for stream automate-v_networkadapters. schema labtech, tmp table name: _airbyte_1621380413832__airbyte_raw_automate_v_networkadapters
2021-05-18 23:43:33 INFO (/tmp/workspace/4/0) LineGobbler(voidCall):69 - 2021-05-18 23:43:33 �[32mINFO�[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):207 - {} - Cleaning tmp table in destination started for stream automate-v_printers. schema labtech, tmp table name: _airbyte_1621380413832__airbyte_raw_automate_v_printers
2021-05-18 23:43:33 INFO (/tmp/workspace/4/0) LineGobbler(voidCall):69 - 2021-05-18 23:43:33 �[32mINFO�[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):207 - {} - Cleaning tmp table in destination started for stream automate-v_processors. schema labtech, tmp table name: _airbyte_1621380413832__airbyte_raw_automate_v_processors
2021-05-18 23:43:33 INFO (/tmp/workspace/4/0) LineGobbler(voidCall):69 - 2021-05-18 23:43:33 �[32mINFO�[m i.a.i.d.j.JdbcBufferedConsumerFactory(lambda$onCloseFunction$3):212 - {} - Cleaning tmp tables in destination completed.
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - Exception in thread "main" java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: unsupported Unicode escape sequence
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - Detail: \u0000 cannot be converted to text.
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - Where: JSON data, line 1: ... Menu\Programs" -g="Wall Control"","Version":...
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - COPY _airbyte_1621380413832__airbyte_raw_automate_software, line 115, column _airbyte_data: "{"AppID":127456301,"ComputerID":2667,"Name":"Wall Control","Location":"C:\Program Files (x86)\Wall..."
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - at io.airbyte.integrations.destination.jdbc.DefaultSqlOperations.lambda$insertRecords$0(DefaultSqlOperations.java:97)
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - at io.airbyte.db.jdbc.DefaultJdbcDatabase.execute(DefaultJdbcDatabase.java:61)
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - at io.airbyte.integrations.destination.jdbc.DefaultSqlOperations.insertRecords(DefaultSqlOperations.java:86)
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - at io.airbyte.integrations.destination.jdbc.JdbcBufferedConsumerFactory.lambda$recordWriterFunction$2(JdbcBufferedConsumerFactory.java:171)
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - at io.airbyte.integrations.destination.buffered_stream_consumer.BufferedStreamConsumer.flushQueueToDestination(BufferedStreamConsumer.java:163)
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - at io.airbyte.integrations.destination.buffered_stream_consumer.BufferedStreamConsumer.acceptTracked(BufferedStreamConsumer.java:145)
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - at io.airbyte.integrations.base.FailureTrackingAirbyteMessageConsumer.accept(FailureTrackingAirbyteMessageConsumer.java:66)
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - at io.airbyte.integrations.base.IntegrationRunner.consumeWriteStream(IntegrationRunner.java:132)
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - at io.airbyte.integrations.base.IntegrationRunner.run(IntegrationRunner.java:113)
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - at io.airbyte.integrations.destination.postgres.PostgresDestination.main(PostgresDestination.java:84)
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - Caused by: org.postgresql.util.PSQLException: ERROR: unsupported Unicode escape sequence
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - Detail: \u0000 cannot be converted to text.
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - Where: JSON data, line 1: ... Menu\Programs" -g="Wall Control"","Version":...
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - COPY _airbyte_1621380413832__airbyte_raw_automate_software, line 115, column _airbyte_data: "{"AppID":127456301,"ComputerID":2667,"Name":"Wall Control","Location":"C:\Program Files (x86)\Wall..."
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - at org.postgresql.core.v3.QueryExecutorImpl.processCopyResults(QueryExecutorImpl.java:1212)
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - at org.postgresql.core.v3.QueryExecutorImpl.endCopy(QueryExecutorImpl.java:1017)
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - at org.postgresql.core.v3.CopyInImpl.endCopy(CopyInImpl.java:49)
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:185)
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:160)
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - at io.airbyte.integrations.destination.jdbc.DefaultSqlOperations.lambda$insertRecords$0(DefaultSqlOperations.java:95)
2021-05-18 23:43:33 ERROR (/tmp/workspace/4/0) LineGobbler(voidCall):69 - ... 9 more

Steps to Reproduce

Source Connector MySQL
2.Destination Postgres
Source contains NULL values

Severity of the bug for you

Medium

Airbyte Version

0.22.3-alpha

Connector Version (if applicable)

source-mysql 0.3.0
destination-postgres 0.3.3

Additional context

I believe we just need a handler for NULL values

┆Issue is synchronized with this Asana task by Unito

rclmenezes · 2021-07-14T13:28:21Z

This prevents me from syncing Twilio into Postgres:

2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - COPY _airbyte_1626232176914__airbyte_raw_twilio_messages, line 1711, column _airbyte_data: "{"body":"Hi Sydney, I created two applications because I put the wrong social on the first app. The ..."
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.integrations.destination.jdbc.DefaultSqlOperations.lambda$insertRecords$0(DefaultSqlOperations.java:97)
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.db.jdbc.DefaultJdbcDatabase.execute(DefaultJdbcDatabase.java:61)
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.integrations.destination.jdbc.DefaultSqlOperations.insertRecords(DefaultSqlOperations.java:86)
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.integrations.destination.jdbc.JdbcBufferedConsumerFactory.lambda$recordWriterFunction$2(JdbcBufferedConsumerFactory.java:173)
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.integrations.destination.buffered_stream_consumer.BufferedStreamConsumer.flushQueueToDestination(BufferedStreamConsumer.java:164)
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.integrations.destination.buffered_stream_consumer.BufferedStreamConsumer.acceptTracked(BufferedStreamConsumer.java:146)
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.integrations.base.FailureTrackingAirbyteMessageConsumer.accept(FailureTrackingAirbyteMessageConsumer.java:66)
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.integrations.base.IntegrationRunner.consumeWriteStream(IntegrationRunner.java:132)
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.integrations.base.IntegrationRunner.run(IntegrationRunner.java:113)
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.integrations.destination.postgres.PostgresDestination.main(PostgresDestination.java:84)
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - Caused by: org.postgresql.util.PSQLException: ERROR: unsupported Unicode escape sequence
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 -   Detail: \u0000 cannot be converted to text.
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 -   Where: JSON data, line 1: {"body":...
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - COPY _airbyte_1626232176914__airbyte_raw_twilio_messages, line 1711, column _airbyte_data: "{"body":"Hi Sydney, I created two applications because I put the wrong social on the first app. The ..."
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - 	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - 	at org.postgresql.core.v3.QueryExecutorImpl.processCopyResults(QueryExecutorImpl.java:1212)
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - 	at org.postgresql.core.v3.QueryExecutorImpl.endCopy(QueryExecutorImpl.java:1017)
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - 	at org.postgresql.core.v3.CopyInImpl.endCopy(CopyInImpl.java:49)
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - 	at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:185)
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - 	at org.postgresql.copy.CopyManager.copyIn(CopyManager.java:160)
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - 	at io.airbyte.integrations.destination.jdbc.DefaultSqlOperations.lambda$insertRecords$0(DefaultSqlOperations.java:95)
2021-07-14 03:22:36 ERROR () LineGobbler(voidCall):85 - 	... 9 more

AllanSituma · 2021-08-06T15:34:09Z

This also prevents me from syncing data from Mysql to Postgres

sherifnada · 2021-08-09T20:18:03Z

Hi everyone, pulling this into the current sprint. Apologies for missing it!

alafanechere · 2022-06-09T17:37:18Z

Hey @sherifnada and @DoNotPanicUA, it looks like a regression made this bug happen again according to a post on our forum

DoNotPanicUA · 2022-06-09T21:43:31Z

Hi @alafanechere,
I've checked the code and made some smoke tests. The current version of the destination removes all \u0000 unicode values from the text.
Please ask for the airbyte log.
It will help us to reproduce user preconditions. Because this is not the same issue as was before.

DoNotPanicUA · 2022-06-09T21:52:32Z

Potentially we have an issue with byte fields.

jstettnerbc · 2022-06-10T07:03:53Z

Hi @DoNotPanicUA
I asked on dicourse about this, wasn't sure if re-opening the ticket would be okay.
Here's a snippet from the log:

airbyte-worker      |   at java.util.ArrayList.forEach(ArrayList.java:1511) ~[?:?]
airbyte-worker      |   at io.airbyte.config.persistence.DatabaseConfigPersistence.writeStandardSyncState(DatabaseConfigPersistence.java:1159) ~[io.airbyte.airbyte-config-config-persistence-0.39.11-alpha.jar:?]
airbyte-worker      |   at io.airbyte.config.persistence.DatabaseConfigPersistence.lambda$writeStandardSyncState$46(DatabaseConfigPersistence.java:1152) ~[io.airbyte.airbyte-config-config-persistence-0.39.11-alpha.jar:?]
airbyte-worker      |   at io.airbyte.db.Database.lambda$transaction$0(Database.java:27) ~[io.airbyte.airbyte-db-db-lib-0.39.11-alpha.jar:?]
airbyte-worker      |   at org.jooq.impl.DefaultDSLContext.lambda$transactionResult0$0(DefaultDSLContext.java:564) ~[jooq-3.13.4.jar:?]
airbyte-worker      |   at org.jooq.impl.Tools$12$1.block(Tools.java:4926) ~[jooq-3.13.4.jar:?]
airbyte-worker      |   at java.util.concurrent.ForkJoinPool.unmanagedBlock(ForkJoinPool.java:3463) ~[?:?]
airbyte-worker      |   at java.util.concurrent.ForkJoinPool.managedBlock(ForkJoinPool.java:3434) ~[?:?]
airbyte-worker      |   at org.jooq.impl.Tools$12.get(Tools.java:4923) ~[jooq-3.13.4.jar:?]
airbyte-worker      |   at org.jooq.impl.DefaultDSLContext.transactionResult0(DefaultDSLContext.java:616) ~[jooq-3.13.4.jar:?]
airbyte-worker      |   at org.jooq.impl.DefaultDSLContext.transactionResult(DefaultDSLContext.java:533) ~[jooq-3.13.4.jar:?]
airbyte-worker      |   at io.airbyte.db.Database.transaction(Database.java:27) ~[io.airbyte.airbyte-db-db-lib-0.39.11-alpha.jar:?]
airbyte-worker      |   at io.airbyte.db.ExceptionWrappingDatabase.transaction(ExceptionWrappingDatabase.java:31) ~[io.airbyte.airbyte-db-db-lib-0.39.11-alpha.jar:?]
airbyte-worker      |   at io.airbyte.config.persistence.DatabaseConfigPersistence.writeStandardSyncState(DatabaseConfigPersistence.java:1151) ~[io.airbyte.airbyte-config-config-persistence-0.39.11-alpha.jar:?]
airbyte-worker      |   at io.airbyte.config.persistence.DatabaseConfigPersistence.writeConfigs(DatabaseConfigPersistence.java:1274) ~[io.airbyte.airbyte-config-config-persistence-0.39.11-alpha.jar:?]
airbyte-worker      |   at io.airbyte.config.persistence.ValidatingConfigPersistence.writeConfigs(ValidatingConfigPersistence.java:89) ~[io.airbyte.airbyte-config-config-persistence-0.39.11-alpha.jar:?]
airbyte-worker      |   at io.airbyte.config.persistence.ValidatingConfigPersistence.writeConfig(ValidatingConfigPersistence.java:80) ~[io.airbyte.airbyte-config-config-persistence-0.39.11-alpha.jar:?]
airbyte-worker      |   at io.airbyte.config.persistence.ConfigRepository.updateConnectionState(ConfigRepository.java:778) ~[io.airbyte.airbyte-config-config-persistence-0.39.11-alpha.jar:?]
airbyte-worker      |   at io.airbyte.workers.temporal.sync.PersistStateActivityImpl.persist(PersistStateActivityImpl.java:32) ~[io.airbyte-airbyte-workers-0.39.11-alpha.jar:?]
airbyte-worker      |   at jdk.internal.reflect.GeneratedMethodAccessor337.invoke(Unknown Source) ~[?:?]
airbyte-worker      |   at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
airbyte-worker      |   at java.lang.reflect.Method.invoke(Method.java:568) ~[?:?]
airbyte-worker      |   at io.temporal.internal.activity.POJOActivityTaskHandler$POJOActivityInboundCallsInterceptor.execute(POJOActivityTaskHandler.java:214) ~[temporal-sdk-1.8.1.jar:?]
airbyte-worker      |   at io.temporal.internal.activity.POJOActivityTaskHandler$POJOActivityImplementation.execute(POJOActivityTaskHandler.java:180) ~[temporal-sdk-1.8.1.jar:?]
airbyte-worker      |   at io.temporal.internal.activity.POJOActivityTaskHandler.handle(POJOActivityTaskHandler.java:120) ~[temporal-sdk-1.8.1.jar:?]
airbyte-worker      |   at io.temporal.internal.worker.ActivityWorker$TaskHandlerImpl.handle(ActivityWorker.java:204) ~[temporal-sdk-1.8.1.jar:?]
airbyte-worker      |   at io.temporal.internal.worker.ActivityWorker$TaskHandlerImpl.handle(ActivityWorker.java:164) ~[temporal-sdk-1.8.1.jar:?]
airbyte-worker      |   at io.temporal.internal.worker.PollTaskExecutor.lambda$process$0(PollTaskExecutor.java:93) ~[temporal-sdk-1.8.1.jar:?]
airbyte-worker      |   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
airbyte-worker      |   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
airbyte-worker      |   at java.lang.Thread.run(Thread.java:833) [?:?]
airbyte-worker      | Caused by: org.postgresql.util.PSQLException: ERROR: unsupported Unicode escape sequence
airbyte-worker      |   Detail: \u0000 cannot be converted to text.
airbyte-worker      |   Where: JSON data, line 1: ..."cursor_field":["cursor_ts_rowversion"],"cursor":...
airbyte-worker      |   at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675) ~[postgresql-42.3.4.jar:42.3.4]
airbyte-worker      |   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365) ~[postgresql-42.3.4.jar:42.3.4]
airbyte-worker      |   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355) ~[postgresql-42.3.4.jar:42.3.4]
airbyte-worker      |   at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490) ~[postgresql-42.3.4.jar:42.3.4]
airbyte-worker      |   at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408) ~[postgresql-42.3.4.jar:42.3.4]
airbyte-worker      |   at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:167) ~[postgresql-42.3.4.jar:42.3.4]
airbyte-worker      |   at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:156) ~[postgresql-42.3.4.jar:42.3.4]
airbyte-worker      |   at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-5.0.1.jar:?]
airbyte-worker      |   at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-5.0.1.jar:?]
airbyte-worker      |   at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:209) ~[jooq-3.13.4.jar:?]
airbyte-worker      |   at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:453) ~[jooq-3.13.4.jar:?]
airbyte-worker      |   at org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:904) ~[jooq-3.13.4.jar:?]
airbyte-worker      |   at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:371) ~[jooq-3.13.4.jar:?]
airbyte-worker      |   ... 33 more

And here's the 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"]]}]}%

What might help to track this down: The error does not pop up if we use full-refresh->overwrite as sync mode. So maybe this is related to the datetypes of the cursor and primary keys ?

cheers and thanks for looking into this!

DoNotPanicUA · 2022-06-15T13:14:45Z

Hi @jstettnerbc,
I've managed to reproduce your case. It happens when we are trying to use a binary field as a cursor.
The Mssql database populates all empty chars in a binary by \u0000 (NULL).

So, not to wait for a fix I would recommend you to use another field as an interval cursor.

DoNotPanicUA · 2022-06-15T15:06:03Z

@jstettnerbc,
In addition, I would not recommend you to use a binary attribute as a cursor. The incremental sync fetches data only with the cursor value more than the last max cursor value.
As, we do this filtering on the db side using SQL, we can't guarantee that this comparison is always correct and you never lose some updates. Much better to use datetime fields or simple numbers.

jstettnerbc · 2022-06-15T16:46:07Z

@DoNotPanicUA thanks for looking into this.

Actually we added the field specifically to be used as cursor. I'm absolutely no expert on Microsoft-SQLServer/TransactSQL, but at first glance this look like the right way to go on the source DB side, doesnt it?
https://docs.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-ver16

But we'll check on our side how we can fix this or whether we can add another field to be used as cursor.

DoNotPanicUA · 2022-06-15T17:00:24Z

@jstettnerbc
You are right about the logical meaning of this value and you are right that the DB uses it for doing similar things. (Figure out which change was earlier). And if you compare values inside MsSQL you will get the right result.
But here we have a bit different situation. The Airbyte source reads Binary data as text before we use it for filtering.
As result, we have few transformations. In general, it works, but no one checked so far that it works properly.
I will create a follow-up ticket to cover it by tests and make sure that all sources handle it right.

P.S. One more thing. The problem is not in the destination. We store the biggest cursor value in a small system database. And yeah, this DB is Postgres 😄 I will close this ticket and create a new one with a proper description.

DoNotPanicUA · 2022-06-15T17:48:46Z

@sherifnada @alafanechere
I've checked our Postgres destination. There is no need to reopen this ticket because the destination works fine.
Here you can see evidences that varbinary and binary data were processed successfully to the Postgres.

I've finished the investigation of the reported issue and created the new issue for it #13814.

ShawnSack added the type/bug Something isn't working label May 19, 2021

sherifnada added area/connectors Connector related issues lang/java priority/high High priority labels Aug 9, 2021

yaroslav-hrytsaienko added the size/S label Aug 10, 2021

DoNotPanicUA self-assigned this Aug 10, 2021

DoNotPanicUA mentioned this issue Aug 11, 2021

🐛 Destination Postgres: fix \u0000(NULL) value processing #5336

Merged

14 tasks

DoNotPanicUA closed this as completed in #5336 Aug 30, 2021

igrankova added connectors/destinations-database connectors/destination/postgres labels Jan 17, 2022

alafanechere reopened this Jun 9, 2022

octavia-squidington-iii added autoteam team/databases labels Jun 9, 2022

DoNotPanicUA added the team/connectors-java label Jun 9, 2022

DoNotPanicUA mentioned this issue Jun 15, 2022

State manager failes when gets value with \u0000 #13814

Closed

DoNotPanicUA closed this as completed Jun 15, 2022

edgao mentioned this issue Nov 29, 2023

Destination redshift: async standard inserts #32888

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

\u0000 cannot be converted to text - Postgres Destination #3476

\u0000 cannot be converted to text - Postgres Destination #3476

ShawnSack commented May 19, 2021 •

edited by sync-by-unito bot

rclmenezes commented Jul 14, 2021

AllanSituma commented Aug 6, 2021

sherifnada commented Aug 9, 2021

alafanechere commented Jun 9, 2022

DoNotPanicUA commented Jun 9, 2022

DoNotPanicUA commented Jun 9, 2022

jstettnerbc commented Jun 10, 2022

DoNotPanicUA commented Jun 15, 2022

DoNotPanicUA commented Jun 15, 2022

jstettnerbc commented Jun 15, 2022

DoNotPanicUA commented Jun 15, 2022

DoNotPanicUA commented Jun 15, 2022

\u0000 cannot be converted to text - Postgres Destination #3476

\u0000 cannot be converted to text - Postgres Destination #3476

Comments

ShawnSack commented May 19, 2021 • edited by sync-by-unito bot

Logs

Steps to Reproduce

Severity of the bug for you

Airbyte Version

Connector Version (if applicable)

Additional context

rclmenezes commented Jul 14, 2021

AllanSituma commented Aug 6, 2021

sherifnada commented Aug 9, 2021

alafanechere commented Jun 9, 2022

DoNotPanicUA commented Jun 9, 2022

DoNotPanicUA commented Jun 9, 2022

jstettnerbc commented Jun 10, 2022

DoNotPanicUA commented Jun 15, 2022

DoNotPanicUA commented Jun 15, 2022

jstettnerbc commented Jun 15, 2022

DoNotPanicUA commented Jun 15, 2022

DoNotPanicUA commented Jun 15, 2022

ShawnSack commented May 19, 2021 •

edited by sync-by-unito bot