Menu
▾
▴
Re: [Hsqldb-user] Unable to access table - fixed
|
From: fredt <fr...@us...> - 2008-05-19 18:04:24
|
Yes, in the same circumstances, the space allocated to the deleted row will not be reused. But this bug is relatively rare to occur so the extra space should not be significant. You need to check the growth in the size of the data file and decide on the DEFRAG frequency. Fred ----- Original Message ----- From: "Francois Valdy" <fra...@gm...> To: "HSQLdb user discussions" <hsq...@li...> Sent: 19 May 2008 16:46 Subject: Re: [Hsqldb-user] Unable to access table - fixed Hi Fred, confirmed to be fixed even in full scale test. However should we expect from this fix a change of behavior in .data fragmentation ? Should I "compact defrag" more often ? Thanks, Francois. On Mon, May 19, 2008 at 3:56 AM, fredt <fr...@us...> wrote: > Francois sent me a reproducible test which allowed me to locate and fix an > annoying bug. > > This bug turns up in some scenarios where a large row is deleted or > updated, > then a smaller row is inserted, which in turn is deleted or updated. The > bug > only manifests if between the two operations the cache is reloaded due to > a > large select operation. The net result of the bug is a row may be partly > overwritten by another row in the .data file, causing unrecoverable > errors. > > I have seen a few corrupted databases where this bug could have been the > cause, but until the test case, could not figure out the cause. > > The fix is in CVS (revision 1.31) > > http://hsqldb.cvs.sourceforge.net/hsqldb/hsqldb-dev/src/org/hsqldb/persist/DataFileCache.java > > The file can be used to recompile 1.8.0.9. For 1.8.0.7, the difference > from > the previous revision (1.30) can be applied to 1.8.0.7. Fix will be > included > in 1.8.0.10 when it is released in a week or so. > > Thanks Francois for an excellent test case. > > Fred > > > ----- Original Message ----- > From: "fredt" <fr...@us...> > To: "HSQLdb user discussions" <hsq...@li...> > Sent: 13 May 2008 20:06 > Subject: Re: [Hsqldb-user] Unable to access table > > > Yes, if the test case is reproducible, please send the database to > ft...@cl... and I will have a look and fix it. > > Fred > > ----- Original Message ----- > From: "Francois Valdy" <fra...@gm...> > To: "HSQLdb user discussions" <hsq...@li...> > Sent: 13 May 2008 19:57 > Subject: Re: [Hsqldb-user] Unable to access table > > > Hi Fred, Bayless, > > Any news on that one ? > > I'm encountering the same issue in production database every day > (occurs on 1.8.0.7 and 1.8.0.9, at least). > This issue is 100% reproductible, and I've attempted to extract it to > a minimum test case, which I'll send to you directly (1Mo). > > It results in either out of memory, null pointer, no such element > exceptions, and once it occurs, nothing can save you except reload > from db.backup/db.log. > It can also result in constraint violation duplicate key on columns > that have NO constraint (yes, unbelievable). > > It can happen with a small DB (20k rows, less than 20Mo), with only > ten statements being executed. > But being quite random in its conditions, the larger the DB, the > likely it occurs. > > I hope you can fix it soon, as our client is crashing every day. > This is also seriously impacting our own "trust" in hsqldb. > > Regards, > Francois. > > On Thu, Apr 3, 2008 at 7:23 PM, fredt <fr...@us...> wrote: >> >> I don't think the size of your databas is excessive. I made my suggestion >> in >> my initial reply to your post. The one that refers to writing a test case >> that I can run. >> >> >> Regards >> >> Fred >> >> ----- Original Message ----- >> From: Bayless Kirtley >> To: HSQLdb user discussions >> >> Sent: 03 April 2008 16:34 >> Subject: Re: [Hsqldb-user] Unable to access table >> >> Sorry to hear that. Do you have any other suggestions, ideas or even >> guesses? >> >> Can I give the engine more memory some way and would that help in >> accessing >> a large table? >> >> Could the large table have corrupted something? >> >> I was lucky yesterday that it was a slow day and all was fine until 4:00 >> pm. >> That gave me overnight to correct the database and recreate those few >> transactions that came later. Had it happened in the middle of a busy >> day, >> I >> would have been screwed for sure. I can attribute the previous crash at >> the >> first of the year to possible program problems, primarily in long open >> transactions as well as excess activity in the HoursWorked table. Those >> have >> all been rectified. I see people on this list talking of tables with far >> more records than my Sales although the Sales records are rather long. >> Although it may not be absolutely essential, it would be nice to be able >> to >> maintain at least a year's worth of sales data active. >> >> In short, I may be able to accept some compromises but I can't accept >> database crashes every three months or so. Somehow, I have to get this >> thing >> stabilized. I truly hope my little application does not exceed the >> capacity >> of HSQL as I have found it to satisfy all my needs and desires otherwise. >> My >> hat is off to you and your team of developers for creating a truly worthy >> tool. >> >> Bayless >> >> ----- Original Message ----- >> From: fredt >> To: HSQLdb user discussions >> Sent: Thursday, April 03, 2008 9:59 AM >> Subject: Re: [Hsqldb-user] Unable to access table >> >> I don't think we can find a cause by looking at the database files. >> >> Fred >> ----- Original Message ----- >> From: Bayless Kirtley >> To: HSQLdb user discussions >> Sent: 03 April 2008 04:19 >> Subject: Re: [Hsqldb-user] Unable to access table >> >> Hi Fred, >> >> The error log didn't show anything but there is another development late >> today. Monday I had to just drop the Logs table and recreate it empty. >> That >> seemed to make things work until late today. Suddenly attempts to access >> the >> HoursWorked table produced the same or at least a very similar error as >> they >> tried to close for the night. I wasn't there at the time to see it. When >> I >> checked it, not only did the HoursWorked table give the >> NoSuchElementException but any attempt to access the Sales table gave an >> Out >> Of Memory error. >> >> I had to go back to the 4:00 pm checkpoint to restore a database that >> would >> work. To reduce the size of the Sales table, I deleted all records from >> last >> year. That was 16k records, leaving 12k still in the table. At that point >> all seems well again except I will have to recreate the lost activity >> (not >> too bad as it was a rainy day). I also discovered in this process that >> SHUTDOWN SCRIPT fails, presumably when it hits the corrupted table, and >> leaves the normal shutdown files plus the .script.new file. It appears >> that >> the database does get shut down in some fashion as it can be restarted >> without complaints. The restarted database, however, still has the >> corrupt >> table. It seems that the only recovery is to drop the table and recreate >> it >> empty. >> >> I can send you the entire bad database (zips to 12MB) as well as the 4:00 >> pm >> checkpoint (zips to 2MB) that was still good if those might help to >> determine where the flaw is. The checkpoint can be sent as a SCRIPT >> version >> if you want. I'm afraid it would require more for the corrupted one >> though. >> Please advise if you think these might help. Meanwhile, I think you told >> me >> earlier that any query on a table might need to retrieve the entire table >> into memory. Can I increase the memory that the engine uses? Would that >> help? >> >> Thanks, >> Bayless >> >> ----- Original Message ----- >> From: fredt >> To: HSQLdb user discussions >> Sent: Tuesday, April 01, 2008 12:29 PM >> Subject: Re: [Hsqldb-user] Unable to access table >> >> We don't have a test that can reproduce this. It is therefore hard to >> tell >> what might have caused this. If you wish, you can report your .properties >> file and the pattern in which the data is added to this table and >> removed. >> I >> can then write a test case and run it over several million ops to see if >> we >> can find something. >> >> Fred >> ----- Original Message ----- >> From: Bayless Kirtley >> To: HSQL List >> Sent: 01 April 2008 00:38 >> Subject: [Hsqldb-user] Unable to access table >> >> My HSQL database has a table that can get fairly large. It is basically a >> log of all database activity. I intended to remove old records under >> program >> control daily but it appears that I failed to do so. In attempting to do >> so >> today, I found that any attempt to access the table produced the >> following >> error. >> >> S1000 General Error java.util.NoSuchElementException / Error Code: >> 40 >> / >> State: S1000 >> >> I got that same error whether trying to access, list or even delete >> records. >> This also seemed to adversely affect attempts to perform a SHUTDOWN >> SCRIPT. >> This always seemed to result in a database.script.new file that obviously >> was incomplete. I was finally able to drop the table and recreate it. >> This >> appears to have corrected the problem. >> >> I am currently running HSQL v 1.8.0.7 on Windows XP, both Home and PRO >> versions. The table has one LONGVARCHAR column that represents the >> individual SQL statements issued and probably averages 150 characters >> each. >> There could have been between 300K and 500K records. >> >> Does this problem indicate a database engine problem or is it a DBManager >> problem? In this case, I did intend to delete old records as I have had a >> similar problem with the same table earlier. I will certainly correct >> that >> ASAP. But I am a little concerned that I may be running into a serious >> problem that cannot be overcome? >> >> Thanks in advance, >> Bayless >> >> >> ________________________________ >> >> >> ------------------------------------------------------------------------- >> Check out the new SourceForge.net Marketplace. >> It's the best place to buy or sell services for >> just about anything Open Source. >> http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace >> >> ________________________________ >> >> >> _______________________________________________ >> Hsqldb-user mailing list >> Hsq...@li... >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user >> >> >> >> >> >> >> ________________________________ >> >> >> ------------------------------------------------------------------------- >> Check out the new SourceForge.net Marketplace. >> It's the best place to buy or sell services for >> just about anything Open Source. >> http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace >> >> ________________________________ >> >> >> _______________________________________________ >> Hsqldb-user mailing list >> Hsq...@li... >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user >> >> >> ________________________________ >> >> >> No virus found in this incoming message. >> Checked by AVG. >> Version: 7.5.519 / Virus Database: 269.22.3/1354 - Release Date: 4/1/2008 >> 5:38 AM >> >> >> >> >> >> >> >> >> ________________________________ >> >> >> ------------------------------------------------------------------------- >> Check out the new SourceForge.net Marketplace. >> It's the best place to buy or sell services for >> just about anything Open Source. >> http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace >> >> ________________________________ >> >> >> _______________________________________________ >> Hsqldb-user mailing list >> Hsq...@li... >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user >> >> >> >> >> >> >> ________________________________ >> >> >> ------------------------------------------------------------------------- >> Check out the new SourceForge.net Marketplace. >> It's the best place to buy or sell services for >> just about anything Open Source. >> http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace >> >> ________________________________ >> >> >> _______________________________________________ >> Hsqldb-user mailing list >> Hsq...@li... >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user >> >> >> ________________________________ >> >> >> No virus found in this incoming message. >> Checked by AVG. >> Version: 7.5.519 / Virus Database: 269.22.5/1357 - Release Date: 4/3/2008 >> 10:48 AM >> >> >> >> >> >> >> >> >> ________________________________ >> >> >> ------------------------------------------------------------------------- >> Check out the new SourceForge.net Marketplace. >> It's the best place to buy or sell services for >> just about anything Open Source. >> http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace >> >> ________________________________ >> >> >> _______________________________________________ >> Hsqldb-user mailing list >> Hsq...@li... >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user >> >> >> >> >> >> ------------------------------------------------------------------------- >> Check out the new SourceForge.net Marketplace. >> It's the best place to buy or sell services for >> just about anything Open Source. >> http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace >> _______________________________________________ >> Hsqldb-user mailing list >> Hsq...@li... >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user >> >> > > ------------------------------------------------------------------------- > 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 > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > > > ------------------------------------------------------------------------- > 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 > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > > > ------------------------------------------------------------------------- > 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 > Hsq...@li... > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > ------------------------------------------------------------------------- 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 Hsq...@li... https://lists.sourceforge.net/lists/listinfo/hsqldb-user |