View Revisions: Issue #4299

Back to Issue
Summary 0004299: Cannot change transaction isolation level while using jndi on wildfly and postgres
Revision 2020-03-04 15:38 by emmedierre
Description Hi,
We using symmetric-ds 3.11.5 + wildfly 8.1 + postgres 9.5 + Java 12 + jdbc driver : postgresql-42.2.10.jar

It works fine if we use direct connection :
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://localhost:5432/dbssms?protocolVersion=3&stringtype=unspecified
db.user=....
db.password=....

It doesn't work if we use JNDI datasource:
db.jndi.name=java:/com.sigmaspa.symmetricds
db.driver=org.postgresql.Driver
db.url=X
db.user=X
db.password=X

The error is
PSQLException: Cannot change transaction isolation level in the middle of a transaction

This is the full exception:
16:21:21,943 ERROR [org.jumpmind.symmetric.job.RouterJob] (sgmserver-1-job-1) Exception while executing job 'Routing': org.jumpmind.db.sql.SqlException: Failed to execute sql: select distinct channel_id from sym_data where ((data_id between ? and ?))
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:305) [symmetric-db-3.11.5.jar:3.11.5]
    at org.jumpmind.db.sql.JdbcSqlReadCursor.<init>(JdbcSqlReadCursor.java:123) [symmetric-jdbc-3.11.5.jar:3.11.5]
    at org.jumpmind.db.sql.JdbcSqlTemplate.queryForCursor(JdbcSqlTemplate.java:156) [symmetric-jdbc-3.11.5.jar:3.11.5]
    at org.jumpmind.db.sql.JdbcSqlTemplate.queryForCursor(JdbcSqlTemplate.java:144) [symmetric-jdbc-3.11.5.jar:3.11.5]
    at org.jumpmind.db.sql.AbstractSqlTemplate.queryForCursor(AbstractSqlTemplate.java:113) [symmetric-db-3.11.5.jar:3.11.5]
    at org.jumpmind.db.sql.AbstractSqlTemplate.query(AbstractSqlTemplate.java:202) [symmetric-db-3.11.5.jar:3.11.5]
    at org.jumpmind.db.sql.AbstractSqlTemplate.query(AbstractSqlTemplate.java:198) [symmetric-db-3.11.5.jar:3.11.5]
    at org.jumpmind.db.sql.AbstractSqlTemplate.query(AbstractSqlTemplate.java:188) [symmetric-db-3.11.5.jar:3.11.5]
    at org.jumpmind.symmetric.service.impl.RouterService.getReadyChannels(RouterService.java:330) [symmetric-core-3.11.5.jar:3.11.5]
    at org.jumpmind.symmetric.service.impl.RouterService.routeDataForEachChannel(RouterService.java:272) [symmetric-core-3.11.5.jar:3.11.5]
    at org.jumpmind.symmetric.service.impl.RouterService.routeData(RouterService.java:217) [symmetric-core-3.11.5.jar:3.11.5]
    at org.jumpmind.symmetric.job.RouterJob.doJob(RouterJob.java:49) [symmetric-client-3.11.5.jar:3.11.5]
    at org.jumpmind.symmetric.job.AbstractJob.invoke(AbstractJob.java:227) [symmetric-client-3.11.5.jar:3.11.5]
    at org.jumpmind.symmetric.job.AbstractJob.run(AbstractJob.java:298) [symmetric-client-3.11.5.jar:3.11.5]
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) [spring-context-5.1.7.RELEASE.jar:5.1.7.RELEASE]
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [rt.jar:1.8.0_211]
    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [rt.jar:1.8.0_211]
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [rt.jar:1.8.0_211]
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [rt.jar:1.8.0_211]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [rt.jar:1.8.0_211]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [rt.jar:1.8.0_211]
    at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_211]
Caused by: org.postgresql.util.PSQLException: Cannot change transaction isolation level in the middle of a transaction.
    at org.postgresql.jdbc.PgConnection.setTransactionIsolation(PgConnection.java:920) [postgresql-42.2.10.jar:42.2.10]
    at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.setJdbcTransactionIsolation(BaseWrapperManagedConnection.java:1052)
    at org.jboss.jca.adapters.jdbc.WrappedConnection.setTransactionIsolation(WrappedConnection.java:906)
    at org.jumpmind.db.sql.JdbcSqlReadCursor.<init>(JdbcSqlReadCursor.java:84) [symmetric-jdbc-3.11.5.jar:3.11.5]
Revision 2024-11-13 18:56 by cquamme
Description Hi,
We using symmetric-ds 3.11.5 + wildfly 8.1 + postgres 9.5 + Java 12 + jdbc driver : postgresql-42.2.10.jar

It works fine if we use direct connection :
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://localhost:5432/dbssms?protocolVersion=3&stringtype=unspecified
db.user=....
db.password=....

It doesn't work if we use JNDI datasource:
db.jndi.name=java:/com.sigmaspa.symmetricds
db.driver=org.postgresql.Driver
db.url=X
db.user=X
db.password=X

The error is
PSQLException: Cannot change transaction isolation level in the middle of a transaction

