Re: java.lang.RuntimeException: rowcount remaining=1 SYS when trying to connect to an H2 database.

[Thomas Mueller]

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. Does it work when you do this?

With version 1.3.162 and older: on out of disk space, the database can get corrupt sometimes, if later write operations succeed. The same problem happens on other kinds of I/O exceptions (where one or some of the writes fail, but subsequent writes succeed). Now the file is closed on the first unsuccessful write operation, so that later requests fail consistently.

Important corruption problems were fixed in version 1.2.135 and version 1.2.140 (see the change log). Known causes for corrupt databases are: if the database was created or used with a version older than 1.2.135, and the process was killed while the database was closing or writing a checkpoint. Using the transaction isolation level READ_UNCOMMITTED (LOCK_MODE 0) while at the same time using multiple connections. Disabling database file protection using (setting FILE_LOCK to NO in the database URL). Some other areas that are not fully tested are: Platforms other than Windows XP, Linux, Mac OS X, or JVMs other than Sun 1.5 or 1.6; the feature MULTI_THREADED; the features AUTO_SERVER and AUTO_RECONNECT; the file locking method 'Serialized'.

So I suggest to upgrade to a more recent version of H2.

Regards,
Thomas



On Fri, Feb 22, 2013 at 2:12 PM, Andrei Dan <xdan...@gmail.com> wrote:
>
> Hello,
>
> I am using H2 1.3.150 and I'm trying to connect to it whenever I start my application ( running on java 1.6.0_37 ), but after a restart of my application I wasn't able to connect to it anymore ( neither from my app and neither form the H2 Console ).
>
> The exception I get is :
>
> Caused by: org.h2.jdbc.JdbcSQLException: General error: "java.lang.RuntimeException: rowcount remaining=1 SYS" [50000-150]
>
>                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.engine.Database.openDatabase(Database.java:248)
>
>                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:289)
>
>                at org.h2.engine.SessionRemote.createSession(SessionRemote.java:265)
>
>                at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:110)
>
>                at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:94)
>
>                at org.h2.Driver.connect(Driver.java:62)
>
>                at java.sql.DriverManager.getConnection(Unknown Source)
>
>                at java.sql.DriverManager.getConnection(Unknown Source)
>
>                at org.h2.tools.RunScript.process(RunScript.java:305)
>
>                at org.h2.tools.RunScript.execute(RunScript.java:289)
>
>                ... 72 more
>
> Caused by: java.lang.RuntimeException: rowcount remaining=1 SYS
>
>                at org.h2.message.DbException.throwInternalError(DbException.java:226)
>
>                at org.h2.table.RegularTable.addIndex(RegularTable.java:260)
>
>                at org.h2.engine.Database.open(Database.java:585)
>
>                at org.h2.engine.Database.openDatabase(Database.java:218)
>
>                ... 88 more
>
>
> Also, here's the exception from my h2 trace file ( which looks a bit different ):
>
> org.h2.jdbc.JdbcSQLException: General error: "java.lang.RuntimeException: rowCount expected 28 got 31 LOB_MAP.LOB_MAP_DATA"; SQL statement:
>
> INSERT INTO INFORMATION_SCHEMA.LOB_MAP(LOB, SEQ, HASH, BLOCK) VALUES(?, ?, ?, ?) [50000-150]
>
> 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:143)
>
> at org.h2.command.dml.Insert.insertRows(Insert.java:126)
>
> at org.h2.command.dml.Insert.update(Insert.java:86)
>
> at org.h2.command.CommandContainer.update(CommandContainer.java:69)
>
> at org.h2.command.Command.executeUpdate(Command.java:209)
>
> at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:181)
>
> at org.h2.store.LobStorage.storeBlock(LobStorage.java:488)
>
> at org.h2.store.LobStorage.addLob(LobStorage.java:363)
>
> at org.h2.store.LobStorage.createBlob(LobStorage.java:578)
>
> at org.h2.value.ValueLobDb.link(ValueLobDb.java:155)
>
> at org.h2.index.PageDataIndex.add(PageDataIndex.java:106)
>
> at org.h2.table.RegularTable.addRow(RegularTable.java:125)
>
> at org.h2.command.dml.Insert.insertRows(Insert.java:126)
>
> at org.h2.command.dml.Insert.update(Insert.java:86)
>
> at org.h2.command.CommandContainer.update(CommandContainer.java:69)
>
> at org.h2.command.Command.executeUpdate(Command.java:209)
>
> at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:143)
>
> at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:129)
>
>         ...
>
> Caused by: java.lang.RuntimeException: rowCount expected 28 got 31 LOB_MAP.LOB_MAP_DATA
>
> at org.h2.message.DbException.throwInternalError(DbException.java:226)
>
> at org.h2.table.RegularTable.checkRowCount(RegularTable.java:173)
>
> at org.h2.table.RegularTable.addRow(RegularTable.java:126)
>
> ... 31 more
>
>
> The URL I use to connect to my H2 db is :
>
> jdbc:h2:/Users/andrei/dev/src/Configuration/Data/h2/persistentSore/appStore;DB_CLOSE_ON_EXIT=TRUE;AUTO_SERVER=TRUE;IFEXISTS=TRUE
>
>
> The ddl of my h2 is :
>
> create table DataModelVersion
>
> (
>
> DatamodelVersionId int not null AUTO_INCREMENT,
>
> Version nvarchar2(2000) not null,
>
> LastModifiedTimestamp timestamp(3) not null,
>
> PRIMARY KEY (DatamodelVersionId)
>
> );
>
>
> CREATE TABLE MessageStore
>
> (
>
>    MessageStoreId INT not null AUTO_INCREMENT,
>
>    PersistentMessage BLOB not null,
>
>    IsRecordValid bit not null,
>
>    SentTimestamp timestamp(3) not null,
>
>    LastModifiedTimestamp timestamp(3) not null,
>
>    PRIMARY KEY (MessageStoreId)
>
> );
>
>
> I noticed there is a similar discussion on :
> https://groups.google.com/forum/?fromgroups=#!topic/h2-database/YLqKymG5J-4
> but adding "RECOVER=1" didn't solve anything for me.
>
> Does anyone has any idea on how did my DB get into this state and if there is anything I can do to prevent it from getting in this state ? ( not to mention being able to recover the data I have in my corrupt db )
>
> Thanks,
>
> Andrei.
>
> --
> 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?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>  
>  

[Andrei Dan]

Hello, 

No, using the recovery tool didn't work since it was not able to connect to the db.

I'll try to update to the latest version.

Thanks, 

Andrei.

[Thomas Mueller]

Hi,

The recovery tool doesn't connect to the database, it opens the database files directly. The tool is started with 

    java org.h2.tools.Recover

See also the documentation and the Javadocs.

Regards,

Thomas

> at org.h2.command.dml.Insert.upda> 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?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>  
>