#351 saveSorted ... negative seek offset (2)
Hello,
I'm using HSQL 1.7.1 with JRE1.3.1_03 in a Windows
2000 environment. My database contains only one table
with 8,000,000 rows (database size is about 1 GB) and
three indexes on single columns. I had a former problem
with creating resp. dropping an index which didn't work
because of an
"File input/output error: saveSorted java.io.IOException:
Negative seek offset in statement [...]"
error (see bug submission 780397 from 2003-07-30). I
solved this problem by creating a new database and
copying the data row by row.
Now I have a more serious problem: The database can't
start anymore because of the same error (though I used
a backup which has been successfully compacted
before). When I remove the "CREATE INDEX" statements
from the script file I get InputStream-errors (because
the index roots aren't valid anymore), when I
remove "CREATE INDEX" and "SET TABLE INDEX"
commands I get no errors but have no access to the
data anymore (though the database size stays the
same).
No my questions:
Is there another workaround for this situation?
How can I manipulate the "SET TABLE INDEX" command
manually so it doesn't lead to the errors described above?
Will it be possible to use an index spanning more rows in
1.7.2?
Greetings,
Christian
Logged In: YES
user_id=150940
You must have hit a bug in 1.7.1. You should be able to get
the database to open following the procedure below.
Remove the last CREATE INDEX command and save the
script.
Once you've opened it, immdediately issue the
SCRIPT 'filename' command to save the data.
Please also report the full DDL for the table, indexes and set
index commands.
Logged In: YES
user_id=150940
Please ignore the suggested fix in the last post and post the
DDL as requested, plus the exact error message that you are
getting now.
Logged In: YES
user_id=834022
DDL:
CREATE CACHED TABLE TBL300_BETRIEBSDATEN(LNGPRIMKEY
BIGINT NOT NULL PRIMARY KEY,LNG_REFIDGERAET BIGINT
NOT NULL,DATZEITPUNKT TIMESTAMP NOT
NULL,INTFEHLERMELDUNGAG INTEGER,VCBETRIEBSMODUS
VARCHAR(31),VCBETRIEBSSTATUS VARCHAR
(13),VCKOMPRESSORSTATUS VARCHAR
(13),INTFEHLERMELDUNG
INTEGER,INTWAERMETAUSCHERTEMPERATUR
INTEGER,DBLAUSSENTEMPERATUR
DOUBLE,INTINVERTERFREQUENZ
INTEGER,INTHEISSGASTEMPERATUR1
INTEGER,INTHEISSGASTEMPERATUR2
INTEGER,INTHEISSGASTEMPERATUR3
INTEGER,INTKOMPRESSORSTROM1
INTEGER,INTKOMPRESSORSTROM2
INTEGER,INTKOMPRESSORSTROM3
INTEGER,INTSOLLWERTRAUMTEMPERATUR
INTEGER,INTISTWERTRAUMTEMPERATUR
INTEGER,INTFREQUENZANFORDERUNG
INTEGER,INTBEANTWORTETEFREQUENZ INTEGER,INTEEV
INTEGER)
CREATE INDEX IDXGERAET ON TBL300_BETRIEBSDATEN
(LNG_REFIDGERAET)
CREATE INDEX IDXDATUM ON TBL300_BETRIEBSDATEN
(DATZEITPUNKT)
SET TABLE TBL300_BETRIEBSDATEN INDEX '570425104 11728
617074464 5677100'
GRANT ALL ON CLASS "org.hsqldb.Library" TO PUBLIC
GRANT ALL ON CLASS "java.lang.Math" TO PUBLIC
CREATE USER SA PASSWORD "" ADMIN
{<aliases>}
This is the error message I get without changes to the script
file:
C:\Programme\KXCompTrol>jre\bin\java -Xmx1024m -cp
classes/hsqldb.jar org.hsqldb.Server
-database Datenbank/KXCompTrolHistorie -port 9002
server.properties not found, using command line or default
properties
Opening database: Datenbank/KXCompTrolHistorie
HSQLDB server 1.7.1 is running
Use SHUTDOWN to close normally. Use [Ctrl]+[C] to abort
abruptly
Server.run/init: java.sql.SQLException: File input/output error:
error java.sql.
SQLException: File input/output error: saveSorted
java.io.IOException: Negative
seek offset closing Datenbank/KXCompTrolHistorie.data
java.sql.SQLException: File input/output error: error
java.sql.SQLException: Fil
e input/output error: saveSorted java.io.IOException:
Negative seek offset closi
ng Datenbank/KXCompTrolHistorie.data
at org.hsqldb.Trace.getError(Unknown Source)
at org.hsqldb.Trace.error(Unknown Source)
at org.hsqldb.Cache.flush(Unknown Source)
at org.hsqldb.Log.close(Unknown Source)
at org.hsqldb.Log.open(Unknown Source)
at org.hsqldb.Database$Logger.openLog(Unknown
Source)
at org.hsqldb.Database.open(Unknown Source)
at org.hsqldb.Database.<init>(Unknown Source)
at org.hsqldb.Server.openDB(Unknown Source)
at org.hsqldb.Server.run(Unknown Source)
at org.hsqldb.Server.main(Unknown Source)
Thank you for your quick reply!
Christian
Logged In: YES
user_id=150940
The workaround for openning the DB might be one of these:
In the following statement, use the same value for the three
first values. First use the first value, if it doesn't work, use
the second one, and if not, the third one:
SET TABLE TBL300_BETRIEBSDATEN INDEX '570425104
11728 617074464 5677100'
Example:
SET TABLE TBL300_BETRIEBSDATEN INDEX '570425104
570425104 570425104 5677100'
Logged In: YES
user_id=834022
This didn't work - the same error occurs.
Logged In: YES
user_id=150940
Ok, please keep a backup copy of the database files.
Then modify the *.properties file and change
modified=yes to
modified=no
And try to open.
In this test use the original line for SET TABLE ...
As before, issue the SCRIPT 'filename' immediatedly upon
openning.
Use Database Manager for this operation.
Logged In: YES
user_id=834022
This workaround allows me to open the database again. But
the script statement doesn't dump insert statements. When I
treat the working database (which crashed some hours after
the last "SHUTDOWN COMPACT" and re-opening) with the
same workaround it dumps only insert statements for data fed
after the last regular shutdown (that means all data inserted
after the file date of the backup-file). When I open my
application and try to access older data the same error
(...saveSorted...) occurs.
We have restored the database from just before the "crash
date" and altered the log algorithm for inserting averaged
data from one day at once, treating also the existing data.
This will reduce the database size by factor 100 so I guess
that this problem can't occur anymore in the future.
However, thank you very much for your support!
Logged In: YES
user_id=150940
This situation does not happen in 1.7.2 as it checks the size
of the .data file before adding rows.