TIMESTAMP JDBC ArrayIndexOutOfBoundsException on big tables

[GoogleCodeExporter]

I searched for ArrayIndexOutOfBoundsException in the issues and did not found 
any fitting, so I think this bug is not fixed in a later version. The two 
databases mgmtp-20141128 and mgmtp-20150327 have the same schema, but they are 
differing in the size:

    1161216 Mar 27 12:52 /cc/H2/mgmtp-20141128.h2.db
 2311876608 Mar 27 11:46 /cc/H2/mgmtp-20150327.h2.db

A crucial amount of this difference is a result of the row count in the table 
APP.TBL_LOGS (see below). It seems to me, that any query containing a TIMESTAMP 
comparison is failing with the same exception on the big table. In contrary 
comparisons on other columns are working:

$ e "select min(id), max(id) from APP.TBL_LOGS;" | xd /cc/Program\ Files\ \(x86
\)/H2/bin/ java -cp h2*.jar org.h2.tools.Shell -url jdbc:h2:tcp://localhost/c:/
H2/mgmtp-20150327 -user app -password 000

Welcome to H2 Shell 1.3.169 (2012-09-09)
Exit with Ctrl+C
Commands are case insensitive; SQL statements end with ';'
help or ?      Display this help
list           Toggle result list / stack trace mode
maxwidth       Set maximum column width (default is 100)
autocommit     Enable or disable autocommit
history        Show the last 20 statements
quit or exit   Close the connection and exit

sql> MIN(ID) | MAX(ID)
506452  | 776686
(1 row, 13 ms)
sql> Aborted
Connection closed

$ e "select max(dte_time) from APP.TBL_LOGS;" | xd /cc/Program\ Files\ \(x86\)/
H2/bin/ java -cp h2*.jar org.h2.tools.Shell -url jdbc:h2:tcp://localhost/c:/H2/
mgmtp-20141128 -user app -password 000

Welcome to H2 Shell 1.3.169 (2012-09-09)
Exit with Ctrl+C
Commands are case insensitive; SQL statements end with ';'
help or ?      Display this help
list           Toggle result list / stack trace mode
maxwidth       Set maximum column width (default is 100)
autocommit     Enable or disable autocommit
history        Show the last 20 statements
quit or exit   Close the connection and exit

sql> MAX(DTE_TIME)
2014-11-28 08:39:42.25
(1 row, 44 ms)
sql> Aborted
Connection closed
 ~  46611
$ e "select max(dte_time) from APP.TBL_LOGS;" | xd /cc/Program\ Files\ \(x86\)/
H2/bin/ java -cp h2*.jar org.h2.tools.Shell -url jdbc:h2:tcp://localhost/c:/H2/
mgmtp-20150327 -user app -password 000

Welcome to H2 Shell 1.3.169 (2012-09-09)
Exit with Ctrl+C
Commands are case insensitive; SQL statements end with ';'
help or ?      Display this help
list           Toggle result list / stack trace mode
maxwidth       Set maximum column width (default is 100)
autocommit     Enable or disable autocommit
history        Show the last 20 statements
quit or exit   Close the connection and exit

sql> Error: org.h2.jdbc.JdbcSQLException: Allgemeiner Fehler: "java.lang.ArrayIn
dexOutOfBoundsException: 0"
General error: "java.lang.ArrayIndexOutOfBoundsException: 0"; SQL statement:
select max(dte_time) from APP.TBL_LOGS [50000-169]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
        at org.h2.message.DbException.get(DbException.java:158)
        at org.h2.message.DbException.convert(DbException.java:281)
        at org.h2.command.Command.executeQuery(Command.java:195)
        at org.h2.server.TcpServerThread.process(TcpServerThread.java:308)
        at org.h2.server.TcpServerThread.run(TcpServerThread.java:149)
        at java.lang.Thread.run(Thread.java:722)
