Recover 1.8.0 database with corrupt data and script files

[JEL]

Greetings and thanks in advance for help -

I have a 1.8.0 HSQLDB database that got corrupted when the linux system was shutdown.
The script and data files were both corrupted - the script only had 3 lines.

I have the backup file (which I have saved to a separate location) and have tried various permutations of restarting the database with and without the script file, data file, and with an edited script file but cannot get at the data.  I can connect using a jdbc client and the tables are there but without any rows.

When I open the re-constituted (from backup) data file in a hex editor, it seems to have good and complete data.

Assuming that all I have is a good backup file (and some older copies of the script file) is there any way that I can recover the database?

Many Thanks -
Joel Lieberman

[Fred Toussi-2]

Although the actual table data is probably intact in the .backup file,
this cannot be recovered without the .script file.

See if there was any .script.new file. Otherwise, it cannot be done.

If you have an older, releatively recent .script file, see if you can
use it to get to the data, but it is not guaranteed.

Fred

On Wed, 30 Sep 2009 11:08 -0700, "JEL" <joel.lieberman@...>
wrote:

>
> Greetings and thanks in advance for help -
>
> I have a 1.8.0 HSQLDB database that got corrupted when the linux system
> was
> shutdown.
> The script and data files were both corrupted - the script only had 3
> lines.
>
> I have the backup file (which I have saved to a separate location) and
> have
> tried various permutations of restarting the database with and without
> the
> script file, data file, and with an edited script file but cannot get at
> the
> data.  I can connect using a jdbc client and the tables are there but
> without any rows.
>
> When I open the re-constituted (from backup) data file in a hex editor,
> it
> seems to have good and complete data.
>
> Assuming that all I have is a good backup file (and some older copies of
> the
> script file) is there any way that I can recover the database?
>
> Many Thanks -
> Joel Lieberman
> --
> View this message in context:
> http://www.nabble.com/Recover-1.8.0-database-with-corrupt-data-and-script-files-tp25684739p25684739.html
> Sent from the HSQLDB - User mailing list archive at Nabble.com.
>
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry® Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9-12, 2009. Register
> now!
> http://p.sf.net/sfu/devconf
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9-12, 2009. Register now!
http://p.sf.net/sfu/devconf
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

[JEL]

Thanks Fred -
I only have a few tables.  My sense is that the SET TABLE -- offsets (numeric pointers) are what are stopping me from using a recent script file.  Could I manually use a hex editor to identify the correct offsets in the data file and reconstruct these few lines in the script file?  It might be tedious, but if you could suggest a manual reconstruction process, I would like to try.
Thanks -
Joel

Fred Toussi-2 wrote:

Although the actual table data is probably intact in the .backup file,
this cannot be recovered without the .script file.

See if there was any .script.new file. Otherwise, it cannot be done.

If you have an older, releatively recent .script file, see if you can
use it to get to the data, but it is not guaranteed.

Fred

On Wed, 30 Sep 2009 11:08 -0700, "JEL" <[e-mail]>
wrote:
>
> Greetings and thanks in advance for help -
>
> I have a 1.8.0 HSQLDB database that got corrupted when the linux system
> was
> shutdown.
> The script and data files were both corrupted - the script only had 3
> lines.
>
> I have the backup file (which I have saved to a separate location) and
> have
> tried various permutations of restarting the database with and without
> the
> script file, data file, and with an edited script file but cannot get at
> the
> data.  I can connect using a jdbc client and the tables are there but
> without any rows.
>
> When I open the re-constituted (from backup) data file in a hex editor,
> it
> seems to have good and complete data.
>
> Assuming that all I have is a good backup file (and some older copies of
> the
> script file) is there any way that I can recover the database?
>
> Many Thanks -
> Joel Lieberman
> --
> View this message in context:
> http://www.nabble.com/Recover-1.8.0-database-with-corrupt-data-and-script-files-tp25684739p25684739.html
> Sent from the HSQLDB - User mailing list archive at Nabble.com.
>
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry® Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9-12, 2009. Register
> now!
> http://p.sf.net/sfu/devconf
> _______________________________________________
> Hsqldb-user mailing list
> [e-mail]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9-12, 2009. Register now!
http://p.sf.net/sfu/devconf
_______________________________________________
Hsqldb-user mailing list
[e-mail]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

