Questions on corrupt DB
|
View:
New views
20 Messages
—
Rating Filter:
Alert me
|
| < Prev | 1 - 2 | Next > |
 |
Questions on corrupt DB
by Bayless Kirtley
::
Rate this Message:
Some parts of this message have been removed.
Learn more about Nabble's security policy.
I switched from McKoi to HSQLDB well over a year
ago in the development phase of a rather complex 100% Java point-of-sale
project. The switch was due to stability problems with McKoi and I have had no
problems since. The system runs under Windows XP Pro. It consists of a cash
register module that maintains the database and a networked back office computer
for management use both with 1GB memory and fairly large disks. It has been live
since late July with plenty of activity and no database problems at
all.
I updated from Java 5.0 to 6.0 a couple of days
ago. Then after just a few transactions today, the database suddenly became
corrupted in some way. Attempting to acces some tables gave an "out of memory"
error even when just trying to list records in a relatively small table. The
only other clue I have is that the .data file was almost, if not exactly, double
the size it should have been. It was 262MB and should have been 131MB. I
reloaded a backup from last night and all went well the rest of the day. I also
reverted back to Java 5.0 just for sanity.
First question is: Are the above enough clues to
make a guess as to what might have gone wrong?
Second question: Does HSQL have any tools that
might be able to correct a corrupted database?
Thanks in advance for any advice I can get on this
matter.
Bayless Kirtley
------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ Hsqldb-user mailing list Hsqldb-user@... https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
|
|
Re: Questions on corrupt DB
by fredt
::
Rate this Message:
Some parts of this message have been removed.
Learn more about Nabble's security policy.
The only variant here is the Java version, so one can
reasonably assume some library is not working correctly in the new version.
Or something to do with garbage collection.
In the past there have been several proven JRE issues that
caused HSQLDB crashes. One was with .zip streams that didn't flush when closed,
the other was with java.nio calls throwing undocumented exceptions. These
problems were fixed in later Java 5.0 versions.
We do not have a tool to correct a corrupted database. As we
don't store redundant data, you need to devise an online backup system (just
backup the database files after a checkpoint) or distributed databases.
Also, if you are using the default nio file access for the .data file,
it would be better to switch it off to avoid possible JRE issues.
Happy New Year to all
Fred
------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ Hsqldb-user mailing list Hsqldb-user@... https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
|
|
Re: Questions on corrupt DB
by Bayless Kirtley
::
Rate this Message:
Some parts of this message have been removed.
Learn more about Nabble's security policy.
Thanks Fred.
I suspected as much. I do, of course, have a backup
scheme in place. Nightly shutdown is done under control of my program which then
copies the files to the office computer. On startup the next morning, the office
computer then archives the backup to a daily directory so as to maintain a
week's worth of dailies. In addition, my programs also copy every update
statement to a separate text file for potential restoration but that has not
been thoroughly tested recently. We do know that it is impossible to make
foolproof software as fools are just too ingenious. The obvious potential flaw
in that scheme is shutting down the office computer before making the backup. I
think that happened in this case as I had no backup from the previous day and
had to go back another day to an archive. As of today, I will also make
another copy on the local machine to cover for that. That does still leave the
possibility of just shutting down the cash register before stopping the
database. Maybe I can find a Windows expert who knows how to avoid
that.
As soon as I can refer back to the documentation, I
will turn off nio per your suggestion. If I go to a live checkpoint backup, is
the database useable while the backup is being done or must it be locked?
Thanks again,
Bayless
------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ Hsqldb-user mailing list Hsqldb-user@... https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
|
|
Re: Questions on corrupt DB
by fredt
::
Rate this Message:
Some parts of this message have been removed.
Learn more about Nabble's security policy.
The engine should recover from a power down when the
database is opened the next time. So perhaps it is
better to make the backup at the beginning of the day (as well) before openning
the database.
A live checkpoint + backup is safe. Any attempt to access the
database during the checkpoint waits until completion.
Fred
------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ Hsqldb-user mailing list Hsqldb-user@... https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
|
|
Re: Questions on corrupt DB
by Bayless Kirtley
::
Rate this Message:
Some parts of this message have been removed.
Learn more about Nabble's security policy.
Thanks for the info. I do remember now that you
remind me that the engine will recover from a power down. That really is some
pretty sophisticated stuff. It's good to not have that to worry about.
Bayless
------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ Hsqldb-user mailing list Hsqldb-user@... https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
|
|
Re: Questions on corrupt DB
by bgrommes
::
Rate this Message:
|
|
|
Re: Questions on corrupt DB
by fredt
::
Rate this Message:
|
|
|
Re: Questions on corrupt DB
by Jim Elliott-3
::
Rate this Message:
|
|
|
Re: Questions on corrupt DB
by bgrommes
::
Rate this Message:
|
|
|
Re: Questions on corrupt DB
by Simpson, Blaine A (Contractor)
::
Rate this Message:
Some thoughts, as requested.
If you have WRITE_DELAY low enough, then I believe HSQLDB should restore all "committed" work. If it doesn't behave like this, I'd like to know. Otherwise, you should use transaction settings to implement your transaction design, not as a trick. If your SQL statements are atomic, then simplify your design by using autocommit. -----Original Message----- From: hsqldb-user-bounces@... on behalf of Ben Grommes Sent: Fri 2008-02-29 14:20 To: hsqldb-user@... Subject: Re: [Hsqldb-user] Questions on corrupt DB When the apparent corruption was detected I copied the database files off before shutting down the hsql server. If I delete the .data file and let hsql restore the database from the .log and .backup files several rows from a particular table are missing. However, there are insert statements for the missing rows in the .log file. I noticed Bayless changed his app to use auto commit rather than explicit transactions as part of his set of fixes. In that vein I removed all the SET AUTOCOMMIT FALSE, SET AUTOCOMMIT TRUE and COMMIT lines from the .log file I had saved off and then when restoring the database none of the rows were missing. This seems to indicate some problem related to auto commit. I didn't get any different results with nio off or jdk 1.5 vs jdk 1.6. Thoughts? ------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ Hsqldb-user mailing list Hsqldb-user@... https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
|
|
Re: Questions on corrupt DB
by Simpson, Blaine A (Contractor)
::
Rate this Message:
I have verified that HSQLDB behaves as I described in my previous post.
If WRITE_DELAY is set low enough, statements are persisted to *.log as soon as a "committing" statement executes (incl. DDL statements). During a controlled shutdown, all pending statements are persisted to *.log and immediately rolled back, reversing the non-committed statements. An effective (or actual) CHECKPOINT is performed after this. If there was a non-controlled shutdown, statements which were never persisted to *.log (due to algorithm explained above) will, of course, never be restored. The restoration consists of an effective (or actual) CHECKPOINT. Corrections or refinements are welcome. -----Original Message----- From: hsqldb-user-bounces@... on behalf of Ben Grommes Sent: Fri 2008-02-29 14:20 To: hsqldb-user@... Subject: Re: [Hsqldb-user] Questions on corrupt DB When the apparent corruption was detected I copied the database files off before shutting down the hsql server. If I delete the .data file and let hsql restore the database from the .log and .backup files several rows from a particular table are missing. However, there are insert statements for the missing rows in the .log file. I noticed Bayless changed his app to use auto commit rather than explicit transactions as part of his set of fixes. In that vein I removed all the SET AUTOCOMMIT FALSE, SET AUTOCOMMIT TRUE and COMMIT lines from the .log file I had saved off and then when restoring the database none of the rows were missing. This seems to indicate some problem related to auto commit. I didn't get any different results with nio off or jdk 1.5 vs jdk 1.6. Thoughts? ------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ Hsqldb-user mailing list Hsqldb-user@... https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
|
|
Re: Questions on corrupt DB
by bgrommes
::
Rate this Message:
|
winmail.dat (4K)