Summary
The user is facing an error while trying to perform CDC from Aurora Postgres to MySQL RDS using Airbyte. The error indicates a SQL syntax issue in the MySQL server version. The user is seeking help to resolve this issue.
Question
Hi team,
I am trying to do CDC from Aurora Postgres 16 to MySQL RDS with latest open source Airbyte deployed on my Mac following this guide for https://docs.airbyte.com/using-airbyte/getting-started/oss-quickstart|deployment (abctl version: v0.13.1). But I could never get it working, the table is created at MySQL destination but there is no records there. In the log of Job, it has the error which I could not understand. Could anyone throw some light how to resolve it? Quite frustrated as I could not find any solution in the Slack and over the internet for this 
2024-08-31 14:50:39 destination > ERROR main i.a.c.i.b.AirbyteExceptionHandler(uncaughtException):30 Something went wrong in the connector. See the logs for more details. java.util.concurrent.CompletionException: java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: 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 'intermediate_data as (select cast(JSON_VALUE(JSON_EXTRACT(_airbyte_data, ‘$.’ at line 1
856
at java.base/java.util.concurrent.CompletableFuture.encodeThrowable(CompletableFuture.java:315) ~[?:?]
857
at java.base/java.util.concurrent.CompletableFuture.completeThrowable(CompletableFuture.java:320) ~[?:?]
858
at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1770) ~[?:?]
859
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[?:?]
860
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[?:?]
861
at java.base/java.lang.Thread.run(Thread.java:1583) [?:?]
862
Caused by: java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: 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 'intermediate_data as (select cast(JSON_VALUE(JSON_EXTRACT(_airbyte_data, ‘$.’ at line 1
863
at io.airbyte.integrations.base.destination.typing_deduping.DefaultTyperDeduper.prepareTablesFuture$lambda$4(DefaultTyperDeduper.kt:264) ~[airbyte-cdk-typing-deduping-0.33.0.jar:?]
864
at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1768) ~[?:?]
865
… 3 more
866
Caused by: java.sql.SQLSyntaxErrorException: 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 'intermediate_data as (select cast(JSON_VALUE(JSON_EXTRACT(_airbyte_data, ‘$.’ at line 1
867
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.22.jar:8.0.22]
868
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.22.jar:8.0.22]
869
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.22.jar:8.0.22]
870
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764) ~[mysql-connector-java-8.0.22.jar:8.0.22]
871
at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648) ~[mysql-connector-java-8.0.22.jar:8.0.22]
872
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) ~[HikariCP-5.1.0.jar:?]
873
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-5.1.0.jar:?]
874
at io.airbyte.cdk.db.jdbc.JdbcDatabase.executeWithinTransaction$lambda$1(JdbcDatabase.kt:46) ~[airbyte-cdk-core-0.33.0.jar:?]
875
at io.airbyte.cdk.db.jdbc.DefaultJdbcDatabase.execute(DefaultJdbcDatabase.kt:30) ~[airbyte-cdk-core-0.33.0.jar:?]
876
at io.airbyte.cdk.db.jdbc.JdbcDatabase.executeWithinTransaction(JdbcDatabase.kt:43) ~[airbyte-cdk-core-0.33.0.jar:?]
877
at io.airbyte.cdk.integrations.destination.jdbc.typing_deduping.JdbcDestinationHandler.execute(JdbcDestinationHandler.kt:180) ~[airbyte-cdk-db-destinations-0.33.0.jar:?]
878
at io.airbyte.integrations.base.destination.typing_deduping.TypeAndDedupeTransaction.executeTypeAndDedupe(TypeAndDedupeTransaction.kt:56) ~[airbyte-cdk-typing-deduping-0.33.0.jar:?]
879
at io.airbyte.integrations.base.destination.typing_deduping.TypeAndDedupeTransaction.executeSoftReset(TypeAndDedupeTransaction.kt:92) ~[airbyte-cdk-typing-deduping-0.33.0.jar:?]
880
at io.airbyte.integrations.base.destination.typing_deduping.DefaultTyperDeduper.prepareTablesFuture$lambda$4(DefaultTyperDeduper.kt:222) ~[airbyte-cdk-typing-deduping-0.33.0.jar:?]
881
at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1768) ~[?:?]
882
… 3 more
This topic has been created from a Slack thread to give it more visibility.
It will be on Read-Only mode here. Click here if you want
to access the original thread.