Issue 104901 - After changing default values in tables the Primarykey Autoincrement starts at 0
Summary: After changing default values in tables the Primarykey Autoincrement starts at 0
Status: CLOSED FIXED
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 3.1.1
Hardware: PC Windows XP
: P2 Trivial (vote)
Target Milestone: OOo 3.2
Assignee: marc.neumann
QA Contact: issues@dba
URL:
Keywords:
Depends on:
Blocks: 99999
  Show dependency tree
 
Reported: 2009-09-08 20:06 UTC by ud
Modified: 2013-07-06 06:52 UTC (History)
5 users (show)

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


Attachments
Example (3.39 KB, application/vnd.sun.xml.base)
2009-09-08 20:08 UTC, ud
no flags Details
Patch (2.19 KB, patch)
2009-09-17 08:03 UTC, ocke.janssen
no flags Details | Diff

Note You need to log in before you can comment on or make changes to this issue.
Description ud 2009-09-08 20:06:44 UTC
After changing default values in tables, the Primarykeyfield with an
Autoincrement starts at 0. I used the hsqldb on WinXp.

Please open attached database
-open "Tabelle1" in design view
-set default value "dtNumber" to 1
-set default value "dtOld" to no
-save "Tabelle1" and close

-open "Tabelle1" by doubleclick
-insert a new dataset, and leave the row by using the TAB-key
=> Error message
=> klick Ok
- leave the row by using the TAB-key
=> Error message
=> klick Ok

After 6 Times to leave the row by TAB-key and klick OK, the
Autoincrement is back to 6 and can work proper.

But if you change

-set default value "dtOld" to yes,
 the game will start from beginn.

But if there where more datasets (e.g. 500) in the table it is no
fun, to get the autoincrement to work again. It could be also, to 
get wrong data.
Comment 1 ud 2009-09-08 20:08:57 UTC
Created attachment 64623 [details]
Example
Comment 2 Frank Schönheit 2009-09-09 08:54:25 UTC
confirming, assigning, promoting to P2 - it effectively corrupts the DB -,
targeting to OOo 3.2.

@ud: Thanks for this excellent bug description

@oj: This sounds like a combination of an OOo and HSQL bug to me. First,
changing the column default should not touch the HSQL table at all - the default
value is a mere client-side property, isn't it? That's the OOo part. Second,
even if we tell HSQL to change a column definition, it shouldn't implicitly
reset the auto-increment value sequence. Please investigate whether you find the
reason/fix for this in the HSQL sources.
Comment 3 ocke.janssen 2009-09-10 09:53:23 UTC
As work around you execute this sql statement in the menu Tools->SQL

ALTER TABLE "tabelle1" ALTER COLUMN "id" RESTART WITH <value you need>

I sent the developer of hsqldb a patch, which he has to check first before
integrating it into OOo.
Comment 4 mhatheoo 2009-09-10 14:28:57 UTC
==>fs

since a couple of days I think about fileing an issue about, that OO.o is
tempering  HSQL-data-files. seeing this issue, I send you these infos, with the
special remark, that you need to find out yourself, what had happend:  

BORG is an calendar-application using the HSQL-engine.
Trying to use these data-files reading by using the OO-build-in HSQL-engine
worked for a couple of time, but final the data-structur and the skript-file
where corrupted and the data where lost - you can find my complaint in the
BORG-forum. Right now I am not sure about the reasons, but the guess it is more
a problem of OO.o.
Please have a look.

Martin
Comment 5 ocke.janssen 2009-09-11 12:54:32 UTC
Fixed in cws dba32h

-Open any new hsqldb
-Tools->SQL
And execute the following

CREATE CACHED TABLE "Tabelle1"("id" INTEGER GENERATED BY DEFAULT AS
IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"dtText" VARCHAR(50) NOT
NULL,"dtNumber" DECIMAL(15),"dtDatum" DATE,"dtOld" BOOLEAN);
INSERT INTO "Tabelle1" ( "dtOld","dtText") VALUES ( 0,'a');
INSERT INTO "Tabelle1" ( "dtOld","dtText") VALUES ( 0,'b');
INSERT INTO "Tabelle1" ( "dtOld","dtText") VALUES ( 0,'c');
INSERT INTO "Tabelle1" ( "dtOld","dtText") VALUES ( 0,'d');
ALTER TABLE "Tabelle1" ALTER COLUMN "id" INTEGER NOT NULL IDENTITY;
INSERT INTO "Tabelle1" ( "dtOld","dtText") VALUES ( 0,'d'); 

==> Violation of unique constraint SYS_PK_47: duplicate value(s) for column(s) "id"

Now the error message should appear anymore.

Comment 6 ocke.janssen 2009-09-11 12:56:11 UTC
The sources for hsqldb have been patched in this cws.
Comment 7 eremmel 2009-09-16 21:12:04 UTC
The error does not only occur when changing the default value, but also when
changing the format of a field, the type of a field, etc.

An other workaround is to execute the statement:
ALTER TABLE "bookmark" ALTER COLUMN ID INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 100, INCREMENT BY 2)
This workaround does not work with out ', INCREMENT BY n' or with ',INCREMENT BY 1'

After the above statement and the one mentioned above the error does not occure
any more.

Comment 8 ocke.janssen 2009-09-17 08:03:33 UTC
Created attachment 64806 [details]
Patch
Comment 9 ocke.janssen 2009-09-28 11:30:49 UTC
Please verify. Have a look at the SQL statement I pasted above to reproduce it.
Comment 10 christoph.lukasiak 2009-10-07 16:07:54 UTC
clu->msc: now i get an 'syntax error in sql expression' error message after
executing the query => not sure if it is really fixed with this
Comment 11 marc.neumann 2009-10-12 10:11:08 UTC
no problem when check with the description from oj Fri Sep 11 11:54:32 +0000
2009. In DEV300_m60 an error occurr in the CWS not.

So it's fixed and I set it to verified
Comment 12 drewjensen.inbox 2009-10-25 20:55:23 UTC
Checked w/ OOO320m_2, Ubuntu 9.04

Closing this issue

Comment 13 Andrea Pescetti 2013-07-06 06:52:26 UTC
For the record, the fix is now included in the fix for bug 121754.