[infinispan-issues] [JBoss JIRA] (ISPN-11004) SQL server exception purging data

[Viet Linh Dang (Jira)]

    [ https://issues.redhat.com/browse/ISPN-11004?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13962214#comment-13962214 ] 

Viet Linh Dang edited comment on ISPN-11004 at 2/3/20 10:49 AM:
----------------------------------------------------------------

I have the same issue with version 9.4.16


was (Author: dvlinh):
I encountered the same issue with version 9.4.16

> SQL server exception purging data
> ---------------------------------
>
>                 Key: ISPN-11004
>                 URL: https://issues.redhat.com/browse/ISPN-11004
>             Project: Infinispan
>          Issue Type: Bug
>          Components: Loaders and Stores
>    Affects Versions: 10.0.1.Final
>            Reporter: Megan van de Valk
>            Assignee: Ryan Emerson
>            Priority: Major
>
> When using string keyed jdbc store with dialect="SQL_SERVER" a SQL exception of 'FOR UPDATE clause allowed only for DECLARE CURSOR' is thrown.
> {code:java}
> 14:57:39,444 WARN  [org.infinispan.PERSISTENCE] (expiration-thread--p7-t1) ISPN000026: Caught exception purging data container!: org.infinispan.persistence.spi.PersistenceException: Failed clearing string based JDBC store
>         at org.infinispan.persistence.jdbc.stringbased.JdbcStringBasedStore.purge(JdbcStringBasedStore.java:502)
>         at org.infinispan.persistence.spi.SegmentedAdvancedLoadWriteStore.purge(SegmentedAdvancedLoadWriteStore.java:291)
>         at org.infinispan.persistence.manager.PersistenceManagerImpl.lambda$purgeExpired$8(PersistenceManagerImpl.java:522)
>         at java.util.ArrayList.forEach(ArrayList.java:1257)
>         at org.infinispan.persistence.manager.PersistenceManagerImpl.purgeExpired(PersistenceManagerImpl.java:528)
>         at org.infinispan.persistence.support.DelegatingPersistenceManager.purgeExpired(DelegatingPersistenceManager.java:90)
>         at org.infinispan.expiration.impl.ExpirationManagerImpl.processExpiration(ExpirationManagerImpl.java:120)
>         at org.infinispan.expiration.impl.ExpirationManagerImpl$ScheduledTask.run(ExpirationManagerImpl.java:282)
>         at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
>         at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
>         at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
>         at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
>         at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
>         at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
>         at java.lang.Thread.run(Thread.java:748)
> Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.
>         at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
>         at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
>         at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
>         at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
>         at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
>         at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
>         at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
>         at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
>         at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
>         at org.infinispan.persistence.jdbc.stringbased.JdbcStringBasedStore.purge(JdbcStringBasedStore.java:467)
>         ... 14 more
> {code}
> The method initSelectOnlyExpiredRowsSql in [AbstractTableManager|https://github.com/infinispan/infinispan/blob/master/persistence/jdbc/src/main/java/org/infinispan/persistence/jdbc/impl/table/AbstractTableManager.java] includes a FOR UPDATE:
> {code:java}
> return String.format("%1$s WHERE %2$s < ? AND %2$s > 0 FOR UPDATE", getLoadAllRowsSql(), config.timestampColumnName());
> {code}
> It should probably be overwritten in [SQLServerTableManger|https://github.com/infinispan/infinispan/blob/master/persistence/jdbc/src/main/java/org/infinispan/persistence/jdbc/impl/table/SQLServerTableManager.java] with 
> {code:java}
> return String.format("%1$s WITH(UPDLOCK) WHERE %2$s < ? AND %2$s > 0", getLoadAllRowsSql(), config.timestampColumnName());
> {code}



--
This message was sent by Atlassian Jira
(v7.13.8#713008)