Duplicated row violating primary key
A dbimport from a 9.40.TC4 production dbexport failed with primary-key violations, and the user confirmed the source tables really did contain duplicate rows despite unique indexes. Art Kagel diagnosed index corruption and suggested oncheck -cDI, which reported "No btree item exists for data row". Advice: only oncheck can identify corrupt indexes (a sysindices/sysconstraints query just lists PK tables), and a GROUP BY unload won't resolve duplicates whose non-key data differs, so dedupe manually. Agreed fix: unload the data, clean duplicate keys in the flat file, drop/recreate the table and reload. Another poster said he saw this on 9.40 but not after moving to IDS 10.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Today , i was trying to do a dbimport from a dbexport created from a
production database (informix 9.40 tc 4) and i had a lot of errors violating
primary key .
So i checked in the production database and i've found that i really had
duplicated records in these tables violating primary key .
I could this happend ?
I've cheched the content of the rows and they are exactely the same (except
for the rowid ) .
Then i've cheched in the sysindices and i've found nunique = 1 .
Do anybody experienced the same problem ?
Best regards ,
Leonardo Perna
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: 08/03/2007
10.58
These are the detail the problem .
Leonardo =20
-----Messaggio originale-----
Da: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Per conto di leo
Inviato: venerd=EC 9 marzo 2007 12.37
A: ids@iiug.org
Oggetto: Duplicated row violating primary key [8617]
Today , i was trying to do a dbimport from a dbexport created from a
production database (informix 9.40 tc 4) and i had a lot of errors =
violating
primary key .=20
So i checked in the production database and i've found that i really had
duplicated records in these tables violating primary key .=20
I could this happend ?=20
I've cheched the content of the rows and they are exactely the same =
(except
for the rowid ) .=20
Then i've cheched in the sysindices and i've found nunique =3D 1 .=20
Do anybody experienced the same problem ?=20
Best regards ,
Leonardo Perna=20
--
No virus found in this outgoing message.=20
Checked by AVG Free Edition.=20
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =
08/03/2007
10.58=20
*************************************************************************=
***
***
Forum Note: Use "Reply" to post a response in the discussion forum.=20
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =
08/03/2007
10.58
=20
--=20
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =
08/03/2007
10.58
=20
=20
Sounds like the primary key index on your production server is corrupted. Have
you run oncheck -cDI against that table? What does it report.
Art S. Kagel
----- Original Message -----
From: Leo <ids@iiug.org>
At: 3/09 6:56:48
These are the detail the problem .
Leonardo =20
-----Messaggio originale-----
Da: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Per conto di leo
Inviato: venerd=EC 9 marzo 2007 12.37
A: ids@iiug.org
Oggetto: Duplicated row violating primary key [8617]
Today , i was trying to do a dbimport from a dbexport created from a
production database (informix 9.40 tc 4) and i had a lot of errors =
violating
primary key .=20
So i checked in the production database and i've found that i really had
duplicated records in these tables violating primary key .=20
I could this happend ?=20
I've cheched the content of the rows and they are exactely the same =
(except
for the rowid ) .=20
Then i've cheched in the sysindices and i've found nunique =3D 1 .=20
Do anybody experienced the same problem ?=20
Best regards ,
Leonardo Perna=20
--
No virus found in this outgoing message.=20
Checked by AVG Free Edition.=20
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =
08/03/2007
10.58=20
*************************************************************************=
***
***
Forum Note: Use "Reply" to post a response in the discussion forum.=20
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =
08/03/2007
10.58
=20
--=20
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =
08/03/2007
10.58
=20
=20
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Leonardo,
I've already seen that when i used to use informix 9.40 it's really happens.
Since i migrated to IDS 10 in january 2006, i don't see it.
Good luck,
Celso Coimbra
-----Mensagem original-----
De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Em nome de leo
Enviada em: sexta-feira, 9 de março de 2007 08:37
Para: ids@iiug.org
Assunto: Duplicated row violating primary key [8617]
Today , i was trying to do a dbimport from a dbexport created from a
production database (informix 9.40 tc 4) and i had a lot of errors violating
primary key .
So i checked in the production database and i've found that i really had
duplicated records in these tables violating primary key .
I could this happend ?
I've cheched the content of the rows and they are exactely the same (except
for the rowid ) .
Then i've cheched in the sysindices and i've found nunique = 1 .
Do anybody experienced the same problem ?
Best regards ,
Leonardo Perna
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: 08/03/2007
10.58
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Art ,
You're right as alwais .
I already did an i have index corruption .
It reports "ERROR:No btree item exists for data row"
Two questions :
1) I think i will export data (grouping by primary key) , drop the table =
and
recreate it .
Do you have a faster solution for that ?
2) To find all the table corrupted without serching in the output of =
onstat-cI , is this query correct ?
select tabname , idxname from sysindices,systables where
sysindices.tabid=3Dsystables.tabid and nunique>0 and idxname in (select nvl (idxname, constrname) from sysconstraints where =
constrtype=3D'P')
Thanks and best regards ,
Leonardo
-----Messaggio originale-----
Da: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Per conto di ART
KAGEL, BLOOMBERG/ 731 LEXIN
Inviato: venerd=EC 9 marzo 2007 13.32
A: ids@iiug.org
Oggetto: Re: R: Duplicated row violating primary key [8619]
Sounds like the primary key index on your production server is =
corrupted.
Have you run oncheck -cDI against that table? What does it report.=20
Art S. Kagel=20
----- Original Message -----
From: Leo <ids@iiug.org>
At: 3/09 6:56:48=20
These are the detail the problem .=20
Leonardo =3D20=20
-----Messaggio originale-----
Da: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Per conto di leo
Inviato: venerd=3DEC 9 marzo 2007 12.37
A: ids@iiug.org
Oggetto: Duplicated row violating primary key [8617]=20
Today , i was trying to do a dbimport from a dbexport created from a
production database (informix 9.40 tc 4) and i had a lot of errors =3D
violating primary key .=3D20 So i checked in the production database and =
i've
found that i really had duplicated records in these tables violating =
primary
key .=3D20 I could this happend ?=3D20 I've cheched the content of the =
rows and
they are exactely the same =3D (except for the rowid ) .=3D20 Then i've =
cheched
in the sysindices and i've found nunique =3D3D 1 .=3D20 Do anybody =
experienced
the same problem ?=3D20=20
Best regards ,
Leonardo Perna=3D20=20
--
No virus found in this outgoing message.=3D20 Checked by AVG Free =
Edition.=3D20
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =3D
08/03/2007
10.58=3D20=20
*************************************************************************=
=3D
***
***
Forum Note: Use "Reply" to post a response in the discussion forum.=3D20 =
--
No virus found in this incoming message.=20
Checked by AVG Free Edition.=20
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =3D
08/03/2007
10.58
=3D20=20
--=3D20
No virus found in this outgoing message.=20
Checked by AVG Free Edition.=20
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =3D
08/03/2007
10.58
=3D20
=3D20=20
*************************************************************************=
***
***
Forum Note: Use "Reply" to post a response in the discussion forum.=20
*************************************************************************=
***
***=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
--=20
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =
08/03/2007
10.58
=20
--=20
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =
08/03/2007
10.58
=20
On 1) If you have multiple rows with the same primary key, which will you keep
if the non-key data is not identical? The GROUP BY will not catch those, you'll
have to filter the records from the export file manually or via some code. The
query in 2) will find all of the tables with a primary key, but it will not
tell you what's corrupt. Only oncheck -cDI will do that.
Art S. Kagel
----- Original Message -----
From: Leo <ids@iiug.org>
At: 3/09 8:02:40
Art ,
You're right as alwais .
I already did an i have index corruption .
It reports "ERROR:No btree item exists for data row"
Two questions :
1) I think i will export data (grouping by primary key) , drop the table =
and
recreate it .
Do you have a faster solution for that ?
2) To find all the table corrupted without serching in the output of =
onstat-cI , is this query correct ?
select tabname , idxname from sysindices,systables where
sysindices.tabid=3Dsystables.tabid and nunique>0 and idxname in (select nvl (idxname, constrname) from sysconstraints where =
constrtype=3D'P')
Thanks and best regards ,
Leonardo
-----Messaggio originale-----
Da: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Per conto di ART
KAGEL, BLOOMBERG/ 731 LEXIN
Inviato: venerd=EC 9 marzo 2007 13.32
A: ids@iiug.org
Oggetto: Re: R: Duplicated row violating primary key [8619]
Sounds like the primary key index on your production server is =
corrupted.
Have you run oncheck -cDI against that table? What does it report.=20
Art S. Kagel=20
----- Original Message -----
From: Leo <ids@iiug.org>
At: 3/09 6:56:48=20
These are the detail the problem .=20
Leonardo =3D20=20
-----Messaggio originale-----
Da: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Per conto di leo
Inviato: venerd=3DEC 9 marzo 2007 12.37
A: ids@iiug.org
Oggetto: Duplicated row violating primary key [8617]=20
Today , i was trying to do a dbimport from a dbexport created from a
production database (informix 9.40 tc 4) and i had a lot of errors =3D
violating primary key .=3D20 So i checked in the production database and =
i've
found that i really had duplicated records in these tables violating =
primary
key .=3D20 I could this happend ?=3D20 I've cheched the content of the =
rows and
they are exactely the same =3D (except for the rowid ) .=3D20 Then i've =
cheched
in the sysindices and i've found nunique =3D3D 1 .=3D20 Do anybody =
experienced
the same problem ?=3D20=20
Best regards ,
Leonardo Perna=3D20=20
--
No virus found in this outgoing message.=3D20 Checked by AVG Free =
Edition.=3D20
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =3D
08/03/2007
10.58=3D20=20
*************************************************************************=
=3D
***
***
Forum Note: Use "Reply" to post a response in the discussion forum.=3D20 =
--
No virus found in this incoming message.=20
Checked by AVG Free Edition.=20
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =3D
08/03/2007
10.58
=3D20=20
--=3D20
No virus found in this outgoing message.=20
Checked by AVG Free Edition.=20
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =3D
08/03/2007
10.58
=3D20
=3D20=20
*************************************************************************=
***
***
Forum Note: Use "Reply" to post a response in the discussion forum.=20
*************************************************************************=
***
***=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
--=20
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =
08/03/2007
10.58
=20
--=20
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =
08/03/2007
10.58
=20
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
when I've found this, i've unloaded data, change the duplicate key in flat
file, recreate the table and reload the data.
Celso Coimbra
.
-----Mensagem original-----
De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Em nome de ART
KAGEL, BLOOMBERG/ 731 LEXIN
Enviada em: sexta-feira, 9 de março de 2007 10:14
Para: ids@iiug.org
Assunto: Re: R: R: Duplicated row violating primary key [8623]
On 1) If you have multiple rows with the same primary key, which will you keep
if the non-key data is not identical? The GROUP BY will not catch those,
you'll
have to filter the records from the export file manually or via some code. The
query in 2) will find all of the tables with a primary key, but it will not
tell you what's corrupt. Only oncheck -cDI will do that.
Art S. Kagel
----- Original Message -----
From: Leo <ids@iiug.org>
At: 3/09 8:02:40
Art ,
You're right as alwais .
I already did an i have index corruption .
It reports "ERROR:No btree item exists for data row"
Two questions :
1) I think i will export data (grouping by primary key) , drop the table =
and
recreate it .
Do you have a faster solution for that ?
2) To find all the table corrupted without serching in the output of =
onstat-cI , is this query correct ?
select tabname , idxname from sysindices,systables where
sysindices.tabid=3Dsystables.tabid and nunique>0 and idxname in (select nvl (idxname, constrname) from sysconstraints where =
constrtype=3D'P')
Thanks and best regards ,
Leonardo
-----Messaggio originale-----
Da: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Per conto di ART
KAGEL, BLOOMBERG/ 731 LEXIN
Inviato: venerd=EC 9 marzo 2007 13.32
A: ids@iiug.org
Oggetto: Re: R: Duplicated row violating primary key [8619]
Sounds like the primary key index on your production server is =
corrupted.
Have you run oncheck -cDI against that table? What does it report.=20
Art S. Kagel=20
----- Original Message -----
From: Leo <ids@iiug.org>
At: 3/09 6:56:48=20
These are the detail the problem .=20
Leonardo =3D20=20
-----Messaggio originale-----
Da: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Per conto di leo
Inviato: venerd=3DEC 9 marzo 2007 12.37
A: ids@iiug.org
Oggetto: Duplicated row violating primary key [8617]=20
Today , i was trying to do a dbimport from a dbexport created from a
production database (informix 9.40 tc 4) and i had a lot of errors =3D
violating primary key .=3D20 So i checked in the production database and =
i've
found that i really had duplicated records in these tables violating =
primary
key .=3D20 I could this happend ?=3D20 I've cheched the content of the =
rows and
they are exactely the same =3D (except for the rowid ) .=3D20 Then i've =
cheched
in the sysindices and i've found nunique =3D3D 1 .=3D20 Do anybody =
experienced
the same problem ?=3D20=20
Best regards ,
Leonardo Perna=3D20=20
--
No virus found in this outgoing message.=3D20 Checked by AVG Free =
Edition.=3D20
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =3D
08/03/2007
10.58=3D20=20
*************************************************************************=
=3D
***
***
Forum Note: Use "Reply" to post a response in the discussion forum.=3D20 =
--
No virus found in this incoming message.=20
Checked by AVG Free Edition.=20
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =3D
08/03/2007
10.58
=3D20=20
--=3D20
No virus found in this outgoing message.=20
Checked by AVG Free Edition.=20
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =3D
08/03/2007
10.58
=3D20
=3D20=20
*************************************************************************=
***
***
Forum Note: Use "Reply" to post a response in the discussion forum.=20
*************************************************************************=
***
***=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
--=20
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =
08/03/2007
10.58
=20
--=20
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =
08/03/2007
10.58
=20
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Sounds like a plan. Hope everything works out.
Art S. Kagel
----- Original Message -----
From: Celso Cabral Coimbra <ids@iiug.org>
At: 3/09 9:09:26
when I've found this, i've unloaded data, change the duplicate key in flat
file, recreate the table and reload the data.
Celso Coimbra
..
-----Mensagem original-----
De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Em nome de ART
KAGEL, BLOOMBERG/ 731 LEXIN
Enviada em: sexta-feira, 9 de março de 2007 10:14
Para: ids@iiug.org
Assunto: Re: R: R: Duplicated row violating primary key [8623]
On 1) If you have multiple rows with the same primary key, which will you keep
if the non-key data is not identical? The GROUP BY will not catch those,
you'll
have to filter the records from the export file manually or via some code. The
query in 2) will find all of the tables with a primary key, but it will not
tell you what's corrupt. Only oncheck -cDI will do that.
Art S. Kagel
----- Original Message -----
From: Leo <ids@iiug.org>
At: 3/09 8:02:40
Art ,
You're right as alwais .
I already did an i have index corruption .
It reports "ERROR:No btree item exists for data row"
Two questions :
1) I think i will export data (grouping by primary key) , drop the table =
and
recreate it .
Do you have a faster solution for that ?
2) To find all the table corrupted without serching in the output of =
onstat-cI , is this query correct ?
select tabname , idxname from sysindices,systables where
sysindices.tabid=3Dsystables.tabid and nunique>0 and idxname in (select nvl (idxname, constrname) from sysconstraints where =
constrtype=3D'P')
Thanks and best regards ,
Leonardo
-----Messaggio originale-----
Da: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Per conto di ART
KAGEL, BLOOMBERG/ 731 LEXIN
Inviato: venerd=EC 9 marzo 2007 13.32
A: ids@iiug.org
Oggetto: Re: R: Duplicated row violating primary key [8619]
Sounds like the primary key index on your production server is =
corrupted.
Have you run oncheck -cDI against that table? What does it report.=20
Art S. Kagel=20
----- Original Message -----
From: Leo <ids@iiug.org>
At: 3/09 6:56:48=20
These are the detail the problem .=20
Leonardo =3D20=20
-----Messaggio originale-----
Da: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Per conto di leo
Inviato: venerd=3DEC 9 marzo 2007 12.37
A: ids@iiug.org
Oggetto: Duplicated row violating primary key [8617]=20
Today , i was trying to do a dbimport from a dbexport created from a
production database (informix 9.40 tc 4) and i had a lot of errors =3D
violating primary key .=3D20 So i checked in the production database and =
i've
found that i really had duplicated records in these tables violating =
primary
key .=3D20 I could this happend ?=3D20 I've cheched the content of the =
rows and
they are exactely the same =3D (except for the rowid ) .=3D20 Then i've =
cheched
in the sysindices and i've found nunique =3D3D 1 .=3D20 Do anybody =
experienced
the same problem ?=3D20=20
Best regards ,
Leonardo Perna=3D20=20
--
No virus found in this outgoing message.=3D20 Checked by AVG Free =
Edition.=3D20
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =3D
08/03/2007
10.58=3D20=20
*************************************************************************=
=3D
***
***
Forum Note: Use "Reply" to post a response in the discussion forum.=3D20 =
--
No virus found in this incoming message.=20
Checked by AVG Free Edition.=20
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =3D
08/03/2007
10.58
=3D20=20
--=3D20
No virus found in this outgoing message.=20
Checked by AVG Free Edition.=20
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =3D
08/03/2007
10.58
=3D20
=3D20=20
*************************************************************************=
***
***
Forum Note: Use "Reply" to post a response in the discussion forum.=20
*************************************************************************=
***
***=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
--=20
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =
08/03/2007
10.58
=20
--=20
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.8/714 - Release Date: =
08/03/2007
10.58
=20
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.