Re: [h2] DELETE statement causes the database to multiply in size and stuck.

[Noel Grandin]

HI

There probably is something wrong with our execution of that query, but I'm afraid it's too complex to be debuggable 
just from the overview.


Unless you can prepare us a self-contained test-case, that won't be much we can 
do, sorry.

Regards, Noel.

--
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/4554dda2-4d69-7cc1-c437-d6d3207da12c%40gmail.com.

Re: [h2] DELETE statement causes the database to multiply in size and stuck.

[Vojtěch Jaroš]

I found an issue with the SQL command, the second table has a where clause
which is always true:

*where T3_T1_FID =  T3_T1_FID *
The redacted file with the exception has it wrong, I made a mistake when
renaming the tables for sending here. Sorry for that. I will run my
application again with the correct statement and see if it's OK now.

Anyway, even the wrong SQL shouldn't cause this behavior so in case you
want to have a look into it, the explain plan (for the buggy query that was
used) says:

DELETE FROM "PUBLIC"."T1"
/* PUBLIC.PRIMARY_KEY_C: T1_ID IN(SELECT DISTINCT
T1_ID
FROM PUBLIC.T1
/* PUBLIC.T1_INDEX */
WHERE NOT EXISTS((SELECT
NULL
FROM PUBLIC.T2
/* PUBLIC.CONSTRAINT_INDEX_9A0: T2_T1_FID = T1_ID */
WHERE T2_T1_FID = T1_ID)
UNION
(SELECT
NULL
FROM PUBLIC.T3
/* PUBLIC.CONSTRAINT_INDEX_1ED */
WHERE T3_T1_FID = T3_T1_FID)))
 */
WHERE "T1_ID" IN(SELECT DISTINCT
"T1_ID"
FROM "PUBLIC"."T1"
/* PUBLIC.T1_INDEX */
WHERE NOT EXISTS((SELECT
NULL
FROM "PUBLIC"."T2"
/* PUBLIC.CONSTRAINT_INDEX_9A0: T2_T1_FID = T1_ID */
WHERE "T2_T1_FID" = "T1_ID")
UNION
(SELECT
NULL
FROM "PUBLIC"."T3"
/* PUBLIC.CONSTRAINT_INDEX_1ED */
WHERE "T3_T1_FID" = "T3_T1_FID")))

By the way, when stopping the process of deleting, the DB had 148GB. After
reopening the database and shutting it down with SHUTDOWN COMPACT, it has
1.4GB.

so 19. 11. 2022 v 11:25 odesílatel Noel Grandin 
napsal:

> What does
>EXPLAIN PLAN 
> show?
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "H2 Database" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/h2-database/DckQhsI73i0/unsubscribe.
> To unsubscribe from this group and all its topics, 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/CAFYHVnVEfV9czquzb-nXy4j8kWeytZN4AjXg7TpCoz0yA8wO-w%40mail.gmail.com
> 
> .
>

-- 
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/CAMOSmwGTPrdpWzV5zBGXzhFBC7PmQ3dF7UA%2B7hktScdN292nXg%40mail.gmail.com.

Re: [h2] DELETE statement causes the database to multiply in size and stuck.

[Noel Grandin]

What does
   EXPLAIN PLAN 
show?

-- 
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/CAFYHVnVEfV9czquzb-nXy4j8kWeytZN4AjXg7TpCoz0yA8wO-w%40mail.gmail.com.

[h2] DELETE statement causes the database to multiply in size and stuck.

[Vojtěch Jaroš]

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