Recovering corrupted database

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Recovering corrupted database

Sergio Bossa
Hi,

after the HSQL database crashed due to out of space errors on disk (and after properly cleaning up space), I'm now getting the following exception on several queries:

FATAL hsqldb.db.HSQLDB362C1245B8.ENGINE - nimrod-data/db.data getFromFile 49719841
org.hsqldb.HsqlException: java.io.EOFException
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.persist.RowStoreAVLDisk.get(Unknown Source)
at org.hsqldb.persist.DataFileCache.getFromFile(Unknown Source)
at org.hsqldb.persist.DataFileCache.get(Unknown Source)
at org.hsqldb.persist.RowStoreAVLDisk.get(Unknown Source)
at org.hsqldb.index.NodeAVLDisk.findNode(Unknown Source)
at org.hsqldb.index.NodeAVLDisk.getLeft(Unknown Source)
at org.hsqldb.index.IndexAVL.next(Unknown Source)
at org.hsqldb.index.IndexAVL.next(Unknown Source)
at org.hsqldb.index.IndexAVL$IndexRowIterator.getNextRow(Unknown Source)
at org.hsqldb.RangeVariable$RangeIteratorMain.findNext(Unknown Source)
at org.hsqldb.RangeVariable$RangeIteratorMain.next(Unknown Source)
at org.hsqldb.QuerySpecification.buildResult(Unknown Source)
at org.hsqldb.QuerySpecification.getSingleResult(Unknown Source)
at org.hsqldb.QuerySpecification.getResult(Unknown Source)
at org.hsqldb.StatementQuery.getResult(Unknown Source)
at org.hsqldb.StatementDMQL.execute(Unknown Source)
at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.executeQuery(Unknown Source)
...
Caused by: java.io.EOFException
at org.hsqldb.lib.HsqlByteArrayInputStream.readInt(Unknown Source)
at org.hsqldb.index.NodeAVLDisk.<init>(Unknown Source)
at org.hsqldb.RowAVLDisk.<init>(Unknown Source)

How can I recover the database from what it looks like data corruption?

Thanks

Sergio B.

--
Sergio Bossa
http://www.linkedin.com/in/sergiob

------------------------------------------------------------------------------
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here
http://p.sf.net/sfu/sfd2d-msazure
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Recovering corrupted database

Fred Toussi-2
Clearly, most of the data is still in the data file, but we haven't got a tool to recover the data automatically.
 
We added a database backup facility to help in this kind of situation. We will also add further safeguards against disk space issues in future versions.
 
Fred
 
On Thu, Mar 22, 2012, at 14:59, Sergio Bossa wrote:
Hi,
 
after the HSQL database crashed due to out of space errors on disk (and after properly cleaning up space), I'm now getting the following exception on several queries:
 
FATAL hsqldb.db.HSQLDB362C1245B8.ENGINE - nimrod-data/db.data getFromFile 49719841
org.hsqldb.HsqlException: java.io.EOFException
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.persist.RowStoreAVLDisk.get(Unknown Source)
at org.hsqldb.persist.DataFileCache.getFromFile(Unknown Source)
at org.hsqldb.persist.DataFileCache.get(Unknown Source)
at org.hsqldb.persist.RowStoreAVLDisk.get(Unknown Source)
at org.hsqldb.index.NodeAVLDisk.findNode(Unknown Source)
at org.hsqldb.index.NodeAVLDisk.getLeft(Unknown Source)
at org.hsqldb.index.IndexAVL.next(Unknown Source)
at org.hsqldb.index.IndexAVL.next(Unknown Source)
at org.hsqldb.index.IndexAVL$IndexRowIterator.getNextRow(Unknown Source)
at org.hsqldb.RangeVariable$RangeIteratorMain.findNext(Unknown Source)
at org.hsqldb.RangeVariable$RangeIteratorMain.next(Unknown Source)
at org.hsqldb.QuerySpecification.buildResult(Unknown Source)
at org.hsqldb.QuerySpecification.getSingleResult(Unknown Source)
at org.hsqldb.QuerySpecification.getResult(Unknown Source)
at org.hsqldb.StatementQuery.getResult(Unknown Source)
at org.hsqldb.StatementDMQL.execute(Unknown Source)
at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.executeQuery(Unknown Source)
...
Caused by: java.io.EOFException
at org.hsqldb.lib.HsqlByteArrayInputStream.readInt(Unknown Source)
at org.hsqldb.index.NodeAVLDisk.<init>(Unknown Source)
at org.hsqldb.RowAVLDisk.<init>(Unknown Source)
 
How can I recover the database from what it looks like data corruption?
 
Thanks
 
Sergio B.
 
--
Sergio Bossa
http://www.linkedin.com/in/sergiob
------------------------------------------------------------------------------
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here
_______________________________________________
Hsqldb-user mailing list
 

------------------------------------------------------------------------------
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here
http://p.sf.net/sfu/sfd2d-msazure
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Recovering corrupted database

Sergio Bossa
On Thu, Mar 22, 2012 at 9:43 PM, Fred Toussi <[hidden email]> wrote:
 
We added a database backup facility to help in this kind of situation. 

How does the mentioned backup facility work?

Btw, talking about backups, it should be safe to take a backup during normal operations (so while the database is running) by simply copying the data directory over some other place, right?

Thanks,

Sergio B.

--
Sergio Bossa
http://www.linkedin.com/in/sergiob

------------------------------------------------------------------------------
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here
http://p.sf.net/sfu/sfd2d-msazure
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Recovering corrupted database

