Quantcast

Database corrupted after power loss!

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Database corrupted after power loss!

giri
Hello,

after power loss, our HSQLDB 2.3.1 database does not start.

We created this DB about month ago, using HSQLDB 2.3.0. We then imported ~700 MB data from another DB, so the new DB was "fresh".
Then we upgraded to HSQLDB 2.3.1 without performing Shutdown Compact command (at that time, we were not aware that it is needed).

Now, beside of .lobs .script and .properties files, there is
800MB .data file (all tables are cached)
14 MB .backup file
100kB .log file.

When I try to start DB, it gives me error which I attach into error1.txt attachment.

When I delete .log and .backup file (I suppose they contain "changes" made since last shutdown. Those changes, I am willing to loose), the DB starts... However when I browse thru tables with some SQL client, it gives me error attached in error2.txt attachment. So I am not able to read my data..

When I perform Shutdown compact (with deleted .log and .backup), it fails on exception in attachment error3.txt.

Could you please help me to get access to my data back? We kind of really need them back as soon as possible, they are essential for us.
Is it for example possible to edit DB files by hand and fix / remove faulty segments?

Thank you very much,
Jan

error1.txt
error2.txt
error3.txt
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database corrupted after power loss!

Fred Toussi-2
The database files may be damaged.

You should never delete the .backup file. You can delete the .log file
which contains the recent changes.

You do not need to perform SHUTDOWN COMPACT to move from version 2.3.0
to 2.3.1.

Fred


On Wed, Nov 6, 2013, at 16:47, giri wrote:

> Hello,
>
> after power loss, our HSQLDB 2.3.1 database does not start.
>
> We created this DB about month ago, using HSQLDB 2.3.0. We then imported
> ~700 MB data from another DB, so the new DB was "fresh".
> Then we upgraded to HSQLDB 2.3.1 without performing Shutdown Compact
> command
> (at that time, we were not aware that it is needed).
>
> Now, beside of .lobs .script and .properties files, there is
> 800MB .data file (all tables are cached)
> 14 MB .backup file
> 100kB .log file.
>
> When I try to start DB, it gives me error which I attach into error1.txt
> attachment.
>
> When I delete .log and .backup file (I suppose they contain "changes"
> made
> since last shutdown. Those changes, I am willing to loose), the DB
> starts...
> However when I browse thru tables with some SQL client, it gives me error
> attached in error2.txt attachment. So I am not able to read my data..
>
> When I perform Shutdown compact (with deleted .log and .backup), it fails
> on
> exception in attachment error3.txt.
>
> Could you please help me to get access to my data back? We kind of really
> need them back as soon as possible, they are essential for us.
> Is it for example possible to edit DB files by hand and fix / remove
> faulty
> segments?
>
> Thank you very much,
> Jan
>
> error1.txt <http://hsqldb.10974.n7.nabble.com/file/n3979/error1.txt>  
> error2.txt <http://hsqldb.10974.n7.nabble.com/file/n3979/error2.txt>  
> error3.txt <http://hsqldb.10974.n7.nabble.com/file/n3979/error3.txt>  
>
>
>
> --
> View this message in context:
> http://hsqldb.10974.n7.nabble.com/Database-corrupted-after-power-loss-tp3979.html
> Sent from the HSQLDB - User mailing list archive at Nabble.com.
>
> ------------------------------------------------------------------------------
> November Webinars for C, C++, Fortran Developers
> Accelerate application performance with scalable programming models.
> Explore
> techniques for threading, error checking, porting, and tuning. Get the
> most
> from the latest Intel processors and coprocessors. See abstracts and
> register
> http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
November Webinars for C, C++, Fortran Developers
Accelerate application performance with scalable programming models. Explore
techniques for threading, error checking, porting, and tuning. Get the most
from the latest Intel processors and coprocessors. See abstracts and register
http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database corrupted after power loss!

