Apache OpenOffice (AOO) Bugzilla – Issue 103682
HSQLDB Index lost (Violation of unique constraint SYS_PK_260...)
Last modified: 2020-05-11 21:27:07 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
@windl 1) Do you use the quickstart for OO.o. 2) Did this problem persist after you had rstarted your machine?
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. ;-)
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
For some reason the PK field (integer, auto-value) is not writeable for a new record.
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.
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.
sounds like a corrupted database :( If the DB doesn't contain confidential data, you might want to attach it here for more thorough investigations?
> 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.
Reset assigne to the default "issues@openoffice.apache.org".
Close this old issue If this occurs again with last build, please reopen and provide a sample ODB to reproduce.