Database created with H2 1.3 possibly incompatible with H2 1.4 due to h2.storeLocalTime?

233 views
Skip to first unread message

Steve McLeod

unread,
Aug 3, 2015, 6:06:35 AM8/3/15
to H2 Database
It seems the problem is essentially caused by this:

In H2 1.3, h2.storeLocalTime=false
In H2 1.4, h2.storeLocalTime=true

1. Create a database with 1.3 that uses columns of type DATETIME
2. Subsequently open it with 1.4
3. You'll get sporadic exceptions when executing SQL select statements. Here's a stack trace:

org.h2.jdbc.JdbcSQLException: General error: "java.lang.ArrayIndexOutOfBoundsException: 2048"

  at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)                            
  at org.h2.message.DbException.get(DbException.java:167)                                            
  at org.h2.message.DbException.convert(DbException.java:294)                                        
  at org.h2.command.Command.executeQuery(Command.java:209)                                           
  at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:109)                  

Caused by: java.lang.ArrayIndexOutOfBoundsException: 2048               
at org.h2.store.Data.writeVarLong(Data.java:1259)                      
at org.h2.store.Data.writeValue(Data.java:526)                         
at org.h2.index.PageBtreeIndex.writeRow(PageBtreeIndex.java:394)       
at org.h2.index.PageBtreeNode.writeData(PageBtreeNode.java:454)        
at org.h2.index.PageBtreeNode.write(PageBtreeNode.java:427)            
at org.h2.store.PageStore.writeBack(PageStore.java:1047)               
at org.h2.util.CacheLRU.removeOld(CacheLRU.java:216)                   
at org.h2.util.CacheLRU.removeOldIfRequired(CacheLRU.java:142)         
at org.h2.util.CacheLRU.put(CacheLRU.java:116)                         
at org.h2.store.PageStore.getPage(PageStore.java:858)                  
at org.h2.index.PageDataIndex.getPage(PageDataIndex.java:234)          
at org.h2.index.PageDataNode.getNextPage(PageDataNode.java:233)        
at org.h2.index.PageDataLeaf.getNextPage(PageDataLeaf.java:400)        
at org.h2.index.PageDataCursor.nextRow(PageDataCursor.java:95)         
at org.h2.index.PageDataCursor.next(PageDataCursor.java:53)            
at org.h2.index.IndexCursor.next(IndexCursor.java:278)                 
at org.h2.table.TableFilter.next(TableFilter.java:361)                 
at org.h2.command.dml.Select.queryFlat(Select.java:533)                
at org.h2.command.dml.Select.queryWithoutCache(Select.java:646)        
at org.h2.command.dml.Query.query(Query.java:323)                      
at org.h2.command.dml.Query.query(Query.java:291)                      
at org.h2.index.ViewIndex.find(ViewIndex.java:283)                     
at org.h2.index.ViewIndex.find(ViewIndex.java:179)                     
at org.h2.index.BaseIndex.find(BaseIndex.java:128)                     
at org.h2.index.IndexCursor.find(IndexCursor.java:160)                 
at org.h2.table.TableFilter.next(TableFilter.java:330)                 
at org.h2.command.dml.Select.queryGroup(Select.java:327)               
at org.h2.command.dml.Select.queryWithoutCache(Select.java:641)        
at org.h2.command.dml.Query.query(Query.java:323)                      
at org.h2.command.dml.Query.query(Query.java:291)                      
at org.h2.command.dml.Query.query(Query.java:37)                       
at org.h2.command.CommandContainer.query(CommandContainer.java:91)     
at org.h2.command.Command.executeQuery(Command.java:197)               

The same problem occurs if I override  h2.storeLocalTime to be true on 1.3 and then open an existing database.

The problem is sporadic; I've been unable to create a short test case. But my findings are feasible, right?

I'd say this is a significant problem.

Steve McLeod

unread,
Aug 4, 2015, 7:02:58 AM8/4/15
to H2 Database
After some more investigation, I think this happens when the database was created with H2 1.3.x, it is opened with H2 1.4.x, a SELECT query is performed that exceeds the "maxmemoryrows" value (a default of 10,000 rows), and their is a TIMESTAMP column in the result.

It seems that the ResultDiskBuffer created to buffer the org.h2.result.ResultSet is getting a wrong result for row length from org.h2.store.Data.getValueLen(Value v, DataHandler handler) when v.getType() is of Value.TIMESTAMP. So a call to ByteArrayOutputStream.checkCapacity is given wrong input.

That's what I think, but as the code is foreign to me, I could be very wrong in my analysis.

Thomas Mueller

unread,
Aug 4, 2015, 12:01:59 PM8/4/15
to h2-da...@googlegroups.com
Hi,

Maybe you can reproduce the problem more easily if you set the system property "h2.check2" to "true".

