SQL query on H2 database table throws ArrayIndexOutOfBoundsException

Question

I have a H2 database on which some queries work, while others are throwing an ArrayIndexOutOfBoundsException.

For example:

SELECT COLUMN_1 FROM MY_TABLE; // works fine


SELECT COUNT(COLUMN_1) FROM MY_TABLE; // gives following error message:
[Error Code: 50000, SQL State: HY000]  
General error: "java.lang.ArrayIndexOutOfBoundsException"; 
SQL statement: SELECT COUNT(COLUMN_1) FROM MY_TABLE [50000-167]

What is the cause for this eror message?

Answer 1

The reason for the error message was a corrupt database.

I resolved the problem by using the H2 Recovery Tool. The steps were the following:

1. Create the recovery script

   C:\PATH_TO_CORRUPT_DB>java -cp C:\PATH_TO_MY_H2\h2.jar org.h2.tools.Recover
   

2. Delete the old db file (not without making a backup copy first, of course ;-))

3. Recreate the database

   C:\PATH_TO_CORRUPT_DB>java -cp C:\PATH_TO_MY_H2\h2.jar org.h2.tools.RunScript -url jdbc:h2:PATH_TO_CORRUPT_DB\NAME_OF_DB -script NAME_OF_SCRIPT_FROM_STEP_ONE.sql
   

Here you can find a more detailed usage description of the H2 Recovery Tool