Why 'Duplicate key on insert detected' ?
nikosv
key on 'c_ylikoy' column which acts as PK (don't ask why they
implemented it this way.....)
Column Name Type Length Nulls Defaults Seq Per Value
c_ylikoy char 10 no no 1
unique
When I try to insert a row with a pk value that does not exist in the
table, I get a 'Duplicate key on insert detected'
I checked the table as well as its secondary indexes, just in case,
and there is no such value; despite that the error persisted
I've copied out the table and copied it in again (the indexes too)
with a different name and the insert was successful!
After this test, I dropped and re-created the original table and
subsequently the insert succeeded,so everything worked fine.
Has anyone experienced this behaviour before/is this a know issue?
Specs:
HP-UX ia64
Ingres II 9.2.0 (i64.hpu/143)
Martin Bowes
You almost certainly had a rules fired procedure in effect.
The insert into the table caused the rule to fire and it did something to another table which violated a duplicate rule. Once you dropped the table the associated rule was dropped as well. Ergo the final insert worked OK.
But everything is NOT fine. You will need to try and find what the rule was and reinstate it. It was probably there for a good reason afterall.
Do you have an old copydb output lying around somewhere?
Is the database journalled?
Martin Bowes
_______________________________________________
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres
Roy Hann
> Hi Nikos
>
> You almost certainly had a rules fired procedure in effect.
I should have thought of that. :-( Been there, done that. Wept
buckets.
> The insert into the table caused the rule to fire and it did
> something to another table which violated a duplicate rule. Once you
> dropped the table the associated rule was dropped as well. Ergo
> the final insert worked OK.
>
> But everything is NOT fine. You will need to try and find what
> the rule was and reinstate it. It was probably there for a good reason
> afterall.
Make sure it IS a good reason. I have learnt the hard way that
rule-fired procedures that create side-effects are rarely worth the
pain. I now try to avoid them for anything but data validation, but
they are immensely useful for that.
--
Roy
UK Ingres User Association Conference 2011 will be on Tuesday June 7 2011.
Put the date in your diary today.
nikosv
Hi Martin,
since I maintain the related table(s) I know for a fact that I have
not installed a rule on it
but to doublecheck if somoene else had installed a rule on it, I
inquired and am waiting for a reply
I will let you know if it was a rule related issues or something
else
thanks
Martin Bowes
Users will *NEVER* admit to anything...they are almost as evil as programmers...
Fortunatly you have access to auditdb. Try something like this...
auditdb -a dbname -table=iiqrytext -b... -e...
The output can then be scanned for rules. As an example I just dropped a table with a rule and checking in iiqrytext I see:
Delete : Transaction Id 00004ca54edc99cd Id (20,0) Table [iiqrytext,$ingres]
Record: <1862270976|0|110|0|0|create rule x_iu_rule after insert, update of x for each row execute procedure "ingres". x_iu_pro
c( new_id = new.id, new_a = new.a, new_string = new.string )>
Marty
-----Original Message-----
From: Martin Bowes [mailto:martin...@ctsu.ox.ac.uk]
Sent: 03 March 2011 08:22
To: Ingres and related product discussion forum
Cc: nikosv
Subject: Re: [Info-Ingres] Why 'Duplicate key on insert detected' ?
Hi Nikos
You almost certainly had a rules fired procedure in effect.
The insert into the table caused the rule to fire and it did something to another table which violated a duplicate rule. Once you dropped the table the associated rule was dropped as well. Ergo the final insert worked OK.
But everything is NOT fine. You will need to try and find what the rule was and reinstate it. It was probably there for a good reason afterall.
Do you have an old copydb output lying around somewhere?
Is the database journalled?
Martin Bowes
-----Original Message-----
From: nikosv [mailto:niko...@gmail.com]
Sent: 03 March 2011 07:33
Ingres Forums
I have seen this twice in VW 1.0 after a software crash in my c/OpenAPI
program.
The table was a non-VW table. After the crash it could not add the
next auto generated key.
Rebooting the VW server cleared the ghost record.
--
randolph
------------------------------------------------------------------------
randolph's Profile: http://community.ingres.com/forum/member.php?userid=25742
View this thread: http://community.ingres.com/forum/showthread.php?t=13096