Unable to access table
|
View:
New views
16 Messages
—
Rating Filter:
Alert me
|
 |
Unable to access table
by Bayless Kirtley
::
Rate this Message:
Some parts of this message have been removed.
Learn more about Nabble's security policy.
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 Hsqldb-user@... https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
|
|
Re: Unable to access table
by fredt
::
Rate this Message:
Some parts of this message have been removed.
Learn more about Nabble's security policy.
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
------------------------------------------------------------------------- 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 Hsqldb-user@... https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
|
|
Re: Unable to access table
by Bayless Kirtley
::
Rate this Message:
Some parts of this message have been removed.
Learn more about Nabble's security policy.
Slap me upside the head, Fred! In looking
at the class that actually performs the logging as I was answering you, I just
discovered that it also writes any related errors to a system file. I had
completely forgotten that. I'll get down there tomorrow and check for any clues
in that error file before reporting back.
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 Hsqldb-user@... https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
|
|
Re: Unable to access table
by Bayless Kirtley
::
Rate this Message:
Some parts of this message have been removed.
Learn more about Nabble's security policy.
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
------------------------------------------------------------------------- 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 Hsqldb-user@... https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
|
|
Re: Unable to access table
by fredt
::
Rate this Message:
Some parts of this message have been removed.
Learn more about Nabble's security policy.
I don't think we can find a cause by looking at the database
files.
Fred
------------------------------------------------------------------------- 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 Hsqldb-user@... https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
|
|
Re: Unable to access table
by Bayless Kirtley
::
Rate this Message:
Some parts of this message have been removed.
Learn more about Nabble's security policy.
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
------------------------------------------------------------------------- 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 Hsqldb-user@... https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
|
|
Re: Unable to access table
by fredt
::
Rate this Message:
Some parts of this message have been removed.
Learn more about Nabble's security policy.
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
------------------------------------------------------------------------- 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 Hsqldb-user@... https://lists.sourceforge.net/lists/listinfo/hsqldb-user |
|
|
Re: Unable to access table
by Francois Valdy
::
Rate this Message:
|