This is the full exception:
16:21:21,943 ERROR [org.jumpmind.symmetric.job.RouterJob] (sgmserver-1-job-1) Exception while executing job 'Routing': org.jumpmind.db.sql.SqlException: Failed to execute sql: select distinct channel_id from sym_data where ((data_id between ? and ?))
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:305) [symmetric-db-3.11.5.jar:3.11.5]
    at org.jumpmind.db.sql.JdbcSqlReadCursor.<init>(JdbcSqlReadCursor.java:123) [symmetric-jdbc-3.11.5.jar:3.11.5]
    at org.jumpmind.db.sql.JdbcSqlTemplate.queryForCursor(JdbcSqlTemplate.java:156) [symmetric-jdbc-3.11.5.jar:3.11.5]
    at org.jumpmind.db.sql.JdbcSqlTemplate.queryForCursor(JdbcSqlTemplate.java:144) [symmetric-jdbc-3.11.5.jar:3.11.5]
    at org.jumpmind.db.sql.AbstractSqlTemplate.queryForCursor(AbstractSqlTemplate.java:113) [symmetric-db-3.11.5.jar:3.11.5]
    at org.jumpmind.db.sql.AbstractSqlTemplate.query(AbstractSqlTemplate.java:202) [symmetric-db-3.11.5.jar:3.11.5]
    at org.jumpmind.db.sql.AbstractSqlTemplate.query(AbstractSqlTemplate.java:198) [symmetric-db-3.11.5.jar:3.11.5]
    at org.jumpmind.db.sql.AbstractSqlTemplate.query(AbstractSqlTemplate.java:188) [symmetric-db-3.11.5.jar:3.11.5]
    at org.jumpmind.symmetric.service.impl.RouterService.getReadyChannels(RouterService.java:330) [symmetric-core-3.11.5.jar:3.11.5]
    at org.jumpmind.symmetric.service.impl.RouterService.routeDataForEachChannel(RouterService.java:272) [symmetric-core-3.11.5.jar:3.11.5]
    at org.jumpmind.symmetric.service.impl.RouterService.routeData(RouterService.java:217) [symmetric-core-3.11.5.jar:3.11.5]
    at org.jumpmind.symmetric.job.RouterJob.doJob(RouterJob.java:49) [symmetric-client-3.11.5.jar:3.11.5]
    at org.jumpmind.symmetric.job.AbstractJob.invoke(AbstractJob.java:227) [symmetric-client-3.11.5.jar:3.11.5]
    at org.jumpmind.symmetric.job.AbstractJob.run(AbstractJob.java:298) [symmetric-client-3.11.5.jar:3.11.5]
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) [spring-context-5.1.7.RELEASE.jar:5.1.7.RELEASE]
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [rt.jar:1.8.0_211]
    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [rt.jar:1.8.0_211]
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [rt.jar:1.8.0_211]
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [rt.jar:1.8.0_211]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [rt.jar:1.8.0_211]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [rt.jar:1.8.0_211]
    at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_211]
Caused by: org.postgresql.util.PSQLException: Cannot change transaction isolation level in the middle of a transaction.
    at org.postgresql.jdbc.PgConnection.setTransactionIsolation(PgConnection.java:920) [postgresql-42.2.10.jar:42.2.10]
    at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.setJdbcTransactionIsolation(BaseWrapperManagedConnection.java:1052)
    at org.jboss.jca.adapters.jdbc.WrappedConnection.setTransactionIsolation(WrappedConnection.java:906)
    at org.jumpmind.db.sql.JdbcSqlReadCursor.<init>(JdbcSqlReadCursor.java:84) [symmetric-jdbc-3.11.5.jar:3.11.5]
Revision 2020-03-04 15:38 by emmedierre
Steps To Reproduce symmetric-ds 3.11.5
wildfly 8.1
postgres 9.5
Java 12
jdbc driver : postgresql-42.2.10.jar

Wildfly JNDI datasource
Name:com.sigmaspa.symmetricds
JNDI:java:/com.sigmaspa.symmetricds
Is enabled?:true
Datasource Class:
Driver:postgresql-42.2.10.jar
Driver Class:org.postgresql.Driver
Share Prepared Statements:false
Statement Cache Size:0

Connection URL:jdbc:postgresql://localhost:5432/testdb?protocolVersion=3&stringtype=unspecified
Revision 2024-11-13 18:56 by cquamme
Steps To Reproduce symmetric-ds 3.11.5
wildfly 8.1
postgres 9.5
Java 12
jdbc driver : postgresql-42.2.10.jar

Wildfly JNDI datasource
Name:com.sigmaspa.symmetricds
JNDI:java:/com.sigmaspa.symmetricds
Is enabled?:true
Datasource Class:
Driver:postgresql-42.2.10.jar
Driver Class:org.postgresql.Driver
Share Prepared Statements:false
Statement Cache Size:0

Connection URL:jdbc:postgresql://localhost:5432/testdb?protocolVersion=3&stringtype=unspecified
Revision 2020-03-04 15:38 by emmedierre
Additional Information I tried to compile symmetric-client.jar and i found that in my case
In ClientSymmetricEngine.createTargetDialect() method (line 303 in ClientSymmetricEngine.java)
the node results "NODE_LOAD_ONLY", but i didn't configure the node to be "load-only" !
Revision 2024-11-13 18:56 by cquamme
Additional Information I tried to compile symmetric-client.jar and i found that in my case
In ClientSymmetricEngine.createTargetDialect() method (line 303 in ClientSymmetricEngine.java)
the node results "NODE_LOAD_ONLY", but i didn't configure the node to be "load-only" !