[jboss-jira] [JBoss JIRA] (WFLY-12887) Clustering: com.ibm.db2.jcc.am.SqlSyntaxErrorException when DB2 is used as invalidation-cache

Clustering: com.ibm.db2.jcc.am.SqlSyntaxErrorException when DB2 is used as invalidation-cache --------------------------------------------------------------------------------------------- Key: WFLY-12887 URL: https://issues.redhat.com/browse/WFLY-12887 Project: WildFly Issue Type: Bug Components: Clustering Affects Versions: 19.0.0.Beta1 Reporter: Tommasso Borgato Assignee: Paul Ferraro Priority: Critical The error happens in clustering tests where the cache is backed by a DB2 relational database; The cache is configured as follows: {noformat} embed-server --server-config=standalone-ha.xml /subsystem=jgroups/channel=ee:write-attribute(name=stack,value=tcp) /subsystem=infinispan/cache-container=web/invalidation-cache=offload:add() /subsystem=infinispan/cache-container=web/invalidation-cache==offload/component=transaction:write-attribute(name=mode, value=BATCH) /subsystem=infinispan/cache-container=web/invalidation-cache=offload/component=locking:write-attribute(name=isolation, value=REPEATABLE_READ) data-source add --name=testDS --jndi-name=java:jboss/datasources/testDS --driver-name=db2-connector.jar --connection-url=jdbc:db2://db2-111.hosts.mwqe.eng.bos.redhat.com:50000/dballo --enabled=true --jta=true --use-java-context=true --transaction-isolation=TRANSACTION_READ_COMMITTED --min-pool-size=1 --max-pool-size=5 --pool-prefill=true --user-name=dballo00 --password=dballo00 --prepared-statements-cache-size=32 --share-prepared-statements=true /subsystem=infinispan/cache-container=web/invalidation-cache=offload/store=jdbc:add(data-source=testDS,fetch-state=false,passivation=false,purge=false,shared=true,dialect=DB2){allow-resource-service-restart=true} /subsystem=infinispan/cache-container=web/invalidation-cache=offload/store=jdbc/table=string:write-attribute(name=prefix,value=s) /subsystem=infinispan/cache-container=web/invalidation-cache=offload/store=jdbc/table=string:write-attribute(name=id-column.name,value=id) /subsystem=infinispan/cache-container=web/invalidation-cache=offload/store=jdbc/table=string:write-attribute(name=id-column.type,value=VARCHAR(255)) /subsystem=infinispan/cache-container=web/invalidation-cache=offload/store=jdbc/table=string:write-attribute(name=data-column.name,value=datum) /subsystem=infinispan/cache-container=web/invalidation-cache=offload/store=jdbc/table=string:write-attribute(name=data-column.type,value=BLOB) /subsystem=infinispan/cache-container=web/invalidation-cache=offload/store=jdbc/table=string:write-attribute(name=timestamp-column.name,value=version) /subsystem=infinispan/cache-container=web/invalidation-cache=offload/store=jdbc/table=string:write-attribute(name=timestamp-column.type,value=BIGINT) /subsystem=infinispan/cache-container=web:write-attribute(name=default-cache, value=offload) /subsystem=transactions:write-attribute(name=node-identifier,value=wildfly1) /subsystem=datasources/data-source=testDS:write-attribute(name=spy,value=true) /subsystem=logging/logger=jboss.jdbc.spy:add(level=TRACE) {noformat} The error is the following: {noformat} 2019-12-16 16:49:31,961 DEBUG [jboss.jdbc.spy] (expiration-thread--p24-t1) java:jboss/datasources/testDS [DataSource] getConnection() 2019-12-16 16:49:31,961 DEBUG [jboss.jdbc.spy] (expiration-thread--p24-t1) java:jboss/datasources/testDS [Connection] setAutoCommit(false) 2019-12-16 16:49:31,962 DEBUG [jboss.jdbc.spy] (expiration-thread--p24-t1) java:jboss/datasources/testDS [Connection] prepareStatement(SELECT datum, id FROM "s_cbnc_ear_a_war" WHERE version < ? AND version > 0 FOR UPDATE) 2019-12-16 16:49:31,971 DEBUG [jboss.jdbc.spy] (expiration-thread--p24-t1) java:jboss/datasources/testDS [PreparedStatement] setLong(1, 1576514971971) 2019-12-16 16:49:31,971 DEBUG [jboss.jdbc.spy] (expiration-thread--p24-t1) java:jboss/datasources/testDS [PreparedStatement] executeQuery() 2019-12-16 16:49:31,987 DEBUG [jboss.jdbc.spy] (persistence-thread--p25-t4) java:jboss/datasources/testDS [Connection] close() 2019-12-16 16:49:31,991 DEBUG [jboss.jdbc.spy] (persistence-thread--p25-t2) java:jboss/datasources/testDS [DataSource] getConnection() 2019-12-16 16:49:31,992 DEBUG [jboss.jdbc.spy] (persistence-thread--p25-t2) java:jboss/datasources/testDS [Connection] isValid(10) 2019-12-16 16:49:31,998 ERROR [org.infinispan.persistence.jdbc.stringbased.JdbcStringBasedStore] (expiration-thread--p24-t1) ISPN008001: Failed clearing cache store: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DBALLO00.s_cbnc_ear_a_war, DRIVER=4.25.1301 at deployment.db2-connector.jar//com.ibm.db2.jcc.am.b6.a(b6.java:810) at deployment.db2-connector.jar//com.ibm.db2.jcc.am.b6.a(b6.java:66) at deployment.db2-connector.jar//com.ibm.db2.jcc.am.b6.a(b6.java:140) at deployment.db2-connector.jar//com.ibm.db2.jcc.am.k3.c(k3.java:2824) at deployment.db2-connector.jar//com.ibm.db2.jcc.am.k3.d(k3.java:2808) at deployment.db2-connector.jar//com.ibm.db2.jcc.am.k3.a(k3.java:2234) at deployment.db2-connector.jar//com.ibm.db2.jcc.am.k4.a(k4.java:8242) at deployment.db2-connector.jar//com.ibm.db2.jcc.t4.ab.i(ab.java:206) at deployment.db2-connector.jar//com.ibm.db2.jcc.t4.ab.b(ab.java:96) at deployment.db2-connector.jar//com.ibm.db2.jcc.t4.p.a(p.java:32) at deployment.db2-connector.jar//com.ibm.db2.jcc.t4.av.i(av.java:150) at deployment.db2-connector.jar//com.ibm.db2.jcc.am.k3.al(k3.java:2203) at deployment.db2-connector.jar//com.ibm.db2.jcc.am.k4.bq(k4.java:3730) at deployment.db2-connector.jar//com.ibm.db2.jcc.am.k4.a(k4.java:4609) at deployment.db2-connector.jar//com.ibm.db2.jcc.am.k4.b(k4.java:4182) at deployment.db2-connector.jar//com.ibm.db2.jcc.am.k4.bd(k4.java:780) at deployment.db2-connector.jar//com.ibm.db2.jcc.am.k4.executeQuery(k4.java:745) at org.jboss.ironjacamar.jdbcadapters@1.4.19.Final//org.jboss.jca.adapters.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:113) at org.jboss.ironjacamar.jdbcadapters@1.4.19.Final//org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504) at org.infinispan.persistence.jdbc@9.4.16.Final//org.infinispan.persistence.jdbc.stringbased.JdbcStringBasedStore.purge(JdbcStringBasedStore.java:456) at org.infinispan@9.4.16.Final//org.infinispan.persistence.manager.PersistenceManagerImpl.lambda$purgeExpired$6(PersistenceManagerImpl.java:470) at java.base/java.util.ArrayList.forEach(ArrayList.java:1540) at org.infinispan@9.4.16.Final//org.infinispan.persistence.manager.PersistenceManagerImpl.purgeExpired(PersistenceManagerImpl.java:473) at org.infinispan@9.4.16.Final//org.infinispan.expiration.impl.ExpirationManagerImpl.processExpiration(ExpirationManagerImpl.java:111) at org.infinispan@9.4.16.Final//org.infinispan.expiration.impl.ExpirationManagerImpl$ScheduledTask.run(ExpirationManagerImpl.java:245) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305) at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at org.jboss.as.clustering.common@19.0.0.Beta1-SNAPSHOT//org.jboss.as.clustering.context.ContextReferenceExecutor.execute(ContextReferenceExecutor.java:49) at org.jboss.as.clustering.common@19.0.0.Beta1-SNAPSHOT//org.jboss.as.clustering.context.ContextualExecutor$1.run(ContextualExecutor.java:70) at java.base/java.lang.Thread.run(Thread.java:834) {noformat} The issue is set as "Blocker" because DB2 is one of the supported databases and we don't have a workaround at present