RE: Err 239 on Insert to serial column

A 239 error is a duplicate key on a unique index. When a serial data type is used, a unique index is created on the column. As you know, when you insert the value 0 into a serial column, it is supposed to take the "next available" value and use it. As you also know, when you insert a value other than 0 into a serial column, that value is used. If you force a value other than the "next available" value, then later change your habits to use 0, you will eventually "catch up" to the value that you forced and the result is a duplicate key. The moral of the story is this: if you are using a serial datatype, always insert the value 0 or have logic to react to the possibility of a duplicate key. -----Original Message----- From: Stuart Worrall [mailto:Stuart@sworrall.freeserve.co.uk] Sent: Wednesday, October 13, 1999 6:44 PM To: informix-list@iiug.org Subject: Err 239 on Insert to serial column Hello, I have recently been presented with a problem whereby the error 239 is generated from dbaccess when trying to insert a row. ie INSERT INTO XXXXX VALUES (0,"DDF",1,"etc") where the first column is the serial. There is a unique index on the column which was dropped and re-created with no success. After further investigation the serial value was found to be 70650. I then used the following statement to force the insert.. INSERT INTO XXXXX VALUES (70651,"DDF",1,"etc") which worked. After this, using the value 0 to insert into the serial field worked and I was able to put in the values of 70652, 70653 etc. Has anybody seen this problem anywhere else? The system is Dynamic Server 7.3.UC8 running on a HP Thanks in advance Stu Worrall

Hi Can I comment on the last message When forcing a value into a serial column say 100, the new value when using 0 will always be 101. e.g. starting on a new table called tableX SQL VALUES ON DATABASE INSERT INTO tableX VALUES (0,"test") 1,"test" INSERT INTO tableX VALUES (0,"test") 2,"test" INSERT INTO tableX VALUES (0,"test") 3,"test" INSERT INTO tableX VALUES (0,"test") 4,"test" INSERT INTO tableX VALUES (0,"test") 5,"test" INSERT INTO tableX VALUES (100,"test") 100,"test" INSERT INTO tableX VALUES (0,"test") 101,"test" INSERT INTO tableX VALUES (0,"test") 102,"test" INSERT INTO tableX VALUES (0,"test") 103,"test" This is my understanding of serial columns. Please mail me if I have it wrong. Thanks Stu Worrall e-mail: stuart@NON.SPAMsworrall.freeserve.co.uk In article <7u4j9j$dig$1@news.xmission.com>, William Raper <WilliamR@catmktg.com> writes > >A 239 error is a duplicate key on a unique index. > >When a serial data type is used, a unique index is created on the column. > >As you know, when you insert the value 0 into a serial column, it is >supposed to take the "next available" value and use it. >As you also know, when you insert a value other than 0 into a serial column, >that value is used. > >If you force a value other than the "next available" value, then later >change your habits to use 0, you will eventually "catch up" to the value >that you forced and the result is a duplicate key. > >The moral of the story is this: if you are using a serial datatype, always >insert the value 0 or have logic to react to the possibility of a duplicate >key. > >-----Original Message----- >From: Stuart Worrall [mailto:Stuart@sworrall.freeserve.co.uk] >Sent: Wednesday, October 13, 1999 6:44 PM >To: informix-list@iiug.org >Subject: Err 239 on Insert to serial column > > >Hello, > >I have recently been presented with a problem whereby the error 239 is >generated from dbaccess when trying to insert a row. ie INSERT INTO XXXXX >VALUES (0,"DDF",1,"etc") where the first column is the serial. > >There is a unique index on the column which was dropped and re-created with >no success. > >After further investigation the serial value was found to be 70650. I then >used the following statement to force the insert.. INSERT INTO XXXXX >VALUES (70651,"DDF",1,"etc") which worked. > >After this, using the value 0 to insert into the serial field worked and I >was able to put in the values of 70652, 70653 etc. > >Has anybody seen this problem anywhere else? > >The system is Dynamic Server 7.3.UC8 running on a HP > >Thanks in advance > >Stu Worrall > > > Stuart Worrall Lintel Software Consultancy Ltd e-mail: stuart@NO.SPAMlintel.co.uk web: www.lintel.co.uk Tel: 01244 316297 Fax: 01244 400171