Caused by: java.lang.ArrayIndexOutOfBoundsException: 0
        at org.h2.index.PageDataLeaf.getRowAt(PageDataLeaf.java:327)
        at org.h2.index.PageDataCursor.nextRow(PageDataCursor.java:97)
        at org.h2.index.PageDataCursor.next(PageDataCursor.java:49)
        at org.h2.index.IndexCursor.next(IndexCursor.java:238)
        at org.h2.table.TableFilter.next(TableFilter.java:353)
        at org.h2.command.dml.Select.queryGroup(Select.java:311)
        at org.h2.command.dml.Select.queryWithoutCache(Select.java:613)
        at org.h2.command.dml.Query.query(Query.java:307)
        at org.h2.command.dml.Query.query(Query.java:277)
        at org.h2.command.dml.Query.query(Query.java:36)
        at org.h2.command.CommandContainer.query(CommandContainer.java:86)
        at org.h2.command.Command.executeQuery(Command.java:191)
        ... 3 more

sql> Aborted
Connection closed

$ e "select count(*) from APP.TBL_LOGS;" | xd /cc/Program\ Files\ \(x86\)/H2/bi
n/ java -cp h2*.jar org.h2.tools.Shell -url jdbc:h2:tcp://localhost/c:/H2/mgmtp
-20150327 -user app -password 000

Welcome to H2 Shell 1.3.169 (2012-09-09)
Exit with Ctrl+C
Commands are case insensitive; SQL statements end with ';'
help or ?      Display this help
list           Toggle result list / stack trace mode
maxwidth       Set maximum column width (default is 100)
autocommit     Enable or disable autocommit
history        Show the last 20 statements
quit or exit   Close the connection and exit

sql> COUNT(*)
270133
(1 row, 14 ms)
sql> Aborted
Connection closed
 ~  46613
$ e "select count(*) from APP.TBL_LOGS;" | xd /cc/Program\ Files\ \(x86\)/H2/bi
n/ java -cp h2*.jar org.h2.tools.Shell -url jdbc:h2:tcp://localhost/c:/H2/mgmtp
-20141128 -user app -password 000

Welcome to H2 Shell 1.3.169 (2012-09-09)
Exit with Ctrl+C
Commands are case insensitive; SQL statements end with ';'
help or ?      Display this help
list           Toggle result list / stack trace mode
maxwidth       Set maximum column width (default is 100)
autocommit     Enable or disable autocommit
history        Show the last 20 statements
quit or exit   Close the connection and exit

sql> COUNT(*)
66
(1 row, 14 ms)
sql> Aborted
Connection closed

Original issue reported on code.google.com by Dolland....@gmail.com on 27 Mar 2015 at 12:38

[GoogleCodeExporter]

Sorry, I had to find out while trying to drop the table, that it was corrupted:

An error occurred when executing the SQL command:
DROP TABLE IF EXISTS APP.TBL_LOGS CASCADE

File corrupted while reading record: "wrong checksum". Possible solution: use 
the recovery tool; SQL statement:
DROP TABLE IF EXISTS APP.TBL_LOGS CASCADE [90030-169] [SQL State=90030, DB 
Errorcode=90030] 

Execution time: 0.17s
(Statement 1 of 8 finished)

Script execution finished
Total script execution time: 30.036s

The question remains, how the corruption occurred.

Original comment by Dolland....@gmail.com on 27 Mar 2015 at 3:27

[GoogleCodeExporter]

Hi,

This looks like a corrupt database. To recover the data, use the tool 
org.h2.tools.Recover to create the SQL script file, and then re-create the 
database using this script.

This is a relatively old version of H2, version 1.3.169 from 2012-09-09. This 
issue is already fixed now (see the change log for details). So I set this 
issue as "works for me".

Original comment by thomas.t...@gmail.com on 29 Mar 2015 at 6:27

• Changed state: WorksForMe

[GoogleCodeExporter]

Hi Thomas,
which issue you mean is already fixed? The issue that H2 responds with an 
ArrayIndexOutOfBoundsException to a corrupt table (for this case: how it is 
reacting now?), or the issue, that the table was corrupted? Is there a general 
strategy to prevent this?

Original comment by Dolland....@gmail.com on 30 Mar 2015 at 3:01

[GoogleCodeExporter]

Hi,

which issue you mean is already fixed?

This issue is fixed in a newer version of H2.

 Is there a general strategy to prevent this?

Yes, upgrade to a more recent version of H2. But usually, you should also have 
backups of the database file.

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 10 Apr 2015 at 5:47