[gould-4]

Thinks like this have happened to our customers a few times over the years, and the effects are pretty devastating for our software. Shouldn't protection from corruption on power loss be built into the database? Has anything changed in 1.9 to help protect against this? We've taken to implementing a number of measures to try to mitigate against catastrophic failures like this, such as periodically doing a CHECKPOINT, and copying the database off to an autobackup directory upon startup, but none of these leave a very good taste in my mouth.

Thoughts?
Carl Gould
Inductive Automation

On Wed Sep 30 11:19:03 PDT 2009, Fred Toussi wrote:

Although the actual table data is probably intact in the .backup file, this cannot be recovered without the .script file. See if there was any .script.new file. Otherwise, it cannot be done. If you have an older, releatively recent .script file, see if you can use it to get to the data, but it is not guaranteed. Fred On Wed, 30 Sep 2009 11:08 -0700, "JEL" wrote: > > Greetings and thanks in advance for help - > > I have a 1.8.0 HSQLDB database that got corrupted when the linux system > was > shutdown. > The script and data files were both corrupted - the script only had 3 > lines. > > I have the backup file (which I have saved to a separate location) and > have > tried various permutations of restarting the database with and without > the > script file, data file, and with an edited script file but cannot get at > the > data. I can connect using a jdbc client and the tables are there but > without any rows. > > When I open the re-constituted (from backup) data file in a hex editor, > it > seems to have good and complete data. > > Assuming that all I have is a good backup file (and some older copies of > the > script file) is there any way that I can recover the database? > > Many Thanks - > Joel Lieberman > -- > View this message in context: > http://www.nabble.com/Recover-1.8.0-database-with-corrupt-data-and-script-files-tp25684739p25684739.html > Sent from the HSQLDB - User mailing list archive at Nabble.com. > > > ------------------------------------------------------------------------------ > Come build with us! The BlackBerry® Developer Conference in SF, CA > is the only developer event you need to attend this year. Jumpstart your > developing skills, take BlackBerry mobile applications to market and stay > ahead of the curve. Join us from November 9-12, 2009. Register > now! > http://p.sf.net/sfu/devconf > _______________________________________________ > Hsqldb-user mailing list > Hsqldb-user@... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user ------------------------------------------------------------------------------ Come build with us! The BlackBerry® Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9-12, 2009. Register now! http://p.sf.net/sfu/devconf _______________________________________________ Hsqldb-user mailing list Hsqldb-user@... https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9-12, 2009. Register now!
http://p.sf.net/sfu/devconf
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

[JEL]

On Wed, 2009-09-30 at 12:46 -0700, gould@... wrote:

> Thinks like this have happened to our customers a few times over the
> years, and the effects are pretty devastating for our software.
> Shouldn't protection from corruption on power loss be built into the
> database? Has anything changed in 1.9 to help protect against this?
> We've taken to implementing a number of measures to try to mitigate
> against catastrophic failures like this, such as periodically doing a
> CHECKPOINT, and copying the database off to an autobackup directory
> upon startup, but none of these leave a very good taste in my mouth.
>
>
> Thoughts?
> Carl Gould
> Inductive Automation
>

[snip]

Hi Carl -

I actually blame myself (not the software) for the problem because I
didn't have a recent backup.  I was just hoping that a manual method of
recovery might be possible.

As an Oracle database specialist, I always keep both physical and
logical backups available and have their generation well automated.  I
have also practiced full disaster recovery on many occasions.
I think that anything can get corrupted - including loss of a whole disk
or server.  I just failed to keep my business rules working in my
personal space (with HSQLDB).  I know - I should be very ashamed!

