Redshift Dv2 Timeouts (marked as broken because of SQLSTATE)

[evantahler]

Consider these connections (1, 2)

Slack (1, 2)

To me it looks like the connection keep alive or similar is not working properly.
Here are two initial sync attempts that ran into a connection timeout while filling the destination tables.

2023-12-18 11:17:01 destination > COMMIT;
2023-12-18 11:27:00 destination > 2023-12-18 11:27:00 WARN o.a.s.c.u.l.LoggingUtils(warn):618 - exceptionCaught(ClientSessionImpl[airbyte@xxx.eu-central-1.compute.amazonaws.com/xxx:22])[state=Opened] SocketException: Connection reset
2023-12-18 11:27:00 destination > 2023-12-18 11:27:00 WARN c.z.h.p.ProxyConnection(checkException):177 - HikariPool-1 - Connection com.amazon.redshift.jdbc.RedshiftConnectionImpl@2a7e15eb marked as broken because of SQLSTATE(08006), ErrorCode(0)
2023-12-18 11:27:00 destination > com.amazon.redshift.util.RedshiftException: An I/O error occurred while sending to the backend.
2023-12-18 11:27:00 destination >         at com.amazon.redshift.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:402) ~[redshift-jdbc42-2.1.0.23.jar:?]
2023-12-18 11:27:00 destination >         at com.amazon.redshift.jdbc.RedshiftStatementImpl.executeInternal(RedshiftStatementImpl.java:515) ~[redshift-jdbc42-2.1.0.23.jar:?]
2023-12-18 11:27:00 destination >         at com.amazon.redshift.jdbc.RedshiftStatementImpl.execute(RedshiftStatementImpl.java:436) ~[redshift-jdbc42-2.1.0.23.jar:?]
2023-12-18 11:27:00 destination >         at com.amazon.redshift.jdbc.RedshiftStatementImpl.executeWithFlags(RedshiftStatementImpl.java:377) ~[redshift-jdbc42-2.1.0.23.jar:?]
2023-12-18 11:27:00 destination >         at com.amazon.redshift.jdbc.RedshiftStatementImpl.executeCachedSql(RedshiftStatementImpl.java:363) ~[redshift-jdbc42-2.1.0.23.jar:?]
2023-12-18 11:27:00 destination >         at com.amazon.redshift.jdbc.RedshiftStatementImpl.executeWithFlags(RedshiftStatementImpl.java:340) ~[redshift-jdbc42-2.1.0.23.jar:?]
2023-12-18 11:27:00 destination >         at com.amazon.redshift.jdbc.RedshiftStatementImpl.execute(RedshiftStatementImpl.java:330) ~[redshift-jdbc42-2.1.0.23.jar:?]
2023-12-18 11:27:00 destination >         at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) ~[HikariCP-5.0.1.jar:?]
2023-12-18 11:27:00 destination >         at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-5.0.1.jar:?]
2023-12-18 11:27:00 destination >         at io.airbyte.cdk.db.jdbc.JdbcDatabase.lambda$execute$0(JdbcDatabase.java:49) ~[airbyte-cdk-core-0.7.3.jar:?]
2023-12-18 11:27:00 destination >         at io.airbyte.cdk.db.jdbc.DefaultJdbcDatabase.execute(DefaultJdbcDatabase.java:46) ~[airbyte-cdk-core-0.7.3.jar:?]
2023-12-18 11:27:00 destination >         at io.airbyte.cdk.db.jdbc.JdbcDatabase.execute(JdbcDatabase.java:49) ~[airbyte-cdk-core-0.7.3.jar:?]
2023-12-18 11:27:00 destination >         at io.airbyte.cdk.integrations.destination.jdbc.typing_deduping.JdbcDestinationHandler.execute(JdbcDestinationHandler.java:76) ~[airbyte-cdk-db-destinations-0.7.3.jar:?]
2023-12-18 11:27:00 destination >         at io.airbyte.integrations.base.destination.typing_deduping.TypeAndDedupeTransaction.executeTypeAndDedupe(TypeAndDedupeTransaction.java:39) ~[airbyte-cdk-typing-deduping-0.7.3.jar:?]
2023-12-18 11:27:00 destination >         at io.airbyte.integrations.base.destination.typing_deduping.DefaultTyperDeduper.lambda$typeAndDedupeTask$2(DefaultTyperDeduper.java:220) ~[airbyte-cdk-typing-deduping-0.7.3.jar:?]
2023-12-18 11:27:00 destination >         at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1768) [?:?]
2023-12-18 11:27:00 destination >         at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
2023-12-18 11:27:00 destination >         at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
2023-12-18 11:27:00 destination >         at java.base/java.lang.Thread.run(Thread.java:833) [?:?]
2023-12-18 11:27:00 destination > Caused by: java.io.EOFException: The server closed the connection.

We likely have a keep-alive/timeout issue with the Redshift driver/client giving up when there are Very Slow (tm) queries.

Highly relevant - https://docs.aws.amazon.com/redshift/latest/mgmt/jdbc20-install.html#configure-tcp-keepalives-jdbc20 (default), but we probably need https://stackoverflow.com/questions/54804275/hikaripool-1-connection-marked-as-broken-because-of-sqlstate08s01-errorcode

[evantahler]

Grooming:

• We should add retries
• We should make the timeouts big (2h?). But not configurable for the user, for now.
• We inherit the source config for the hikaripool - hopefully this is easy to override?

Check here https://stackoverflow.com/questions/60925289/hikaripool-1-connection-marked-as-broken-because-of-sqlstate08006-errorcode

Look in the RedshiftSQLGenerator.

[evantahler]

Grooming:

• We think this an SSH tunnel issue, not a JDBC issue
• similar to SSH bastion times out with destination-postgres V2 #33871