Simple query timed out deleting alot of rows. Any suggestions to avoid this exception [57014-182]?

[Kenton Garner]

Any suggestions to avoid this exception?
We preform a clean up of history files older than one day.  In this test we were running full out and our database file was approx 1.2Gb in size. 
We were down for a day and when we ran the next time it tried to clean up the old records and apparently it took too long and threw this exception.

"Statement was canceled or the session timed out; SQL statement: ... [57014-182] "

Shutdown requested on error by Thread: "Thread-4"
com.issinc.cds.sbrecv.history.RecvHistoryException: Exception deleting History [DELETE CDS_HISTORY WHERE MSGDATE <= ? LIMIT 10000]:
at com.issinc.cds.sbrecv.history.RecvHistoryDBStore.deleteOlderThanMsgDate(RecvHistoryDBStore.java:555)
at com.issinc.cds.sbrecv.history.RecvHistoryDBManager$HistoryTask.run(RecvHistoryDBManager.java:147)
Caused by: org.h2.jdbc.JdbcBatchUpdateException: Statement was canceled or the session timed out; SQL statement:
DELETE CDS_HISTORY WHERE MSGDATE <= ? LIMIT 10000 [57014-182]
at org.h2.jdbc.JdbcPreparedStatement.executeBatch(JdbcPreparedStatement.java:1198)
at com.zaxxer.hikari.proxy.PreparedStatementJavassistProxy.executeBatch(PreparedStatementJavassistProxy.java)
at com.issinc.cds.sbrecv.history.RecvHistoryDBStore.deleteOlderThanMsgDate(RecvHistoryDBStore.java:547)
... 1 more

 

[Noel Grandin]

Need whole exception.

[Kenton Garner]

Sorry, thought I did give you everything.  However, there was a tad more in the trace file ( see bottom ).

Some additional information:  This process executes (n) jdbc batch statements each deleting up to 10,000 rows at a time.

          // Find multiple of 10,000 rows to determine how many delete queries to add to batch
          int totalQueries = (nTotalRows / 10000) + (((nTotalRows % 10000) > 0) ? 1 : 0);

          for ( int batch = 0; batch < totalQueries; batch++ )
          {
            pstmt.setString( 1, msgDate );
            pstmt.addBatch();
          }

          rows = pstmt.executeBatch();

11-07 19:12:46 jdbc[3]: exception

org.h2.jdbc.JdbcBatchUpdateException: Statement was canceled or the session timed out; SQL statement:
DELETE CDS_HISTORY WHERE MSGDATE <= ? LIMIT 10000 [57014-182]
    at org.h2.jdbc.JdbcPreparedStatement.executeBatch(JdbcPreparedStatement.java:1198)
    at com.zaxxer.hikari.proxy.PreparedStatementJavassistProxy.executeBatch(PreparedStatementJavassistProxy.java)
    at com.issinc.cds.sbrecv.history.RecvHistoryDBStore.deleteOlderThanMsgDate(RecvHistoryDBStore.java:547)

    at com.issinc.cds.sbrecv.history.RecvHistoryDBManager$HistoryTask.run(RecvHistoryDBManager.java:147)
org.h2.jdbc.JdbcSQLException: Statement was canceled or the session timed out; SQL statement:

DELETE CDS_HISTORY WHERE MSGDATE <= ? LIMIT 10000 [57014-182]

    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
    at org.h2.message.DbException.get(DbException.java:144)
    at org.h2.engine.Session.checkCanceled(Session.java:1034)
    at org.h2.command.Prepared.checkCanceled(Prepared.java:275)
    at org.h2.command.dml.Delete.update(Delete.java:91)
    at org.h2.command.CommandContainer.update(CommandContainer.java:78)
    at org.h2.command.Command.executeUpdate(Command.java:254)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:157)
    at org.h2.jdbc.JdbcPreparedStatement.executeBatch(JdbcPreparedStatement.java:1183)

    at com.zaxxer.hikari.proxy.PreparedStatementJavassistProxy.executeBatch(PreparedStatementJavassistProxy.java)
    at com.issinc.cds.sbrecv.history.RecvHistoryDBStore.deleteOlderThanMsgDate(RecvHistoryDBStore.java:547)

    at com.issinc.cds.sbrecv.history.RecvHistoryDBManager$HistoryTask.run(RecvHistoryDBManager.java:147)
org.h2.jdbc.JdbcSQLException: Statement was canceled or the session timed out; SQL statement:

DELETE CDS_HISTORY WHERE MSGDATE <= ? LIMIT 10000 [57014-182]

On Monday, November 10, 2014 2:04:41 AM UTC-5, Noel Grandin wrote:

Need whole exception.

[Noel Grandin]

Assuming you have not set the MAX_QUERY_TIMEOUT database setting, the only other answer is that something else in your
stack is cancelling the operation by closing that connection.

[Kenton Garner]

To follow up ( better late then never ) ....
Noel you were right, but my issue was misrepresented.

The problem turned out to be the initial select statement that was determining the number of records I needed to delete. 
It was timing out because in my environment the JDBC Statement Query Timeout was defaulting to 3 seconds.  The select count(*).... query was taking about 5 seconds to return. 
I had call the setQueryTimeout() method on the JDBC Statement object to increase the query timeout. 
My initial interpretation of the problem was incorrect.   The JDBC batched loop to delete the records in chunks worked fine.