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)) [5-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
