Service Desk Practitioners Forum
cancel

Strange problem when creating changes with Workorders

Highlighted
George M. Meneg
Acclaimed Contributor.

Strange problem when creating changes with Workorders

Hello,

I got the following error when I create a change with chained workorders

JDBC error 2627: [DBCLUSTER]Violation of PRIMARY KEY constraint 'WOR_PK'. Cannot insert duplicate key in object 'ITSM_WORKORDERS'., SQL state: 23000 for query: INSERT INTO itsm_workorders
( wor_attachment_exists
,wor_requestor_per_oid
,wor_pri_oid
,slc_wor_per_oid
,wor_tem_oid
,wor_cha_oid
,wor_oid
,reg_created_by_oid
,reg_created
,wor_planstart
,wor_sta_oid
,wor_cisupdated
,wor_poo_oid
,wor_description
,wor_id
,wor_cod_oid
,ass_wog_from_oid
,ass_assignstatus
,ass_per_from_oid
,ass_per_to_oid
,ass_workgroup
,wor_lockseq
) VALUES ( 0
,?
,?
,?
,?
,?
,?
,?
, CONVERT(DATETIME,?,120)
, CONVERT(DATETIME,?,120)
,?
,0
,?
,?
,?
,?
,?
,NULL
,?
,?
,?
,?)
Values:
281478318129173
3094610073
281478318129173
281488149578513
281492096614491
281492096614533
281480531935310
2005/11/14 13:56:25
2005/11/14 13:56:25
281485720895912
281489043424153
Αλλαγή CNAME στο DNS REQUESTS
4507
633318698189791
281478301548638
281478318129173
281478318129173
281481127329836
1

The weird thing is:
1. This doesn't happen on the test server (the db copy on the test server is two week old)
2. the workorders are created.

It smells like a data corruption but I cannot imagine where!
menes fhtagn
9 REPLIES
George M. Meneg
Acclaimed Contributor.

Re: Strange problem when creating changes with Workorders

And '281492096614533' which is WOR_OID (the primary key of ITSM_WORKORDERS) *IS* unique. I cannot understand why I have this error.
menes fhtagn
Vasily Aksenov
Acclaimed Contributor.

Re: Strange problem when creating changes with Workorders

Hi
what sp do you use.
It may be cache problem on client
George M. Meneg
Acclaimed Contributor.

Re: Strange problem when creating changes with Workorders

Hello Vasily,

Unfortunately it isn't. I emtied both client and server cache and restarted the server. We have SP16 on the production server. But the test server, also SP16 doesn't excibit this behaviour!

It's more worrying than blocking problem. As I said the workorders created normally and the process works like a charm.
menes fhtagn
Chris Bailey_3
Outstanding Contributor.

Re: Strange problem when creating changes with Workorders

George,

If you run a SELECT statement against the database after the work order is created, does it have all the same wor_id and wor_oid values in the DB as the client's error message reported?

Like you say, the work order is created, so it's weird that the error ever shows up. Which leads me to wonder if the primary key value gets changed before it is really inserted into the db, or if the client is popping up a false alarm...

If it's a false alarm (likely since you say that it's unique already), you could try running a SQL trace to spot the statement that the SD Client is running to check for uniqueness while it's running. (MSSQL Server comes with a utility called "Profiler" that can do this, and I'd assume Oracle has a similar tool.) Then you could use that statement to troubleshoot from the client's perspective. However, if you're going to that level of effort, it might be time to call it in to HP support and see what they can tell you about it,too.

I just tried this on our test server, and I can see that the first thing executed is:

update ifc_sequences set seq_usn = seq_usn + 1 where seq_cat_oid = 556859410
select seq_usn from ifc_sequences where seq_cat_oid = 556859410

I believe this is where it picks the next unique ID. Could one of those sequence values have been modified? I'm not sure if there's a good way to "fix" it if it has, but that might be a place to start searching.

Anyway, that's the best I can think of without actually seeing the database and all that. Hope it has some value for you.

Thanks,
Chris
George M. Meneg
Acclaimed Contributor.

Re: Strange problem when creating changes with Workorders

Hello Chris and thank you for your reply,

It is not a client problem since the error message also appears at the server logs.

The value in the IFC_SEQUENSES is the right one. It's the ID of the last created workorder.

However I don't think that the above is about WOR_OID and not WOR_ID since the PK for ITSM_WORKGROUPS is WOR_OID.

Also, before this "error" is generated I got the error about "not able to replan". In this popup ALL the workorders and the change have already got an ID. It appears where it should normaly display "Change is created".
menes fhtagn
Chris Bailey_3
Outstanding Contributor.

Re: Strange problem when creating changes with Workorders

George,

Does the message appear when you create a work order without an associated change ticket? If not, then I doubt that it's something wrong with the ITSM_WORKORDERS table. It would seem to be a db or system rule of some sort at that point.

Also, I know I've seen that "replanning" message before. My quick search of the forums here revealed this:

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=891941

Is one of the planned start/finish fields not being filled in? In your error message above, I see wor_planstart, but not wor_planfinish. Did a template maybe populate the one value and not the other? It could be that the client doesn't attempt to increment the wor_oid until it werifies those planning conditions, but that's only speculation on my part.

Thanks,
Chris
George M. Meneg
Acclaimed Contributor.

Re: Strange problem when creating changes with Workorders

Hello Chris,

<<
Does the message appear when you create a work order without an associated change ticket?
If not, then I doubt that it's something wrong with the ITSM_WORKORDERS table.
It would seem to be a db or system rule of some sort at that point.
>>
No, it is happening only with chained workorders in change. If the workorders haven't predecessor/successor relations there is no prolbem.

<<
Also, I know I've seen that "replanning" message before. My quick search of the forums here revealed this:
>>

The replanning is not bothering me. I mentioned it because it is poped up before the <> displaying that workorders and change have been assigned an ID. Thus they are inserted into the appropriate tables.

Maybe the problem lies in ITSM_WORKORDERPREDECESSORS but the the constraints seems not to be violated.
menes fhtagn
Chris Bailey_3
Outstanding Contributor.

Re: Strange problem when creating changes with Workorders

Hi George,

I hate to say this, but I'm running low on good ideas. :-\

The only real pattern that I can correlate here is that if the error only appears when using predecessor/successor, I would suspect that the replanning message might be more than a mere nuissance, as it is also related to the predecessor/successor functionality. As such, I'd try providing both planned start and finish values to see if the error persists.

Sorry that I don't have anything else more definite to add to the discussion. Hopefully someone else has a good answer. If I think of anything better, I'll let you know.

Thanks,
Chris
George M. Meneg
Acclaimed Contributor.

Re: Strange problem when creating changes with Workorders

See http://openview.hp.com/ecare/getsupportdoc?docid=ITSM008250

The problem occurs when Predecessor and Successor are enabled as Audited Attributes in Tools > System > Security > Audit > Audit Rules > Work Order

The fix will be included on SP17 when available.
menes fhtagn