Stuart Worrall wrote: > > Hi > > Can I comment on the last message > > When forcing a value into a serial column say 100, the new value when > using 0 will always be 101. Unless the "current" serial value is already greater than 101 or you insert (LONGMAX) which will reset the current serial and the smallest hole will be filled, yes. > e.g. starting on a new table called tableX > > SQL VALUES ON > DATABASE > > INSERT INTO tableX VALUES (0,"test") 1,"test" > INSERT INTO tableX VALUES (0,"test") 2,"test" > INSERT INTO tableX VALUES (0,"test") 3,"test" > INSERT INTO tableX VALUES (0,"test") 4,"test" > INSERT INTO tableX VALUES (0,"test") 5,"test"> > INSERT INTO tableX VALUES (100,"test") 100,"test"> > INSERT INTO tableX VALUES (0,"test") 101,"test" > INSERT INTO tableX VALUES (0,"test") 102,"test" > INSERT INTO tableX VALUES (0,"test") 103,"test"> > This is my understanding of serial columns. Please mail me if I have it > wrong. [SNIP] So the following is true: CREATE TABLE tablex( a serial, b char(10)); CREATE UNIQUE INDEX u_tablex ON tablex( a ); a next a --- ------ INSERT INTO tableX VALUES (0, "test"); 1 2 INSERT INTO tableX VALUES (0, "test"); 2 3 INSERT INTO tableX VALUES (4, "test"); 4 5 INSERT INTO talbeX VALUES (10000, "test"); 10000 10001 INSERT INTO tableX VALUES (0, "test"); 10001 10002 INSERT INTO tableX VALUES (100, "test"); 100 10002 INSERT INTO tableX VALUES (2147483647, "reset") 2147483647 1 INSERT INTO tableX VALUES (0, "test"); 4 5 Also in testing this schenario I have discover at least one situation that will cause the behavior that Stuart originally described. I created the table without the UNIQUE index and after the insert of LONGMAX above the last insert inserted a duplicate value of "1" as indicated by the "next a" column above and without the index to prevent it. So I deleted the pair of dups and added the index and inserted the last row, with the zero for column a, again and got the "duplicate value for unique key" message. Like Stuart inserting an explicit row with the expected value (in that case it truly was "1" since I had deleted the rows with 1 in them) fixed the problem and I was able to continue inserting with zero. Stuart, does this match something like what you experienced? If so the mystery is solved. Art S. Kagel

Im afraid that the table in question never has this much manipulation on it. The table is simply a spool request holder for print jobs. The 70650 value is the max amount of records that have ever been entered since it was created. It is however cleared of certain records once in a while. I tried to reproduce the error that you highlighted from your testing. I think that this is the same error that Informix have pointed out to me in an attempt to explain the error. The error is 118970 INSERTING TWO ROWS CONCURRENTLY WITH DELETED PRIMARY KEY LEADS TO ERROR -239 etc Description: In a database with logging an insert of two rows currently leads to an error -239 if these values have been used and deleted in a primary key column. The workaround is to turn off logging or alter the table to row locking. I tried this on the test table you suggested and setting the locking mode to row does indeed fix the problem. As the serial column on our table is only ever referenced with the zero, I cant see how this problem could affect it. Unless of course it is a similar problem which has not yet been recognised by Informix Thanks Stu Worrall Art S. Kagel <kagel@bloomberg.net> wrote in message news:38073302.3D7C9F66@bloomberg.net... > Stuart Worrall wrote: > > > > Hi > > > > Can I comment on the last message > > > > When forcing a value into a serial column say 100, the new value when > > using 0 will always be 101. > > Unless the "current" serial value is already greater than 101 or you > insert (LONGMAX) which will reset the current serial and the smallest > hole will be filled, yes. > > > e.g. starting on a new table called tableX > > > > SQL VALUES ON > > DATABASE > > > > INSERT INTO tableX VALUES (0,"test") 1,"test" > > INSERT INTO tableX VALUES (0,"test") 2,"test" > > INSERT INTO tableX VALUES (0,"test") 3,"test" > > INSERT INTO tableX VALUES (0,"test") 4,"test" > > INSERT INTO tableX VALUES (0,"test") 5,"test"> > > > INSERT INTO tableX VALUES (100,"test") 100,"test"> > > > INSERT INTO tableX VALUES (0,"test") 101,"test" > > INSERT INTO tableX VALUES (0,"test") 102,"test" > > INSERT INTO tableX VALUES (0,"test") 103,"test"> > > > This is my understanding of serial columns. Please mail me if I have it > > wrong. > [SNIP] > > So the following is true: > > CREATE TABLE tablex( a serial, b char(10)); > CREATE UNIQUE INDEX u_tablex ON tablex( a );> a next a > --- ------ > INSERT INTO tableX VALUES (0, "test"); 1 2 > INSERT INTO tableX VALUES (0, "test"); 2 3 > INSERT INTO tableX VALUES (4, "test"); 4 5 > INSERT INTO talbeX VALUES (10000, "test"); 10000 10001 > INSERT INTO tableX VALUES (0, "test"); 10001 10002 > INSERT INTO tableX VALUES (100, "test"); 100 10002 > INSERT INTO tableX VALUES (2147483647, "reset") 2147483647 1 > INSERT INTO tableX VALUES (0, "test"); 4 5> > Also in testing this schenario I have discover at least one situation that > will cause the behavior that Stuart originally described. I created the > table without the UNIQUE index and after the insert of LONGMAX above the > last insert inserted a duplicate value of "1" as indicated by the "next a" > column above and without the index to prevent it. So I deleted the pair > of dups and added the index and inserted the last row, with the zero for > column a, again and got the "duplicate value for unique key" message. > Like Stuart inserting an explicit row with the expected value (in that > case it truly was "1" since I had deleted the rows with 1 in them) fixed > the problem and I was able to continue inserting with zero. > > Stuart, does this match something like what you experienced? If so the > mystery is solved. > > Art S. Kagel