I believe that HSQLDB does have the ability to provide both logical and
physical backups via various kinds of shutdown or checkpoint, as you
indicated, followed by file copy to a safe location.  If I can dig out
from under the current mess, I will make setting up some cron jobs, to
accomplish this, my first priority.

I just need to think through how best to do things with the database
"live".

I have started to reconstruct things by using a hex dump followed by
ascii conversion.  I am trying to reconstruct by re-creating records in
an older database using copy and paste from the source.  It seems to
work but is very tedious.

Cheers -
Joel
>


------------------------------------------------------------------------------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9-12, 2009. Register now!
http://p.sf.net/sfu/devconf
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

[gould-4]

I can appreciate the need for backups, but I guess my point is that to the extent possible, this should be baked into HSQLDB itself. The reason is that a database like HSQLDB is used in a lot of embedded scenarios, where the presence of a database is hidden from the end user, and there is no DBA around. It needs to be maintenance-free. In this sort of environment, the backup, corruption detection, and restoration needs to be automatic. Because this environment is so common for HSQLDB, it seems like it would serve the most good to be included by default in the database itself, especially if something as mundane as power loss can corrupt the files.

Carl Gould
Inductive Automation

------------------------------------------------------------------------------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9-12, 2009. Register now!
http://p.sf.net/sfu/devconf
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

[Fred Toussi-2]

Some parts of this message have been removed. Learn more about Nabble's security policy.

Certainly.

 

Over the years we have found various causes for occasional corruption. Each of these causes showed itself in completely different usage scenarios, and has been fixed. Main ones were:

 

1. properties file disappearing during database shutdown coinciding with machine shutdown -- discovered and fixed about three years ago

2. certain patterns of delete, insert, select causing overwriting the data -- discovered and fixed about two years ago

3. use of java.nio data file together with frequent CHECKPOINT DEFRAG (automatic or by user) resulting in an out of data .backup file - discovered and fixed recently in 1.8.1 and 1.9

4. use of small cache sizes with very large row data sometimes causing index corruption - discovered about a year ago - fixed in 1.9 with major rewrite of some parts

 

In addition, version 1.9 has built-in facilities for online and offline backup and restore. You can perform an online backup, which also does a checkpoint, anytime you want.

 

We have built in protection against power-cut, but I cannot explain how this database ended up with an incomplete .script file. The script file is never kept open. When it is written, it is written to a different file, and when the writing is complete, the file is renamed. The OS shouldn't truncate the file. Anyway, I follow up all of our user's feedback and will find a way to work around potential issues like this.

 

Such problems have not been been limited to HSQLDB. Derby/JavaDB has been out for nearly 4 years and has had corruption problems in some versions.

 

Fred

 

On Wed, 30 Sep 2009 12:46 -0700, gould@... wrote:

Thinks like this have happened to our customers a few times over the years, and the effects are pretty devastating for our software. Shouldn't protection from corruption on power loss be built into the database? Has anything changed in 1.9 to help protect against this? We've taken to implementing a number of measures to try to mitigate against catastrophic failures like this, such as periodically doing a CHECKPOINT, and copying the database off to an autobackup directory upon startup, but none of these leave a very good taste in my mouth.

Thoughts?
Carl Gould
Inductive Automation

 

On Wed Sep 30 11:19:03 PDT 2009, Fred Toussi wrote:

Although the actual table data is probably intact in the .backup file, this cannot be recovered without the .script file. See if there was any .script.new file. Otherwise, it cannot be done. If you have an older, releatively recent .script file, see if you can use it to get to the data, but it is not guaranteed. Fred On Wed, 30 Sep 2009 11:08 -0700, "JEL" wrote: > > Greetings and thanks in advance for help - > > I have a 1.8.0 HSQLDB database that got corrupted when the linux system > was > shutdown. > The script and data files were both corrupted - the script only had 3 > lines. > > I have the backup file (which I have saved to a separate location) and > have > tried various permutations of restarting the database with and without > the > script file, data file, and with an edited script file but cannot get at > the > data. I can connect using a jdbc client and the tables are there but > without any rows. > > When I open the re-constituted (from backup) data file in a hex editor, > it > seems to have good and complete data. > > Assuming that all I have is a good backup file (and some older copies of > the > script file) is there any way that I can recover the database? > > Many Thanks - > Joel Lieberman > -- > View this message in context: > http://www.nabble.com/Recover-1.8.0-database-with-corrupt-data-and-script-files-tp25684739p25684739.html > Sent from the HSQLDB - User mailing list archive at Nabble.com. > > > ------------------------------------------------------------------------------ > Come build with us! The BlackBerry® Developer Conference in SF, CA > is the only developer event you need to attend this year. Jumpstart your > developing skills, take BlackBerry mobile applications to market and stay > ahead of the curve. Join us from November 9-12, 2009. Register > now! > http://p.sf.net/sfu/devconf > _______________________________________________ > Hsqldb-user mailing list > Hsqldb-user@... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user ------------------------------------------------------------------------------ Come build with us! The BlackBerry® Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9-12, 2009. Register now! http://p.sf.net/sfu/devconf _______________________________________________ Hsqldb-user mailing list Hsqldb-user@... https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9-12, 2009. Register now!
http://p.sf.net/sfu/devconf
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

[Fred Toussi-2]

That's correct.

This isn't easy though.

If you can find one valid offset for each table, then you can find the
index root and use it in the SET TABLE command.

By comparing to a backup database, you may find a pointer to a row, then
note the bytes that are written.

        -- 4 byte size of row is written first
        out.writeIntData(iBalance);
        out.writeIntData((iLeft == NO_POS) ? 0
                                           : iLeft);
        out.writeIntData((iRight == NO_POS) ? 0
                                            : iRight);
        out.writeIntData((iParent == NO_POS) ? 0
                                             : iParent);

If iParent is not 0, then go to the position indicated by iParent, and
repeat (this is a balanced binary tree, so it shouldn't take more than
several iterations)

Then use this position for ALL the values for the SET TABLE command

Retry until you open the database and do a SELECT TOP 100 * FROM T for
all the tables.

At this point, you should be able to perform SHUTDOWN SCRIPT. (Otherwise
the database is not functional)

FRED

On Wed, 30 Sep 2009 11:35 -0700, "JEL" <joel.lieberman@...>
wrote:

>
> Thanks Fred -
> I only have a few tables.  My sense is that the SET TABLE -- offsets
> (numeric pointers) are what are stopping me from using a recent script
> file.
> Could I manually use a hex editor to identify the correct offsets in the
> data file and reconstruct these few lines in the script file?  It might
> be
> tedious, but if you could suggest a manual reconstruction process, I
> would
> like to try.
> Thanks -
> Joel
>
> Fred Toussi-2 wrote:
> >
> > Although the actual table data is probably intact in the .backup file,
> > this cannot be recovered without the .script file.
> >
> > See if there was any .script.new file. Otherwise, it cannot be done.
> >
> > If you have an older, releatively recent .script file, see if you can
> > use it to get to the data, but it is not guaranteed.
> >
> > Fred
> >
> > On Wed, 30 Sep 2009 11:08 -0700, "JEL" <joel.lieberman@...>
> > wrote:
> >>
> >> Greetings and thanks in advance for help -
> >>
> >> I have a 1.8.0 HSQLDB database that got corrupted when the linux system
> >> was
> >> shutdown.
> >> The script and data files were both corrupted - the script only had 3
> >> lines.
> >>
> >> I have the backup file (which I have saved to a separate location) and
> >> have
> >> tried various permutations of restarting the database with and without
> >> the
> >> script file, data file, and with an edited script file but cannot get at
> >> the
> >> data.  I can connect using a jdbc client and the tables are there but
> >> without any rows.
> >>
> >> When I open the re-constituted (from backup) data file in a hex editor,
> >> it
> >> seems to have good and complete data.
> >>
> >> Assuming that all I have is a good backup file (and some older copies of
> >> the
> >> script file) is there any way that I can recover the database?
> >>
> >> Many Thanks -
> >> Joel Lieberman
> >> --
> >> View this message in context:
> >> http://www.nabble.com/Recover-1.8.0-database-with-corrupt-data-and-script-files-tp25684739p25684739.html
> >> Sent from the HSQLDB - User mailing list archive at Nabble.com.
> >>
> >>
> >> ------------------------------------------------------------------------------
> >> Come build with us! The BlackBerry® Developer Conference in SF, CA
> >> is the only developer event you need to attend this year. Jumpstart your
> >> developing skills, take BlackBerry mobile applications to market and stay
> >> ahead of the curve. Join us from November 9-12, 2009. Register
> >> now!
> >> http://p.sf.net/sfu/devconf
> >> _______________________________________________
> >> Hsqldb-user mailing list
> >> Hsqldb-user@...
> >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >
> > ------------------------------------------------------------------------------
> > Come build with us! The BlackBerry® Developer Conference in SF, CA
> > is the only developer event you need to attend this year. Jumpstart your
> > developing skills, take BlackBerry mobile applications to market and stay
> > ahead of the curve. Join us from November 9-12, 2009. Register
> > now!
> > http://p.sf.net/sfu/devconf
> > _______________________________________________
> > Hsqldb-user mailing list
> > Hsqldb-user@...
> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >
> >
>
> --
> View this message in context:
> http://www.nabble.com/Recover-1.8.0-database-with-corrupt-data-and-script-files-tp25684739p25686558.html
> Sent from the HSQLDB - User mailing list archive at Nabble.com.
>
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry® Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9-12, 2009. Register
> now!
> http://p.sf.net/sfu/devconf
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9-12, 2009. Register now!
http://p.sf.net/sfu/devconf
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

