H2 File corruption in automated tests

[dnloc]

I am newly working on a mature-ish project, and in my attempts to upgrade from 1.1.x to 1.3.155 I get the errors below.

I can upgrade and my automated tests run happily and repeatedly, but if one ever fails and the build exits, the next build will fail with the trace below. It looks like the files get corrupted (changing the directory it uses fixes it, but this isn't a proper solution). Is the db not being closed properly?

Caused by: org.h2.jdbc.JdbcSQLException: General error: "java.lang.NullPointerException" [50000-155]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
    at org.h2.message.DbException.get(DbException.java:156)
    at org.h2.message.DbException.convert(DbException.java:279)
    at org.h2.table.RegularTable.addRow(RegularTable.java:145)
    at org.h2.store.PageStore.redo(PageStore.java:1470)
    at org.h2.store.PageLog.recover(PageLog.java:316)
    at org.h2.store.PageStore.recover(PageStore.java:1324)
    at org.h2.store.PageStore.openExisting(PageStore.java:349)
    at org.h2.store.PageStore.open(PageStore.java:273)
    at org.h2.engine.Database.getPageStore(Database.java:2039)
    at org.h2.engine.Database.open(Database.java:547)
    at org.h2.engine.Database.openDatabase(Database.java:218)
    at org.h2.engine.Database.<init>(Database.java:213)
    at org.h2.engine.Engine.openSession(Engine.java:56)
    at org.h2.engine.Engine.openSession(Engine.java:159)
    at org.h2.engine.Engine.createSessionAndValidate(Engine.java:138)
    at org.h2.engine.Engine.createSession(Engine.java:121)
    at org.h2.engine.Engine.createSession(Engine.java:28)
    at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:285)
    at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:112)
    at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:96)
    at org.h2.Driver.connect(Driver.java:72)
    at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
    at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
    at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
    at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
    ... 54 more
Caused by: java.lang.NullPointerException
    at org.h2.engine.Database.getLobConnection(Database.java:2251)
    at org.h2.store.LobStorage.init(LobStorage.java:88)
    at org.h2.store.LobStorage.getInputStream(LobStorage.java:360)
    at org.h2.value.ValueLobDb.getInputStream(ValueLobDb.java:280)
    at org.h2.value.ValueLobDb.getReader(ValueLobDb.java:266)
    at org.h2.value.ValueLobDb.getString(ValueLobDb.java:187)
    at org.h2.value.ValueLobDb.compareSecure(ValueLobDb.java:252)
    at org.h2.value.Value.compareTypeSave(Value.java:868)
    at org.h2.table.Table.compareTypeSave(Table.java:1027)
    at org.h2.index.BaseIndex.compareValues(BaseIndex.java:241)
    at org.h2.index.BaseIndex.compareRows(BaseIndex.java:173)
    at org.h2.index.PageBtree.find(PageBtree.java:117)
    at org.h2.index.PageBtreeLeaf.addRow(PageBtreeLeaf.java:146)
    at org.h2.index.PageBtreeLeaf.addRowTry(PageBtreeLeaf.java:100)
    at org.h2.index.PageBtreeIndex.addRow(PageBtreeIndex.java:93)
    at org.h2.index.PageBtreeIndex.add(PageBtreeIndex.java:84)
    at org.h2.table.RegularTable.addRow(RegularTable.java:127)
    ... 76 more

Any more info required?

[Thomas Mueller]

Hi,

It looks like you are using an index on a CLOB column. Most databases
don't allow you to add an index on a CLOB or BLOB, but H2 allows it.
However, there is a problem when opening the database when trying to
re-apply committed transactions, because the CLOB/BLOB mechanism now
requires an open database.... So the index can't be updated.

I'm not sure what the best solution is. Maybe H2 shouldn't allow you
to create an index on a BLOB or CLOB column, like most other
databases.

Regards,
Thomas

[Rami Ojares]

On 29.6.2011 22:39, Thomas Mueller wrote:
> I'm not sure what the best solution is. Maybe H2 shouldn't allow you
> to create an index on a BLOB or CLOB column, like most other
> databases.

That is definately the way to go until someone provides a solution that
actually works.

I would assume the no one wants to index BLOBs and for CLOBs you most
likely want to use a fulltext index.
I am assuming that a regular index or fulltext index does not speed up
regex or like queries?

Currently the fulltext index is created for the whole database and there
can be only one index / database.
Maybe in the future the fulltext index could be made as just one of the
possible index types for character based columns?
And then the idea of having indexes on CLOBs could be revisited.

- rami

[Thomas Mueller]

Hi,

The next version of H2 will not allow to create indexes on BLOB and
CLOB columns.

> I am assuming that a regular index or fulltext index does not speed up regex
> or like queries?

Currently not. A regular index can in theory speed up queries of the
form "column like 'xxx%'", but in practice such indexes are still bad
because they load a lot of data (log(n) rows, but each row can be very
big).

> Currently the fulltext index is created for the whole database and there can
> be only one index / database.

You can define what tables / columns to index:
http://h2database.com/javadoc/org/h2/fulltext/FullText.html#createIndex_Connection_String_String_String

But it's true that there is only one fulltext index per database.

> Maybe in the future the fulltext index could be made as just one of the
> possible index types for character based columns?

Yes, that would be an option.

Regards,
Thomas