giri
Thank you for your reply..

Currently, we suspect faulty RAM module to be a source of our problems.

We managed to recover a week old backup, which was not corrupted..

We also managed to recover .log files, which were filled by data during the last week. Files .backup from during last week are damaged and we can not use those.

Now the question is - is it reasonable to expect, that if we take week old, correct DB and apply all lines from all .log files created during this week (together over 20MB), we end up with current database?

Do the .log files contain simply all SQL commands (changing some data) performed since start of DB?
Our app does not use CHECKPOINT, Shutdown Compact nor any similar HSQLDB specific functions, therefore if .log contains all changes since start, it should be possible to re-apply it on DB backed up just before that start, correct?

One problem is that .log files do not use "update" SQL functions, but rather "delete"&"insert"..
Currently we work on code that would transform those "delete"&"insert" into "update" queries, so we would not get into troubles with foreign keys etc.

What do you think about that?

Thank you very much for your help,
Jan
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database corrupted after power loss!

Fred Toussi-2
Please also check there is no other change in the software, for example
JDK version.

In fact, using a .log file is much easier than you think.

You need the setting SET FILES LOG SIZE 0 in your database to prevent
automatic checkpoint.

1. You connect to a copy of the backup database and perform SHUTDOWN
after it has processed its log file. This is called the "old database"
2. You change a line in the .properties file of the old database to
"modified=yes"
3. You add the .log file from the latest database to the older database
files.
4. You connect to the older database and perform SHUTDOWN after it has
processed the new log.

The database is now updated and usable. After checking it, you can back
it up for the future.

Fred

On Thu, Nov 7, 2013, at 11:46, giri wrote:

> Thank you for your reply..
>
> Currently, we suspect faulty RAM module to be a source of our problems.
>
> We managed to recover a week old backup, which was not corrupted..
>
> We also managed to recover .log files, which were filled by data during
> the
> last week. Files .backup from during last week are damaged and we can not
> use those.
>
> Now the question is - is it reasonable to expect, that if we take week
> old,
> correct DB and apply all lines from all .log files created during this
> week
> (together over 20MB), we end up with current database?
>
> Do the .log files contain simply all SQL commands (changing some data)
> performed since start of DB?
> Our app does not use CHECKPOINT, Shutdown Compact nor any similar HSQLDB
> specific functions, therefore if .log contains all changes since start,
> it
> should be possible to re-apply it on DB backed up just before that start,
> correct?
>
> One problem is that .log files do not use "update" SQL functions, but
> rather
> "delete"&"insert"..
> Currently we work on code that would transform those "delete"&"insert"
> into
> "update" queries, so we would not get into troubles with foreign keys
> etc.
>
> What do you think about that?
>
> Thank you very much for your help,
> Jan
>
>
>
> --
> View this message in context:
> http://hsqldb.10974.n7.nabble.com/Database-corrupted-after-power-loss-tp3979p3981.html
> Sent from the HSQLDB - User mailing list archive at Nabble.com.
>
> ------------------------------------------------------------------------------
> November Webinars for C, C++, Fortran Developers
> Accelerate application performance with scalable programming models.
> Explore
> techniques for threading, error checking, porting, and tuning. Get the
> most
> from the latest Intel processors and coprocessors. See abstracts and
> register
> http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
November Webinars for C, C++, Fortran Developers
Accelerate application performance with scalable programming models. Explore
techniques for threading, error checking, porting, and tuning. Get the most
from the latest Intel processors and coprocessors. See abstracts and register
http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Database corrupted after power loss!

giri
Thank you Fred!

You just saved our day! The approach you described worked like a charm and now we have "only a couple" of lost data due to some .log files we were not able to restore. But that is something we expected.

Just out of curiosity - are there any cases reported, when changing JDK caused similar problems? On our machine, we update Java (we have only JDK, not SDK) as soon as new release is out..

Thank you one more time
Jan
Loading...