[Fred Toussi-2]

Joel,

If you have any suggestions for improvements in this area for 1.9,
please let us know and we will incorporate.

Fred

On Wed, 30 Sep 2009 16:24 -0400, "Joel E. Lieberman"
<joel.lieberman@...> wrote:

> On Wed, 2009-09-30 at 12:46 -0700, gould@... wrote:
> > Thinks like this have happened to our customers a few times over the
> > years, and the effects are pretty devastating for our software.
> > Shouldn't protection from corruption on power loss be built into the
> > database? Has anything changed in 1.9 to help protect against this?
> > We've taken to implementing a number of measures to try to mitigate
> > against catastrophic failures like this, such as periodically doing a
> > CHECKPOINT, and copying the database off to an autobackup directory
> > upon startup, but none of these leave a very good taste in my mouth.
> >
> >
> > Thoughts?
> > Carl Gould
> > Inductive Automation
> >
> [snip]
>
> Hi Carl -
>
> I actually blame myself (not the software) for the problem because I
> didn't have a recent backup.  I was just hoping that a manual method of
> recovery might be possible.
>
> As an Oracle database specialist, I always keep both physical and
> logical backups available and have their generation well automated.  I
> have also practiced full disaster recovery on many occasions.
> I think that anything can get corrupted - including loss of a whole disk
> or server.  I just failed to keep my business rules working in my
> personal space (with HSQLDB).  I know - I should be very ashamed!
>
> I believe that HSQLDB does have the ability to provide both logical and
> physical backups via various kinds of shutdown or checkpoint, as you
> indicated, followed by file copy to a safe location.  If I can dig out
> from under the current mess, I will make setting up some cron jobs, to
> accomplish this, my first priority.
>
> I just need to think through how best to do things with the database
> "live".
>
> I have started to reconstruct things by using a hex dump followed by
> ascii conversion.  I am trying to reconstruct by re-creating records in
> an older database using copy and paste from the source.  It seems to
> work but is very tedious.
>
> Cheers -
> Joel
> >
>
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry® Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9-12, 2009. Register
> now!
> http://p.sf.net/sfu/devconf
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9-12, 2009. Register now!
http://p.sf.net/sfu/devconf
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

[gould-4]

Fred,

Thanks for the detailed response. I'm glad to know about the new backup/restore facilities in 1.9, which we will be testing in our development branch shortly.