I couldn't reproduce the problem so far, and the code looks correct to me (I debugged it and added some trace output, both for storeLocalTime true and false). Maybe it's a bug in an old version, or some strange combination.

Could you post the exact stack trace including error message and error code?

Which version of H2 do you use exactly (1.3.x and 1.4.x), and what is the "create_build" in the database file (select * from information_schema.settings)? How do you set the system property (in which version of H2, how, and when)? It would need to be set before loading the H2 driver.

Regards,
Thomas
--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Steve McLeod

unread,
Aug 4, 2015, 12:48:06 PM8/4/15
to H2 Database
Hi Thomas,

The database is created with 1.3.176. The error happens in 1.4.188. 

I don't normally set h2.storeLocalTime - I just observed that it has a different default in 1.4 to 1.3, and at one point my investigations led me to code dependent on the value. To investigate further, I tried reloading the database created in 1.3.176, this time running System.setProperty("h2.storeLocalTime", "true") immediately before loading the H2 JDBC driver (which I do using Class.forName("org.h2.Driver")), and was able to sporadically reproduce the problem.

Here's a stack trace (right now it is happening with a simple INSERT statement)

 sql = INSERT INTO hand (gameid, casinoid, gamenumber, starttime) VALUES (?, ?, ?, ?)
 org.h2.jdbc.JdbcSQLException: General error: "java.lang.ArrayIndexOutOfBoundsException"; SQL statement:
 INSERT INTO hand (gameid, casinoid, gamenumber, starttime) VALUES (?, ?, ?, ?) [50000-188]
  at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
  at org.h2.message.DbException.get(DbException.java:168)
  at org.h2.message.DbException.convert(DbException.java:295)
  at org.h2.table.RegularTable.addRow(RegularTable.java:137)
  at org.h2.command.dml.Insert.insertRows(Insert.java:156)
  at org.h2.command.dml.Insert.update(Insert.java:114)
  at org.h2.command.CommandContainer.update(CommandContainer.java:78)
  at org.h2.command.Command.executeUpdate(Command.java:254)
  at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:157)
  at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:143)
  at // non-h2 removed

Caused by: java.lang.ArrayIndexOutOfBoundsException

I'll keep investigating...

I can also supply a database that has the problem, but it is 768 MB after zipping...so I think I'll try to write some repro code instead.
Hi,

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

Steve McLeod

unread,
Aug 5, 2015, 5:18:36 AM8/5/15
to H2 Database
My latest findings:

I can reproduce (again, sporadically) the problem by exclusively using h2 1.4.188 as follows:
1) set System.setProperty("h2.storeLocalTime", "false"), start the app, import some data; then
2) restart the app with System.setProperty("h2.storeLocalTime", "true") and import some more data.

Once the error occurs, the database is in a corrupted state.

I've managed to create and save a corrupted database, which is zipped to only 5 MB. The zip file includes a trace.db file. Thomas, let me know if it is useful for you to see this corrupted database.

So far I've been doing this with MV_STORE=false. 

I'll continue posting more info as I narrow down the problem.

Steve McLeod

unread,
Aug 5, 2015, 7:08:37 AM8/5/15
to H2 Database
More info:

I've been unable to reproduce this using the MVStore.

I can make this problem happen when I only use h2 1.3.176 and I can make it happen when I only use h2 1.4.188.

I initially create the database and load some data with h2.storeLocalTime=false, then restart the app with h2.storeLocalTime=true and load some more data. Usually database corruption ensues.

 I've stripped almost every config option from the database url so that it just reads:

jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware.pokercopilot/database/pokercopilot;MV_STORE=false

I believe this confirms it is not a new issue, but it has become apparent now because the default value of h2.storeLocalTime changes from 1.3 to 1.4, and I'm trying to move our app from 1.3 to 1.4.

Here's the most recent stack trace:

