NPE in select count(*) from table

[Wagner Correa Ramos]

Hi,

First of all thnks for hsqlDB team, the hsqlDB is a powerfull and compact database engine, very fast and very simple to use. Congratulations !

Well, I am a happy hsqldb user (1.8.0.5), but I´m having some problems: Last week I lost an user database table because of a NPE error, but I´m having too memory exceptions errors, so I had believe that the out of memory error was the cause of the crash.
I resolve the out-of-memory problem and the app was running for a week - but today I had another problem of NPE and the only solution was drop and recreate the table.
I tried shutdown compact, etc, but nothing could resolve the problem.
I am installing today the 1.8.0.7 release and hope that the problem was fixed.
The error that I having today occurs when I execute one simple query:
select count(*) from up001;
I receive a msg of general error and in the log I have:

java.lang.NullPointerException
        at org.hsqldb.persist.DataFileCache.get(Unknown Source)
        at org.hsqldb.Table$RowStore.get(Unknown Source)
        at org.hsqldb.Table.getRow(Unknown Source)
        at org.hsqldb.DiskNode.findNode(Unknown Source)
        at org.hsqldb.DiskNode.getLeft(Unknown Source)
        at org.hsqldb.Index.firstRow(Unknown Source)
        at org.hsqldb.TableFilter.findFirst(Unknown Source)
        at org.hsqldb.Select.buildResult(Unknown Source)
        at org.hsqldb.Select.getSingleResult(Unknown Source)
        at org.hsqldb.Select.getResult(Unknown Source)
        at org.hsqldb.CompiledStatementExecutor.executeSelectStatement(Unknown Source)
        at org.hsqldb.CompiledStatementExecutor.executeImpl(Unknown Source)
        at org.hsqldb.CompiledStatementExecutor.execute(Unknown Source)
        at org.hsqldb.Session.sqlExecuteCompiledNoPreChecks(Unknown Source)
        at org.hsqldb.DatabaseCommandInterpreter.executePart(Unknown Source)
        at org.hsqldb.DatabaseCommandInterpreter.execute(Unknown Source)
        at org.hsqldb.Session.sqlExecuteDirectNoPreChecks(Unknown Source)
        at org.hsqldb.Session.execute(Unknown Source)
        at org.hsqldb.ServerConnection.run(Unknown Source)
        at java.lang.Thread.run(Thread.java:595)

The table definition is:
CREATE CACHED TABLE UP001
(LOO_TRANSACTION_ID NUMERIC(12) NOT NULL PRIMARY KEY,
LOO_OPERATION CHAR(1),
LOO_TIMESTAMP TIMESTAMP,
LOO_TABLE VARCHAR(100),
LOO_KEY VARCHAR(1000),
LOO_COLUMNS VARCHAR(4000),
LOO_EXCEPTION_CODE NUMERIC(10),
LOO_EXCEPTION_MSG VARCHAR(1000),
LOO_LOGCODE NUMERIC(10),
LOO_STATUS CHAR(1),
LOO_RETRYCOUNT NUMERIC(10),
LOO_CYCLE NUMERIC(10));

Is there some workaround to recover table data when this type of error occurs ?

[Fred Toussi]

There is no way out of this. You can probably recover data from some of the tables by querying and exporting.

In 1.8.0.7 more bugs relating to pesistenc have been fixed. You should switch to this version.