+1 to this topic.
We are on version 1.3.172, with connection string "
jdbc:h2:./data/db/db;MVCC=TRUE;MAX_COMPACT_TIME=6000;" -- so no
experimental flags or anything in my case.
One of our customers consistently produces "Row not found when trying to
delete from index" on 2 tables. Here are the exceptions:
==== In table "lease"
javax.persistence.PersistenceException: org.h2.jdbc.JdbcSQLException: Row
not found when trying to delete from index
"PUBLIC.I_LEASE_CREATED_AT_UPDATED_AT_TOKEN_ID: ( /* key:14162 */ 14162,
42, 101, FALSE, TIMESTAMP '2013-11-03 01:14:30.302', TIMESTAMP '2013-11-03
01:44:28.159', TIMESTAMP '2013-11-03 01:44:28.159', TRUE, NULL, NULL,
-2318)"; SQL statement:
DELETE FROM lease WHERE expired = 1 AND updated_at < ? LIMIT 10000
[90112-172]
at
com.avaje.ebeaninternal.server.persist.ExeUpdateSql.execute(ExeUpdateSql.java:76)
~[license-server.jar:2.2.1]
at
com.avaje.ebeaninternal.server.persist.DefaultPersistExecute.executeSqlUpdate(DefaultPersistExecute.java:115)
~[license-server.jar:2.2.1]
at
com.avaje.ebeaninternal.server.core.PersistRequestUpdateSql.executeNow(PersistRequestUpdateSql.java:44)
~[license-server.jar:2.2.1]
at
com.avaje.ebeaninternal.server.core.PersistRequest.executeStatement(PersistRequest.java:74)
~[license-server.jar:2.2.1]
at
com.avaje.ebeaninternal.server.core.PersistRequestUpdateSql.executeOrQueue(PersistRequestUpdateSql.java:49)
~[license-server.jar:2.2.1]
at
com.avaje.ebeaninternal.server.persist.DefaultPersister.executeSqlUpdate(DefaultPersister.java:139)
~[license-server.jar:2.2.1]
Caused by: org.h2.jdbc.JdbcSQLException: Row not found when trying to
delete from index "PUBLIC.I_LEASE_CREATED_AT_UPDATED_AT_TOKEN_ID: ( /*
key:14162 */ 14162, 42, 101, FALSE, TIMESTAMP '2013-11-03 01:14:30.302',
TIMESTAMP '2013-11-03 01:44:28.159', TIMESTAMP '2013-11-03 01:44:28.159',
TRUE, NULL, NULL, -2318)"; SQL statement:
DELETE FROM lease WHERE expired = 1 AND updated_at < ? LIMIT 10000
[90112-172]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
~[license-server.jar:2.2.1]
at org.h2.message.DbException.get(DbException.java:169)
~[license-server.jar:2.2.1]
at org.h2.message.DbException.get(DbException.java:146)
~[license-server.jar:2.2.1]
at org.h2.index.PageBtreeLeaf.remove(PageBtreeLeaf.java:230)
~[license-server.jar:2.2.1]
at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:332)
~[license-server.jar:2.2.1]
at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:332)
~[license-server.jar:2.2.1]
The index definition for this table (copy-pasting liquibase XML) :
<createIndex tableName="lease"
indexName="i_lease_created_at_updated_at_token_id">
<column name="created_at"/>
<column name="updated_at"/>
<column name="token_id"/>
</createIndex>
==== In table "access_token"
Caused by: org.h2.jdbc.JdbcSQLException: Row not found when trying to
delete from index "PUBLIC.I_SEATS_SAMPLE_CREATED_AT: ( /* key:5057781 */
5057781, TIMESTAMP '2013-11-03 01:11:00.0', 2, 49, 12)"; SQL statement:
delete from access_token where id=? and name=? and auth_token=? and
subnet_mask=? and max_users is null and is_active=? and is_total_token=?
and created_at=? [90112-172]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
~[license-server.jar:2.2.1]
at org.h2.message.DbException.get(DbException.java:169)
~[license-server.jar:2.2.1]
at org.h2.message.DbException.get(DbException.java:146)
~[license-server.jar:2.2.1]
at org.h2.index.PageBtreeLeaf.remove(PageBtreeLeaf.java:230)
~[license-server.jar:2.2.1]
at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:332)
~[license-server.jar:2.2.1]
at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:332)
~[license-server.jar:2.2.1]
Here's the index definition for seats_sample table that looks to fail:
<createIndex tableName="seats_sample"
indexName="i_seats_sample_created_at">
<column name="created_at"/>
</createIndex>
... and here's the foreign key constraint that connects them and defines
the on-delete action, just in case:
<addForeignKeyConstraint baseTableName="seats_sample"
baseColumnNames="token_id"
constraintName="fk_seats_usage_access_token"
referencedTableName="access_token"
referencedColumnNames="id"
onDelete="SET NULL"/>
The first of those indexes is a multi-column indexes which i doubt that
even "works"... if I investivate see my queries with EXPLAIN, they mostly
don't seem to be used at all (although the columns I query by are exactly
the same). Thus, for at least one of the cases above, the solution for me
is probably to get rid of the multi-column index for good. But anyhow, I
still wanted to raise this here.. there might be some underlying root cause
that maybe could be improved.
It would also be very helpful if somebody could take 5 minutes to explain a
bit what this exceptions substantially means... why shouldn't it not be
able to delete from index? Is the index itself corrupt then, or could it be
some sort of an issue in H2 code? This does not reproduce consinstently,
i.e. it happens for our customer (whose database actually doesn't look to
be corrupt, i.e. application itself works), but we ourselves cannot
reproduce it. Makes me wonder ....
Thanks!
Sander.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
