Recovery of H2Database corruption

[María Arias de Reyna]

Hi,

I have a Java application that uses a H2Database with Hibernate. The
problem is, sometimes this database gets corrupted. Probably the
problem is mine, because sometimes there are power failures so the
application is not properly closed. It cannot be avoided.

But after accepting that the database will be sometimes corrupted, is
there any way to recover it as much as posible?

I have read that the recovery tool will be helpful, but on
http://www.h2database.com/html/advanced.html it says that "The recover
tool can be used to extract the contents of a data file[...]". As data
file it means the whole database? If I run the recovery tool and then
do a runscript, the database will be recovered? Should I delete the
lock file first?

In case the type of corruption is relevant, the exception trace is
this:

org.hibernate.exception.GenericJDBCException: Cannot open connection
at
org.hibernate.exception.SQLStateConverter.handledNonSpecificException
(SQLStateConverter.java:126)
at org.hibernate.exception.SQLStateConverter.convert
(SQLStateConverter.java:114)
[...]
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: org.h2.jdbc.JdbcSQLException: General error:
java.lang.RuntimeException: rowcount remaining=-49 SYS [50000-100]
at org.h2.message.Message.getSQLException(Message.java:103)
at org.h2.message.Message.convert(Message.java:257)
at org.h2.engine.Database.<init>(Database.java:231)
at org.h2.engine.Engine.openSession(Engine.java:57)
at org.h2.engine.Engine.openSession(Engine.java:126)
at org.h2.engine.Engine.getSession(Engine.java:109)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:111)
at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.RuntimeException: rowcount remaining=-49 SYS
at org.h2.message.Message.getInternalError(Message.java:179)
at org.h2.table.TableData.addIndex(TableData.java:202)
at org.h2.engine.Database.open(Database.java:582)
at org.h2.engine.Database.<init>(Database.java:212)
... 5 more

at org.h2.engine.SessionRemote.done(SessionRemote.java:493)
at org.h2.engine.SessionRemote.initTransfer(SessionRemote.java:
111)
at org.h2.engine.SessionRemote.connectServer
(SessionRemote.java:316)
at org.h2.engine.SessionRemote.connectEmbeddedOrServer
(SessionRemote.java:227)
at org.h2.engine.SessionRemote.createSession
(SessionRemote.java:221)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:103)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:87)
at org.h2.Driver.connect(Driver.java:57)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at
org.hibernate.connection.DriverManagerConnectionProvider.getConnection
(DriverManagerConnectionProvider.java:133)
at org.hibernate.jdbc.ConnectionManager.openConnection
(ConnectionManager.java:446)
... 24 more



Thanks :)

[María Arias de Reyna]

> If I run the recovery tool and then
> do a runscript, the database will be recovered?

Anyway, I tried this and the recovery tool doesn't work well. It
extracts the data on a wrong order, making the runscript fail because
it tries to add data before the foreign keys are fully inserted on
their referenced tables.

Table A has a foreign key referencing Table B
The RunScript tries to add the data on table A before adding the data
on table B, so it fails (the foreign key doesn't have a proper
reference).

[María Arias de Reyna]

It also fails trying to add the "sa" user, which I also think is a
bug.

Exception in thread "main" org.h2.jdbc.JdbcSQLException: User SA
already exists [90033-100]
at org.h2.message.Message.getSQLException(Message.java:103)
at org.h2.message.Message.getSQLException(Message.java:114)
at org.h2.message.Message.getSQLException(Message.java:77)
at org.h2.command.ddl.CreateUser.update(CreateUser.java:69)
at org.h2.command.CommandContainer.update
(CommandContainer.java:69)
at org.h2.command.Command.executeUpdate(Command.java:206)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:
283)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:127)
at java.lang.Thread.run(Thread.java:595)

at org.h2.engine.SessionRemote.done(SessionRemote.java:493)
at org.h2.command.CommandRemote.executeUpdate
(CommandRemote.java:192)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:163)
at org.h2.tools.RunScript.process(RunScript.java:227)
at org.h2.tools.RunScript.process(RunScript.java:198)
at org.h2.tools.RunScript.process(RunScript.java:324)
at org.h2.tools.RunScript.run(RunScript.java:151)
at org.h2.tools.RunScript.main(RunScript.java:94)

[Thomas Mueller]

Hi,

I suggest to upgrade to a newer version of H2. In version 1.1.108, a
bug was fixed that could cause data corruption (see also
http://www.h2database.com/html/changelog.html "When the shutdown hook
closed the database, the last log file was deleted too early. This
could cause uncommitted changes to be persisted. In some cases, this
could cause data corruption.").

You are right, the recovery tool create the foreign keys before
inserting data. This doesn't work in some cases. I will fix this
problem in the next release.

> It also fails trying to add the "sa" user, which I also think is a bug.

This is documented at
http://www.h2database.com/html/advanced.html#using_recover_tool : "If
you run the script against a database that was created with the same
user, or if there are conflicting users, running the script will fail.
Consider running the script against a database that was created with a
user name that is not in the script."

Regards,
Thomas

2009/5/25 María Arias de Reyna <del...@gmail.com>:

[Neunerball]

Hi,

In addition, I'd try to use a Smart-UPS (battery backup), which can
shutdown your machine properly after a certain amount of time. Thus,
avoiding
the corruptions, due to a power failure.


On May 25, 2:59 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> I suggest to upgrade to a newer version of H2. In version 1.1.108, a

> bug was fixed that could cause data corruption (see alsohttp://www.h2database.com/html/changelog.html"When the shutdown hook

> closed the database, the last log file was deleted too early. This
> could cause uncommitted changes to be persisted. In some cases, this
> could cause data corruption.").
>
> You are right, the recovery tool create the foreign keys before
> inserting data. This doesn't work in some cases. I will fix this
> problem in the next release.
>
> > It also fails trying to add the "sa" user, which I also think is a bug.
>

> This is documented athttp://www.h2database.com/html/advanced.html#using_recover_tool: "If

> you run the script against a database that was created with the same
> user, or if there are conflicting users, running the script will fail.
> Consider running the script against a database that was created with a
> user name that is not in the script."
>
> Regards,
> Thomas
>

> 2009/5/25 María Arias de Reyna <dela...@gmail.com>:

>
>
>
>
>
> >> If I run the recovery tool and then
> >> do a runscript, the database will be recovered?
>
> > Anyway, I tried this and the recovery tool doesn't work well. It
> > extracts the data on a wrong order, making the runscript fail because
> > it tries to add data before the foreign keys are fully inserted on
> > their referenced tables.
>
> > Table A  has a foreign key referencing Table B
> > The RunScript tries to add the data on table A before adding the data
> > on table B, so it fails (the foreign key doesn't have a proper

> > reference).- Hide quoted text -
>
> - Show quoted text -

[ally...@gmail.com]

Long shot that this will get a reply, but I'm having an issue recovering a corrupted database. I'm running into the created by SA problem where CREATE USER SA fails on running the sql with Runscript. I see the explanation, but I don't understand what "Consider running the script against a database that was created with a user name that is not in the script." actually means. Do I create a new database with the same name as the one I tried to recover manually with a different user and then try to run the script against it?

Thanks,
Alex

[Noel Grandin]

Either edit the script to remove the part that creates the SA user, or
create a database using a username that is not in the script.

> --
> 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.