org.h2.jdbc.JdbcSQLException: General error: "java.lang.ArrayIndexOutOfBoundsException: 2048" [50000-176]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.message.DbException.convert(DbException.java:294)
at org.h2.table.RegularTable.addRow(RegularTable.java:138)
at org.h2.engine.UndoLogRecord.undo(UndoLogRecord.java:111)
at org.h2.engine.Session.rollbackTo(Session.java:595)
at org.h2.command.Command.executeUpdate(Command.java:278)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:158)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:144)
at com.barbarysoftware.databaseframework.JdbcTemplateCore.update(JdbcTemplateCore.java:311)
... 15 more
Caused by: java.lang.ArrayIndexOutOfBoundsException: 2048
at org.h2.store.Data.writeByte(Data.java:379)
at org.h2.store.Data.writeValue(Data.java:441)
at org.h2.index.PageBtreeIndex.writeRow(PageBtreeIndex.java:394)
at org.h2.index.PageBtreeNode.writeData(PageBtreeNode.java:454)
at org.h2.index.PageBtreeNode.write(PageBtreeNode.java:427)
at org.h2.store.PageStore.writeBack(PageStore.java:1047)
at org.h2.util.CacheLRU.removeOld(CacheLRU.java:216)
at org.h2.util.CacheLRU.removeOldIfRequired(CacheLRU.java:142)
at org.h2.util.CacheLRU.put(CacheLRU.java:116)
at org.h2.util.CacheLRU.update(CacheLRU.java:123)
at org.h2.store.PageStore.update(PageStore.java:1097)
at org.h2.index.PageDataLeaf.addRowTry(PageDataLeaf.java:209)
at org.h2.index.PageDataLeaf.split(PageDataLeaf.java:377)
at org.h2.index.PageDataNode.addRowTry(PageDataNode.java:139)
at org.h2.index.PageDataIndex.addTry(PageDataIndex.java:174)
at org.h2.index.PageDataIndex.add(PageDataIndex.java:137)
at org.h2.table.RegularTable.addRow(RegularTable.java:120)
... 21 more

Steve McLeod

unread,
Aug 5, 2015, 7:59:01 AM8/5/15
to H2 Database
Here's a stack trace in the same scenario from h2 1.4.188 

org.h2.jdbc.JdbcSQLException: General error: "java.lang.ArrayIndexOutOfBoundsException: 4096"; SQL statement:
ROLLBACK [50000-188]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:168)
at org.h2.message.DbException.convert(DbException.java:295)
at org.h2.command.Command.executeUpdate(Command.java:262)
at org.h2.jdbc.JdbcConnection.rollbackInternal(JdbcConnection.java:1500)
at org.h2.jdbc.JdbcConnection.rollback(JdbcConnection.java:488)
at org.h2.jdbcx.JdbcXAConnection$PooledJdbcConnection.close(JdbcXAConnection.java:465)
at com.barbarysoftware.databaseframework.JdbcTemplateCore.update(JdbcTemplateCore.java:278)
...
Caused by: java.lang.ArrayIndexOutOfBoundsException: 4096
at org.h2.store.Data.writeVarLong(Data.java:1254)
at org.h2.store.Data.writeValue(Data.java:523)
at org.h2.index.PageBtreeIndex.writeRow(PageBtreeIndex.java:393)
at org.h2.index.PageBtreeNode.writeData(PageBtreeNode.java:453)
at org.h2.index.PageBtreeNode.write(PageBtreeNode.java:426)
at org.h2.store.PageStore.writeBack(PageStore.java:1046)
at org.h2.store.PageStore.writeBack(PageStore.java:418)
at org.h2.store.PageStore.checkpoint(PageStore.java:436)
at org.h2.store.PageStore.commit(PageStore.java:1480)
at org.h2.engine.Database.commit(Database.java:1995)
at org.h2.engine.Session.rollback(Session.java:614)
at org.h2.command.dml.TransactionCommand.update(TransactionCommand.java:49)
at org.h2.command.CommandContainer.update(CommandContainer.java:78)
at org.h2.command.Command.executeUpdate(Command.java:254)
... 17 more

Thomas Mueller

unread,
Aug 5, 2015, 12:21:54 PM8/5/15
to h2-da...@googlegroups.com
Hi,

I can reproduce the problem now, and I think I know what the problem is. I hope to have a fix ready in a few days.

Regards,
Thomas

Hi,

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

Thomas Mueller

unread,
Aug 5, 2015, 12:22:15 PM8/5/15
to h2-da...@googlegroups.com
Hi,

Steve, thanks a lot for your help!

Regards,
Thomas

Steve McLeod

unread,
Aug 6, 2015, 5:05:53 AM8/6/15
to H2 Database

Steve, thanks a lot for your help!

My pleasure! This is probably the remaining impediment stopping me from moving Poker Copilot to H2 1.4, so I had a good reason for finding the problem.



 
Hi,

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

Thomas Mueller

unread,
Aug 6, 2015, 12:06:36 PM8/6/15
to h2-da...@googlegroups.com
Hi,

I will remove the system property "h2.storeLocalTime". As a workaround for existing H2 version 1.4.x, you can set the system property "h2.storeLocalTime" to "false", if you want to write to old (PageStore) databases.

So an ArrayIndexOutOfBoundsException is thrown in some cases when opening an old version 1.3 databases, or a 1.4 database with the settings "mv_store=false" and the system property "h2.storeLocalTime" set to false. It mainly shows up if there is an index on a time, date, or timestamp column. The system property "h2.storeLocalTime" will no longer be supported (MVStore databases always store local time, and PageStore databases never do, in the next version).

The root cause reason is that adding a row to a page only allocated space for the new row, but didn't take into account that existing rows now can use more space, due to the changed format.

Regards,
Thomas


Hi,

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages