insert duplicate values into unique index column
An Informix SE 7.23 user couldn't insert rows into table rs_scat (unique composite index on rsc_shl, rsc_catagory): every insert for rsc_shl 39125 failed with error 239 / ISAM 100 duplicate-key, even though selects showed no such rows. After confirming absence of the rows via a SELECT INTO TEMP (unindexed, forcing a sequential scan), Jonathan Leffler suggested running secheck on the table's .dat file. It reported 17 bad data record pointers, i.e. a corrupt index. Answering yes to secheck's prompts dropped and rebuilt the index and fixed free lists, after which the inserts worked.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
My lack of database knowledge is going to be evidident with this
question, but I can't figure out how to get this to work. I'm using
Informix SE 7.23 and I have a table rs_scat with two unique index
columns, rsc_shl and rsc_catagory. The 4GL program that updated the
table screwed up because there should have been a 39125 value for
rsc_shl and as you can see from the following select statement it's
missing
select * from rs_scat
where rsc_shl between 39124 and 39126
rsc_shl rsc_catagory rsc_amount rsc_retail
39124 01 203.51 277.36
39124 02 68.92 97.50
39124 03 136.92 468.00
39124 08 113.64 160.83
39124 09 29.74 59.47
39124 11 549.43 584.50
39126 01 203.91 270.75
39126 02 106.14 147.53
39126 03 21.70 134.00
39126 08 125.21 163.50
39126 09 9.87 19.74
39126 10 214.39 255.26
39126 11 162.62 173.00
I thought I could do the following to insert the missing data for the
39125 rsc_shl number
insert into rs_scat values (39125, "01", 217.14, 282.29)
and then do a similar insert for each catagory code (rsc_catagory) 01
to 11
the problem is dbaccess gives an error:
239: Could not insert new row - duplicate value in a UNIQUE INDEX
column.
100: ISAM error: duplicate value for a record with unique key.
I guess the error is because rsc_catagory(which is a unique index
column) has code "01" in it already, but how does the 4GL program
insert the duplicate data in rsc_shl and rsc_catagory that my select
statement above shows? How can I insert values 01 to 11 in
rsc_catagory and number 39125 in rsc_shl for each of those values?
Can you send the schema information for the table:
dbschema -d <database_name> -t <table_name> Or at least the indexes.
$ dbschema -d <database> -t rs_scat
DBSCHEMA Schema Utility INFORMIX-SQL Version 7.23.UC13
Copyright (C) Informix Software, Inc., 1984-1997
{ TABLE "ss".rs_scat row size = 19 number of columns = 4 index size =
18 }
create table "ss".rs_scat
(
rsc_shl integer,
rsc_catagory char(4),
rsc_amount decimal(10,2),
rsc_retail decimal(8,2)
);
revoke all on "ss".rs_scat from "public";
create unique index "ss".rs_scat_p on "ss".rs_scat
(rsc_shl,rsc_catagory);
C Douglas Said >and then do a similar insert for each catagory code (rsc_catagory) 01 to 11 Don't you mean 02 to 11 since you have already inserted the 01 record?
>Don't you mean 02 to 11 since you have already inserted the 01 record? yes
So it sounds like this should work based on a email I got, but I still
get the error.
First I double checked to make sure no records were there
select * from rs_scat
where rsc_shl = 39125
results
rsc_shl rsc_catagory rsc_amount rsc_retail
no rows found
Then I tried the insert again
insert into rs_scat values (39125, "01", 217.14, 282.29)
and I get the error
239: Could not insert new row - duplicate value in a UNIQUE INDEX
column.
100: ISAM error: duplicate value for a record with unique key.
It was suggested to check the index, but I'm unsure of the exact sytax
for secheck and I can query all the other data in the table just fine
so it doesn't seem like the index would be corrupt.
C Douglas wrote:
> So it sounds like this should work based on a email I got, but I still
> get the error.
>
> First I double checked to make sure no records were there
>
> select * from rs_scat
> where rsc_shl = 39125>
> results
>
> rsc_shl rsc_catagory rsc_amount rsc_retail
>
> no rows found
>
> Then I tried the insert again
> insert into rs_scat values (39125, "01", 217.14, 282.29)>
> and I get the error
>
> 239: Could not insert new row - duplicate value in a UNIQUE INDEX
> column.
> 100: ISAM error: duplicate value for a record with unique key.>
> It was suggested to check the index, but I'm unsure of the exact sytax
> for secheck and I can query all the other data in the table just fine
> so it doesn't seem like the index would be corrupt.
You could try:
SELECT * FROM rs_cat INTO TEMP t;
SELECT * FROM t WHERE rsc_shl = 39125;
Since t has no index, you are guaranteed a sequential scan.
For secheck: you can run 'secheck -:' and it should give you usage info.
You could also run: secheck yourdbs.dbs/rs_sc*.dat
You could add a -n option if you don't want it to fix anything - just
report the problems.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
>SELECT * FROM rs_cat INTO TEMP t;
>SELECT * FROM t WHERE rsc_shl = 39125;
This found no rows
>For secheck: you can run 'secheck -:' and it should give you usage info.
'secheck -:' gave an illegal option error
>You could also run: secheck yourdbs.dbs/rs_sc*.dat
This is getting to the problem, here is the output:
$ secheck rs_sc*.dat
BCHECK C-ISAM B-tree Checker version 7.23.UC13
Copyright (C) 1981-1997 Informix Software, Inc.
Software Serial Number
C-ISAM File: rs_sc00152.dat
Checking dictionary and file sizes.
Index file node size = 1024
Current C-ISAM index file node size = 1024
Checking data file records.
Checking indexes and key descriptions.
Index 1 = unique key
0 index node(s) used -- 1 index b-tree level(s) used
Index 2 = unique key (0,4,2) (4,4,0)
2512 index node(s) used -- 3 index b-tree level(s) used
ERROR: 17 bad data record pointer(s)
Delete index ?
I've never removed and recreated an index before, will this cause any
data loss? What is the syntax to remove and recreate the index?
Based on the index name from what dbschema reports below would I just
do this:
'drop index rs_scat_p'
and then to create
'create unique index "ss".rs_scat_p on "ss".rs_scat
(rsc_shl,rsc_catagory);'
or do I just answer yes to the secheck prompt to delete the index and
will it also recreate it?
$ dbschema -d <database> -t rs_scat
DBSCHEMA Schema Utility INFORMIX-SQL Version 7.23.UC13
Copyright (C) Informix Software, Inc., 1984-1997
{ TABLE "ss".rs_scat row size = 19 number of columns = 4 index size =
18 }
create table "ss".rs_scat
(
rsc_shl integer,
rsc_catagory char(4),
rsc_amount decimal(10,2),
rsc_retail decimal(8,2)
);
revoke all on "ss".rs_scat from "public";
create unique index "ss".rs_scat_p on "ss".rs_scat
(rsc_shl,rsc_catagory);
I answered yes to all the secheck prompts and the index was dropped and recreated. This fixed the problem and now I was able to insert the missing data. Thanks for the help. ERROR: 17 bad data record pointer(s) Delete index ? y Remake index ? y Checking data record and index node free lists. ERROR: 17 missing data record pointer(s) Fix data record free list ? y ERROR: 2512 missing index node pointer(s) Fix index node free list ? y Recreating index node free list. Recreating data record free list. Recreating index 2. 3 index node(s) used, 2512 free -- 168892 data record(s) used, 68 free