Hello, we are using a H2 database that is normally ~20 GB in size. It was created from scratch with the current version of H2 (2.1.214). Every time when it reaches some size which was not exactly determined, but something over 20 GB, the cleanup DELETE query that normally takes just a few minutes gets stuck for hours. During these hours, CPU is low, but the DB is growing in size, reaching the size of the partition (currently 200 GB). If I interrupt the process (^C), I get the attached error (table names and app packages redacted, but otherwise no change).
The query is: *delete from T1 where T1_ID in (select T1_ID from T1 where not exists (select null from T2 where T2_T1_FID=T1_ID union select null from T3 where T3_T1_FID=T1_ID))* where: *T1_ID is the primary key of T1T2_T1_FID is a foreign key in T2 pointing to T1_ID T3_T1_FID is a foreign key in T3 pointing to T1_ID* the JDBC string is: *jdbc:h2:file;DB_CLOSE_ON_EXIT=FALSE;CACHE_SIZE=2097152;MAX_COMPACT_TIME=67890* The goal is to delete orphan entries in T1 that are not referenced in neither T2 nor T3. When the DB is smaller, this doesn't happen, now it happened for the third time when building the DB from scratch. When the union statement was not present (there was just T2 and no T3), it worked too. Is this a known bug? Or any other ideas? (Maybe this kind of query is not supported in H2? I'm not a DB specialist). Vojtěch Jaroš -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/ec7b2bf1-ce7e-4129-a367-51be6ecbb33an%40googlegroups.com.
org.h2.jdbc.JdbcSQLNonTransientException: General error: "org.h2.mvstore.MVStoreException: Reading from file sun.nio.ch.FileChannelImpl@4b423192 failed at 6485265680 (length -1), read 256, remaining 0 [2.1.214/1]"; SQL statement: delete from T1 where T1_ID in (select T1_ID from T1 where not exists (select null from T2 where T2_T1_FID=T1_ID union select null from T3 where T3_T1_FID=T1_ID)) [50000-214] at org.h2.message.DbException.getJdbcSQLException(DbException.java:554) ~[[redacted app name]:?] at org.h2.message.DbException.getJdbcSQLException(DbException.java:477) ~[[redacted app name]:?] at org.h2.message.DbException.get(DbException.java:212) ~[[redacted app name]:?] at org.h2.message.DbException.convert(DbException.java:395) ~[[redacted app name]:?] at org.h2.command.Command.executeUpdate(Command.java:264) ~[[redacted app name]:?] at org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:190) ~[[redacted app name]:?] at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:143) ~[[redacted app name]:?] [redacted] at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) ~[?:?] at java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[?:?] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304) ~[?:?] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?] at java.lang.Thread.run(Thread.java:829) ~[?:?] Caused by: org.h2.mvstore.MVStoreException: Reading from file sun.nio.ch.FileChannelImpl@4b423192 failed at 6485265680 (length -1), read 256, remaining 0 [2.1.214/1] at org.h2.mvstore.DataUtils.newMVStoreException(DataUtils.java:1004) ~[[redacted app name]:?] at org.h2.mvstore.DataUtils.readFully(DataUtils.java:470) ~[[redacted app name]:?] at org.h2.mvstore.FileStore.readFully(FileStore.java:98) ~[[redacted app name]:?] at org.h2.mvstore.Chunk.readBufferForPage(Chunk.java:422) ~[[redacted app name]:?] at org.h2.mvstore.MVStore.readPage(MVStore.java:2569) ~[[redacted app name]:?] at org.h2.mvstore.MVMap.readPage(MVMap.java:633) ~[[redacted app name]:?] at org.h2.mvstore.Page$NonLeaf.getChildPage(Page.java:1125) ~[[redacted app name]:?] at org.h2.mvstore.Cursor.hasNext(Cursor.java:64) ~[[redacted app name]:?] at org.h2.mvstore.tx.TransactionMap$CommittedIterator.fetchNext(TransactionMap.java:940) ~[[redacted app name]:?] at org.h2.mvstore.db.MVSecondaryIndex$MVStoreCursor.next(MVSecondaryIndex.java:434) ~[[redacted app name]:?] at org.h2.index.IndexCursor.next(IndexCursor.java:287) ~[[redacted app name]:?] at org.h2.table.TableFilter.next(TableFilter.java:425) ~[[redacted app name]:?] at org.h2.command.query.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1832) ~[[redacted app name]:?] at org.h2.result.LazyResult.hasNext(LazyResult.java:78) ~[[redacted app name]:?] at org.h2.result.FetchedResult.next(FetchedResult.java:34) ~[[redacted app name]:?] at org.h2.command.query.Select.queryFlat(Select.java:728) ~[[redacted app name]:?] at org.h2.command.query.Select.queryWithoutCache(Select.java:833) ~[[redacted app name]:?] at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:197) ~[[redacted app name]:?] at org.h2.command.query.Query.query(Query.java:494) ~[[redacted app name]:?] at org.h2.command.query.Query.query(Query.java:475) ~[[redacted app name]:?] at org.h2.command.query.SelectUnion.queryWithoutCache(SelectUnion.java:171) ~[[redacted app name]:?] at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:197) ~[[redacted app name]:?] at org.h2.command.query.Query.executeExists(Query.java:559) ~[[redacted app name]:?] at org.h2.command.query.Query.exists(Query.java:535) ~[[redacted app name]:?] at org.h2.expression.condition.ExistsPredicate.getValue(ExistsPredicate.java:25) ~[[redacted app name]:?] at org.h2.expression.condition.ConditionNot.getValue(ConditionNot.java:36) ~[[redacted app name]:?] at org.h2.expression.Expression.getBooleanValue(Expression.java:332) ~[[redacted app name]:?] at org.h2.command.query.Select.isConditionMet(Select.java:449) ~[[redacted app name]:?] at org.h2.command.query.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1835) ~[[redacted app name]:?] at org.h2.result.LazyResult.hasNext(LazyResult.java:78) ~[[redacted app name]:?] at org.h2.result.FetchedResult.next(FetchedResult.java:34) ~[[redacted app name]:?] at org.h2.command.query.Select.queryFlat(Select.java:728) ~[[redacted app name]:?] at org.h2.command.query.Select.queryWithoutCache(Select.java:833) ~[[redacted app name]:?] at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:197) ~[[redacted app name]:?] at org.h2.command.query.Query.query(Query.java:512) ~[[redacted app name]:?] at org.h2.command.query.Query.query(Query.java:475) ~[[redacted app name]:?] at org.h2.expression.condition.ConditionInQuery.getValue(ConditionInQuery.java:68) ~[[redacted app name]:?] at org.h2.expression.condition.ConditionInQuery.getValue(ConditionInQuery.java:55) ~[[redacted app name]:?] at org.h2.expression.Expression.getBooleanValue(Expression.java:332) ~[[redacted app name]:?] at org.h2.command.dml.Delete.update(Delete.java:68) ~[[redacted app name]:?] at org.h2.command.dml.DataChangeStatement.update(DataChangeStatement.java:74) ~[[redacted app name]:?] at org.h2.command.CommandContainer.update(CommandContainer.java:169) ~[[redacted app name]:?] at org.h2.command.Command.executeUpdate(Command.java:252) ~[[redacted app name]:?] ... 13 more Caused by: java.nio.channels.ClosedByInterruptException at java.nio.channels.spi.AbstractInterruptibleChannel.end(AbstractInterruptibleChannel.java:199) ~[?:?] at sun.nio.ch.FileChannelImpl.endBlocking(FileChannelImpl.java:162) ~[?:?] at sun.nio.ch.FileChannelImpl.readInternal(FileChannelImpl.java:816) ~[?:?] at sun.nio.ch.FileChannelImpl.read(FileChannelImpl.java:796) ~[?:?] at org.h2.mvstore.DataUtils.readFully(DataUtils.java:456) ~[[redacted app name]:?] at org.h2.mvstore.FileStore.readFully(FileStore.java:98) ~[[redacted app name]:?] at org.h2.mvstore.Chunk.readBufferForPage(Chunk.java:422) ~[[redacted app name]:?] at org.h2.mvstore.MVStore.readPage(MVStore.java:2569) ~[[redacted app name]:?] at org.h2.mvstore.MVMap.readPage(MVMap.java:633) ~[[redacted app name]:?] at org.h2.mvstore.Page$NonLeaf.getChildPage(Page.java:1125) ~[[redacted app name]:?] at org.h2.mvstore.Cursor.hasNext(Cursor.java:64) ~[[redacted app name]:?] at org.h2.mvstore.tx.TransactionMap$CommittedIterator.fetchNext(TransactionMap.java:940) ~[[redacted app name]:?] at org.h2.mvstore.db.MVSecondaryIndex$MVStoreCursor.next(MVSecondaryIndex.java:434) ~[[redacted app name]:?] at org.h2.index.IndexCursor.next(IndexCursor.java:287) ~[[redacted app name]:?] at org.h2.table.TableFilter.next(TableFilter.java:425) ~[[redacted app name]:?] at org.h2.command.query.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1832) ~[[redacted app name]:?] at org.h2.result.LazyResult.hasNext(LazyResult.java:78) ~[[redacted app name]:?] at org.h2.result.FetchedResult.next(FetchedResult.java:34) ~[[redacted app name]:?] at org.h2.command.query.Select.queryFlat(Select.java:728) ~[[redacted app name]:?] at org.h2.command.query.Select.queryWithoutCache(Select.java:833) ~[[redacted app name]:?] at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:197) ~[[redacted app name]:?] at org.h2.command.query.Query.query(Query.java:494) ~[[redacted app name]:?] at org.h2.command.query.Query.query(Query.java:475) ~[[redacted app name]:?] at org.h2.command.query.SelectUnion.queryWithoutCache(SelectUnion.java:171) ~[[redacted app name]:?] at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:197) ~[[redacted app name]:?] at org.h2.command.query.Query.executeExists(Query.java:559) ~[[redacted app name]:?] at org.h2.command.query.Query.exists(Query.java:535) ~[[redacted app name]:?] at org.h2.expression.condition.ExistsPredicate.getValue(ExistsPredicate.java:25) ~[[redacted app name]:?] at org.h2.expression.condition.ConditionNot.getValue(ConditionNot.java:36) ~[[redacted app name]:?] at org.h2.expression.Expression.getBooleanValue(Expression.java:332) ~[[redacted app name]:?] at org.h2.command.query.Select.isConditionMet(Select.java:449) ~[[redacted app name]:?] at org.h2.command.query.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1835) ~[[redacted app name]:?] at org.h2.result.LazyResult.hasNext(LazyResult.java:78) ~[[redacted app name]:?] at org.h2.result.FetchedResult.next(FetchedResult.java:34) ~[[redacted app name]:?] at org.h2.command.query.Select.queryFlat(Select.java:728) ~[[redacted app name]:?] at org.h2.command.query.Select.queryWithoutCache(Select.java:833) ~[[redacted app name]:?] at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:197) ~[[redacted app name]:?] at org.h2.command.query.Query.query(Query.java:512) ~[[redacted app name]:?] at org.h2.command.query.Query.query(Query.java:475) ~[[redacted app name]:?] at org.h2.expression.condition.ConditionInQuery.getValue(ConditionInQuery.java:68) ~[[redacted app name]:?] at org.h2.expression.condition.ConditionInQuery.getValue(ConditionInQuery.java:55) ~[[redacted app name]:?] at org.h2.expression.Expression.getBooleanValue(Expression.java:332) ~[[redacted app name]:?] at org.h2.command.dml.Delete.update(Delete.java:68) ~[[redacted app name]:?] at org.h2.command.dml.DataChangeStatement.update(DataChangeStatement.java:74) ~[[redacted app name]:?] at org.h2.command.CommandContainer.update(CommandContainer.java:169) ~[[redacted app name]:?] at org.h2.command.Command.executeUpdate(Command.java:252) ~[[redacted app name]:?] ... 13 more