Issue 103682 - HSQLDB Index lost (Violation of unique constraint SYS_PK_260...)
Summary: HSQLDB Index lost (Violation of unique constraint SYS_PK_260...)
Status: CONFIRMED
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 3.1
Hardware: PC Windows XP
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2009-07-21 08:50 UTC by windl
Modified: 2017-05-20 10:47 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments

Note You need to log in before you can comment on or make changes to this issue.
Description windl 2009-07-21 08:50:33 UTC
In a database design with many tables and relations, one table has an INTEGER ID
that is used as primary key, and it's value is autoincremented. After some time
I could not enter any new data into that table. The error message was:
Violation of unique constraint SYS_PK_260: duplicate value(s) for column(s) "ID"
in statement [INSERT INTO "LSystem_VDisk" ("LSystem_ID", "valid_from",
"VDisk_ID") VALUES(?,?,?)]
As closing/re-opening the table did not work, just as closing/re-opening the
application did not, I tried something like "rebuilding the index" (which does
not exist). As the index is not used in a releation, I though I could drop it
and then re-create it. When I tried to drop the index (or modify it), I get
another error:
Index not found: SYS_IDX_259 in statement [DROP INDEX "SYS_IDX_259" ON
"LSystem_VDisk"]

This could be yet another bug in HSQLDB, but maybe the view of StarOffice isn't
in sync with HSQLDB.
Actual Product is StarOffice 9 Update 2
Comment 1 drewjensen.inbox 2009-07-31 04:30:50 UTC
@windl 1) Do you use the quickstart for OO.o. 2) Did this problem persist after
you had rstarted your machine?



Comment 2 windl 2009-07-31 08:06:51 UTC
No, I don't use the quickstarter, and yes, the problem is still there after a
reboot of the machine. I only hope it's not related to the fact that the table
has exactly 64 entries now. ;-)
Comment 3 Frank Schönheit 2009-08-10 19:30:17 UTC
I remember having seen this myself for a table of mine (with much much more than
64 records). Unfortunately, I was never able to properly reproduce the problem:
The DB you have is corrupted, but unless we know which steps cause this
corruption, its hard to fix.

IIRC, the following fixed the problem for my particular DB:
- open the affected table in the table data view
- change the value of the PK field in the last last (read: most recently
  inserted) to something else
- change the value back
Comment 4 windl 2009-08-12 07:49:11 UTC
For some reason the PK field (integer, auto-value) is not writeable for a new
record.
Comment 5 Frank Schönheit 2009-08-12 07:55:51 UTC
That's why my suggestion to change the PK in an *existing* record.

I suspect that HSQL's internal data is slightly corrupted, which leads to not
being able to insert new PK values. However, in my experience, it helped to
"fix" this data by modifying the PK of the most recently inserted record, as
described above.
Comment 6 windl 2009-08-12 11:36:26 UTC
I played several rounds of "Changing PK, saving", stopping Base, starting Base,
changing back the PK value, etc., but the effect remains. Another strange thing
is that for this table a default value for another column that once was defined
cannot be removed. It can be removed, the design can be saved, but after
reopening the default value is there again. Very strange.
Comment 7 Frank Schönheit 2009-08-12 12:22:54 UTC
sounds like a corrupted database :(
If the DB doesn't contain confidential data, you might want to attach it here
for more thorough investigations?
Comment 8 Frank Schönheit 2009-08-13 13:41:58 UTC
> IIRC, the following fixed the problem for my particular DB:
> - open the affected table in the table data view
> - change the value of the PK field in the last last (read: most recently
>   inserted) to something else
> - change the value back

Ah, the most important information was missing here: You must not arbitrarily
change the value, but increment it by 1.

(for the record: I tried that with the corrupted database windl sent me
separately, and it worked fine - after that one could enter records, again.)

Still, there are open questions:
- How was this corrupted caused? Pretty difficult to find out
  after-the-fact
- Can we fix HSQL to at least recognize this situation, and silently
  correct it?
- Is there another manual workaround, like explicitly rebuilding the
  affected index (since I assume that the index on the auto-inc PK column
  is corrupted)?

I'll take over this issue, confirm it, and keep it for reference. Discussions
with the HSQL team are ongoing on this.
Comment 9 Marcus 2017-05-20 10:47:33 UTC
Reset assigne to the default "issues@openoffice.apache.org".