Error 90112 is being passed to the application

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Open the attached database using the standard connection options
2. Execute DELETE FROM FLTWINDS.ALERTS WHERE TIMERECEIVED < 1239015340745;

This should simply deleted the matching rows. Instead I get
org.h2.jdbc.JdbcSQLException: Row not found when trying to delete from 
index FLTWINDS.PRIMARY_KEY_7; SQL statement:
DELETE FROM FLTWINDS.ALERTS WHERE `TIMERECEIVED` < ?; [90112-110]
    at org.h2.message.Message.getSQLException(Message.java:107)
    at org.h2.message.Message.getSQLException(Message.java:118)
    at org.h2.message.Message.getSQLException(Message.java:77)
    at org.h2.index.BtreeLeaf.remove(BtreeLeaf.java:132)
    at org.h2.index.BtreeNode.remove(BtreeNode.java:144)
    at org.h2.index.BtreeIndex.remove(BtreeIndex.java:256)
    at org.h2.table.TableData.removeRow(TableData.java:317)
    at org.h2.command.dml.Delete.update(Delete.java:69)
    at org.h2.command.CommandContainer.update(CommandContainer.java:71)
    at org.h2.command.Command.executeUpdate(Command.java:207)
    at 
org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStateme
nt.java:139)
    at 
org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:
128)

The online error analyzer states that the error code 90112 is an internal 
error code that should be handled internally. If I open the attached 
database using RECOVER=1 the index is rebuilt and the query executes 
correctly.

Original issue reported on code.google.com by pepijn.v...@gmail.com on 6 Apr 2009 at 7:30

Attachments:

• db.zip

[GoogleCodeExporter]

Hi,

The problem seems to be that the index is corrupt. To fix the problem, close all
connections from the database and delete the .index.db file. This solved the 
problem
for me.

In the .trace.db file there are some exceptions that say some connection were 
not
closed, but garbage collection. It's better to close all connections in the
application, specially when the application is stopped or when the web 
application is
unloaded.

The database was created with version 1.0.79 (the CREATE_BUILD setting is 79; 
SELECT
* FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME='CREATE_BUILD'). Since then, some
problems that are related to such problems have been fixed:

- Version 1.1.108 (2009-02-28):
  When the shutdown hook closed the database, 
  the last log file was deleted too early. 
  This could cause uncommitted changes to be persisted.

- Version 1.1.111 (2009-04-10):
  Queries that are ordered by an indexed column 
  returned no rows in certain cases

It's not sure that the problem was caused by this problem, but it could be.

I am very interested in analyzing and solving this problem. Corruption problems 
have
top priority for me. I have a few questions:

- What is your database URL?
- Do you use Tomcat or another web server? Do you unload or reload the web 
application?
- Did you use multiple connections?
- Do you use any settings or special features (for example, the setting LOG=0,
    or two phase commit, linked tables, cache settings)?
- Is the application multi-threaded?
- What operating system, file system, and virtual machine
    (java -version) do you use?
- Is it (or was it at some point) a networked file system?
- Is the database usually closed normally, or is process terminated forcefully
    or the computer switched off?
- Is it possible to reproduce this problem using a fresh database
    (sometimes, or always)?

Original comment by thomas.t...@gmail.com on 13 Apr 2009 at 2:56

[GoogleCodeExporter]

The database is used as a local datastore, H2 is running in a J2SE application 
in 
embedded mode. The database can be long lived, so it was probably created using 
1.0.79, but we have upgraded to the latest available release in the meantime 
and the 
exception still occurs.

My guess would be that the index file got corrupted due to my code not properly 
closing connections either due to a bug in my code or a killing of the process. 
This 
is not really the issue; I don't think there is anything you can do to handle 
this 
case. It would be nice if this case was detected and handled by H2 itself 
though 
instead of throwing an exception. Not sure if this is possible though.

Below are answers to your specific questions:
- What is your database URL?
Normally we would like to use
jdbc:h2:c:/Users/pepijn/AppData/Local/.db/pepijn
but currently I'm using
jdbc:h2:c:/Users/pepijn/AppData/Local/.db/pepijn;RECOVER=1
to force index recreation

- Do you use Tomcat or another web server? Do you unload or reload the web 
application?
No

- Did you use multiple connections?
Yes, this has been changed in our code in the meantime though. The code was 
maintaining connections in ThreadLocals, in other words a new connection was 
created 
per thread and these connections were not properly closed.

- Do you use any settings or special features (for example, the setting LOG=0,
    or two phase commit, linked tables, cache settings)?
No

- Is the application multi-threaded?
Yes

- What operating system, file system, and virtual machine
    (java -version) do you use?
Windows XP SP2, NTFS, 1.6.0_07

- Is it (or was it at some point) a networked file system?
No

- Is the database usually closed normally, or is process terminated forcefully
    or the computer switched off?
See comment on multiple connections. Our code was not correctly closing 
connections 
and that is most likely the root cause of the index corruption. It may be that 
the 
process was killed as well. This was reported by a user of the application in 
question 
and he did not provide me with an exact scenario

- Is it possible to reproduce this problem using a fresh database
    (sometimes, or always)?
No this happens periodically.

Original comment by pepijn.v...@gmail.com on 14 Apr 2009 at 9:43

[GoogleCodeExporter]

> we have upgraded to the latest available release in the meantime 
> and the exception still occurs.

Does the exception still occur using a fresh database,
or using a database that was created using an older version?
To find out what version of H2 as used to create a database, execute:
SELECT * FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME='CREATE_BUILD'

The problem is, the data in the .data.db file could be corrupt,
and in that case deleting the .index.db file will not help.

Original comment by thomas.t...@gmail.com on 18 Apr 2009 at 3:32

[GoogleCodeExporter]

> Does the exception still occur using a fresh database,
> or using a database that was created using an older version?
> To find out what version of H2 as used to create a database, execute:
> SELECT * FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME='CREATE_BUILD'
After my code changes and the upgrade I haven't seen this issue anymore. 
Problem is I 
can't tell you which of the two changes made the difference. If I find some 
time I 
will try to create a simple application that duplicates as close as possible my 
usage 
of the database. If I can reproduce the issue with 1.0.79 I can then try the 
same 
thing with the latest release.

> The problem is, the data in the .data.db file could be corrupt,
> and in that case deleting the .index.db file will not help.
True. I was looking at this from the point of view that I simply wanted my 
application to start without errors. I hadn't considered data corruption yet.

Original comment by pepijn.v...@gmail.com on 20 Apr 2009 at 6:25

[GoogleCodeExporter]

I am setting this issue as 'Fixed' for now. Please re-open or create a new 
issue if
the problem occurs again using a fresh database.

Thanks!

Original comment by thomas.t...@gmail.com on 27 Apr 2009 at 6:29

• Changed state: Fixed