Thanks,
Carl Gould
Inductive Automation

On Wed Sep 30 14:02:43 PDT 2009, Fred Toussi wrote:

Certainly.

 

Over the years we have found various causes for occasional corruption. Each of these causes showed itself in completely different usage scenarios, and has been fixed. Main ones were:

 

1. properties file disappearing during database shutdown coinciding with machine shutdown -- discovered and fixed about three years ago

2. certain patterns of delete, insert, select causing overwriting the data -- discovered and fixed about two years ago

3. use of java.nio data file together with frequent CHECKPOINT DEFRAG (automatic or by user) resulting in an out of data .backup file - discovered and fixed recently in 1.8.1 and 1.9

4. use of small cache sizes with very large row data sometimes causing index corruption - discovered about a year ago - fixed in 1.9 with major rewrite of some parts

 

In addition, version 1.9 has built-in facilities for online and offline backup and restore. You can perform an online backup, which also does a checkpoint, anytime you want.

 

We have built in protection against power-cut, but I cannot explain how this database ended up with an incomplete .script file. The script file is never kept open. When it is written, it is written to a different file, and when the writing is complete, the file is renamed. The OS shouldn't truncate the file. Anyway, I follow up all of our user's feedback and will find a way to work around potential issues like this.

 

Such problems have not been been limited to HSQLDB. Derby/JavaDB has been out for nearly 4 years and has had corruption problems in some versions.

 

Fred

 

On Wed, 30 Sep 2009 12:46 -0700, gould@... wrote:

Thinks like this have happened to our customers a few times over the years, and the effects are pretty devastating for our software. Shouldn't protection from corruption on power loss be built into the database? Has anything changed in 1.9 to help protect against this? We've taken to implementing a number of measures to try to mitigate against catastrophic failures like this, such as periodically doing a CHECKPOINT, and copying the database off to an autobackup directory upon startup, but none of these leave a very good taste in my mouth.

Thoughts?
Carl Gould
Inductive Automation

 

On Wed Sep 30 11:19:03 PDT 2009, Fred Toussi wrote:

Although the actual table data is probably intact in the .backup file, this cannot be recovered without the .script file. See if there was any .script.new file. Otherwise, it cannot be done. If you have an older, releatively recent .script file, see if you can use it to get to the data, but it is not guaranteed. Fred On Wed, 30 Sep 2009 11:08 -0700, "JEL" wrote: > > Greetings and thanks in advance for help - > > I have a 1.8.0 HSQLDB database that got corrupted when the linux system > was > shutdown. > The script and data files were both corrupted - the script only had 3 > lines. > > I have the backup file (which I have saved to a separate location) and > have > tried various permutations of restarting the database with and without > the > script file, data file, and with an edited script file but cannot get at > the > data. I can connect using a jdbc client and the tables are there but > without any rows. > > When I open the re-constituted (from backup) data file in a hex editor, > it > seems to have good and complete data. > > Assuming that all I have is a good backup file (and some older copies of > the > script file) is there any way that I can recover the database? > > Many Thanks - > Joel Lieberman > -- > View this message in context: > http://www.nabble.com/Recover-1.8.0-database-with-corrupt-data-and-script-files-tp25684739p25684739.html > Sent from the HSQLDB - User mailing list archive at Nabble.com. > > > ------------------------------------------------------------------------------ > Come build with us! The BlackBerry® Developer Conference in SF, CA > is the only developer event you need to attend this year. Jumpstart your > developing skills, take BlackBerry mobile applications to market and stay > ahead of the curve. Join us from November 9-12, 2009. Register > now! > http://p.sf.net/sfu/devconf > _______________________________________________ > Hsqldb-user mailing list > Hsqldb-user@... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user ------------------------------------------------------------------------------ Come build with us! The BlackBerry® Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9-12, 2009. Register now! http://p.sf.net/sfu/devconf _______________________________________________ Hsqldb-user mailing list Hsqldb-user@... https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9-12, 2009. Register now!
http://p.sf.net/sfu/devconf
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user