Fred Toussi-2
It's documented in the Guide.
 
For offline databases, backup is executed from the command line. For live databases, the BACKUP DATABASE statement is used. This executes within the database process. Backup can be performed with BLOCKING which performs a checkpoint and prevents database access during backup. The alternative NOT BLOCKING mode was addedd this year and has been bug-fixed in the SVN trunk.
 
Copying the data directory while the database is running may work in the ZFS file system or similar. It is not guaranteed to work in other systems as the backup set may be inconsistent, due to changes to files while backup is in progress.
 
Fred
 
On Thu, Mar 22, 2012, at 22:43, Sergio Bossa wrote:
On Thu, Mar 22, 2012 at 9:43 PM, Fred Toussi <[hidden email]> wrote:
 
We added a database backup facility to help in this kind of situation. 
 
How does the mentioned backup facility work?
 
Btw, talking about backups, it should be safe to take a backup during normal operations (so while the database is running) by simply copying the data directory over some other place, right?
 
Thanks,
 
Sergio B.
 
--
Sergio Bossa
http://www.linkedin.com/in/sergiob
------------------------------------------------------------------------------
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here
_______________________________________________
Hsqldb-user mailing list
 

------------------------------------------------------------------------------
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here
http://p.sf.net/sfu/sfd2d-msazure
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Recovering corrupted database

Sergio Bossa
Thanks for your answers Fred.

What exactly is the problem with copying the data directory during live operations? Is it data corruption, or just loss of latest inflight transactions? 

Thanks,

Sergio Bossa
Sent by iPhone

Il giorno 23/mar/2012, alle ore 10:59, Fred Toussi <[hidden email]> ha scritto:

It's documented in the Guide.
 
For offline databases, backup is executed from the command line. For live databases, the BACKUP DATABASE statement is used. This executes within the database process. Backup can be performed with BLOCKING which performs a checkpoint and prevents database access during backup. The alternative NOT BLOCKING mode was addedd this year and has been bug-fixed in the SVN trunk.
 
Copying the data directory while the database is running may work in the ZFS file system or similar. It is not guaranteed to work in other systems as the backup set may be inconsistent, due to changes to files while backup is in progress.
 
Fred
 
On Thu, Mar 22, 2012, at 22:43, Sergio Bossa wrote:
On Thu, Mar 22, 2012 at 9:43 PM, Fred Toussi <[hidden email]> wrote:
 
We added a database backup facility to help in this kind of situation. 
 
How does the mentioned backup facility work?
 
Btw, talking about backups, it should be safe to take a backup during normal operations (so while the database is running) by simply copying the data directory over some other place, right?
 
Thanks,
 
Sergio B.
 
--
Sergio Bossa
http://www.linkedin.com/in/sergiob
------------------------------------------------------------------------------
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here
_______________________________________________
Hsqldb-user mailing list
 
------------------------------------------------------------------------------
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here
http://p.sf.net/sfu/sfd2d-msazure
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here
http://p.sf.net/sfu/sfd2d-msazure
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Recovering corrupted database

Fred Toussi-2
"It is not guaranteed to work in other systems as the backup set may be inconsistent," This can mean unrecoverable (corrupt) data, or incomplete data. In the default configuration, if no checkpoint occurs during external copying of files, and if the .data file is copied before the .backup file, followed by the rest of the files, the result may be OK. But as most of the files are open and the backup is done by a different process than the database engine, the OS may cause unanticipated problems.
 
Fred
 
On Fri, Mar 23, 2012, at 14:42, Sergio Bossa wrote:
Thanks for your answers Fred.
 
What exactly is the problem with copying the data directory during live operations? Is it data corruption, or just loss of latest inflight transactions? 
 
Thanks,

Sergio Bossa
Sent by iPhone

Il giorno 23/mar/2012, alle ore 10:59, Fred Toussi <[hidden email]> ha scritto:
 
It's documented in the Guide.
 
For offline databases, backup is executed from the command line. For live databases, the BACKUP DATABASE statement is used. This executes within the database process. Backup can be performed with BLOCKING which performs a checkpoint and prevents database access during backup. The alternative NOT BLOCKING mode was addedd this year and has been bug-fixed in the SVN trunk.
 
Copying the data directory while the database is running may work in the ZFS file system or similar. It is not guaranteed to work in other systems as the backup set may be inconsistent, due to changes to files while backup is in progress.
 
Fred
 
On Thu, Mar 22, 2012, at 22:43, Sergio Bossa wrote:
On Thu, Mar 22, 2012 at 9:43 PM, Fred Toussi <[hidden email]> wrote:
 
We added a database backup facility to help in this kind of situation. 
 
How does the mentioned backup facility work?
 
Btw, talking about backups, it should be safe to take a backup during normal operations (so while the database is running) by simply copying the data directory over some other place, right?
 
Thanks,
 
Sergio B.
 
--
Sergio Bossa
http://www.linkedin.com/in/sergiob
------------------------------------------------------------------------------
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here
_______________________________________________
Hsqldb-user mailing list
 
------------------------------------------------------------------------------
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here
http://p.sf.net/sfu/sfd2d-msazure
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
------------------------------------------------------------------------------
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here
_______________________________________________
Hsqldb-user mailing list
 

------------------------------------------------------------------------------
This SF email is sponsosred by:
Try Windows Azure free for 90 days Click Here
http://p.sf.net/sfu/sfd2d-msazure
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user