[source-postgres] stream hangs in infinite loop of 'Transaction isolation level -1 not supported' error

[samcz-tm]

Connector Name

source-postgres

Connector Version

3.6.23

What step the error happened?

During the sync

Relevant information

The stream starts and then immediately falls into an infinite loop of this same error. It will hang indefinitely in this state until canceled or the connection times out. If the stream is restarted immediately after cancelling or timing out it runs without issue. This is not happening on every sync, we are running the stream every 30 minutes and it is only getting stuck in this error loop once or twice per day. Issue only seems to have started happening after we upgraded to connector version 3.6.23, but downgrading back to 3.6.19 has not fixed it.

Our configuration for the stream is: airbyte -> pgbouncer -> postgresql db. Pgbouncer is just showing reconnections without any error and postgresql is not showing any error logs. In pgbouncer we have pool_mode = session.

Relevant log output

2024-12-02 13:41:44 source > ERROR HikariPool-1 connection adder c.z.h.p.HikariPool(createPoolEntry):483 HikariPool-1 - Error thrown while acquiring connection from data source org.postgresql.util.PSQLException: Transaction isolation level -1 not supported.
        at org.postgresql.jdbc.PgConnection.setTransactionIsolation(PgConnection.java:1062) ~[postgresql-42.6.2.jar:42.6.2]
        at com.zaxxer.hikari.pool.PoolBase.setupConnection(PoolBase.java:415) ~[HikariCP-5.1.0.jar:?]
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:365) ~[HikariCP-5.1.0.jar:?]
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:202) ~[HikariCP-5.1.0.jar:?]
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:461) ~[HikariCP-5.1.0.jar:?]
        at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:724) ~[HikariCP-5.1.0.jar:?]
        at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:703) ~[HikariCP-5.1.0.jar:?]
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:317) ~[?:?]
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[?:?]
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[?:?]
        at java.base/java.lang.Thread.run(Thread.java:1583) [?:?]
Stack Trace: org.postgresql.util.PSQLException: Transaction isolation level -1 not supported.
        at org.postgresql.jdbc.PgConnection.setTransactionIsolation(PgConnection.java:1062)
        at com.zaxxer.hikari.pool.PoolBase.setupConnection(PoolBase.java:415)
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:365)
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:202)
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:461)
        at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:724)
        at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:703)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:317)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
        at java.base/java.lang.Thread.run(Thread.java:1583)

Contribute

• Yes, I want to contribute

[samcz-tm]

Update: this has now started happening with one of our MySQL sources as well. Almost identical error message.

2024-12-09 17:01:27 ERROR i.a.w.i.VersionedAirbyteStreamFactory(internalLog):304 - ERROR HikariPool-1 connection adder c.z.h.p.HikariPool(createPoolEntry):483 HikariPool-1 - Error thrown while acquiring connection from data source java.sql.SQLException: Unsupported transaction isolation level '-1'
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.30.jar:8.0.30]
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.30.jar:8.0.30]
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) ~[mysql-connector-java-8.0.30.jar:8.0.30]
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) ~[mysql-connector-java-8.0.30.jar:8.0.30]
        at com.mysql.cj.jdbc.ConnectionImpl.setTransactionIsolation(ConnectionImpl.java:2246) ~[mysql-connector-java-8.0.30.jar:8.0.30]
        at com.zaxxer.hikari.pool.PoolBase.setupConnection(PoolBase.java:415) ~[HikariCP-5.1.0.jar:?]
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:365) ~[HikariCP-5.1.0.jar:?]
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:202) ~[HikariCP-5.1.0.jar:?]
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:461) ~[HikariCP-5.1.0.jar:?]
        at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:724) ~[HikariCP-5.1.0.jar:?]
        at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:703) ~[HikariCP-5.1.0.jar:?]
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:317) ~[?:?]
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[?:?]
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[?:?]
        at java.base/java.lang.Thread.run(Thread.java:1583) [?:?]
Stack Trace: java.sql.SQLException: Unsupported transaction isolation level '-1'
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
        at com.mysql.cj.jdbc.ConnectionImpl.setTransactionIsolation(ConnectionImpl.java:2246)
        at com.zaxxer.hikari.pool.PoolBase.setupConnection(PoolBase.java:415)
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:365)
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:202)
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:461)
        at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:724)
        at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:703)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:317)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
        at java.base/java.lang.Thread.run(Thread.java:1583)