1005076 - [Oracle] Job Queue foreign key error when bugmail generated faster than processed

Status: RESOLVED DUPLICATE of bug 553296

(Bugzilla :: Email Notifications, defect)

Description

[chad.m.ashworth]

Attachment: ts_job_uniqkey_fix.sql

User Agent: Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E; InfoPath.3; MS-RTC LM 8)

Steps to reproduce:

Cloned a bug that had quite a few dependent bugs (it has 17 right now).


Actual results:

I was getting a foreign key constraint error for IDX_500C5F2143BE4620CAC3 on the ts_job table when I had use_mailer_queue enabled.


Expected results:

Email notifications should have been sent.  What appears to be happening is the UNIQKEY field in the ts_job table is a primary key but doesn't get popluated with data.  With so many bugs tied together, the ts_job table was getting populated faster than it was getting processed.  To fix this problem I created a sequence (I'm using ORACLE for the database) TS_JOB_UNIQKEY_SEQ and tied it to the TS_JOB table with a trigger (TS_JOB_UNIQKEY_TR) and this fixed the problem.  I have attached the code.

Comment 1

[Teemu Mannermaa (:wicked)]

(In reply to chad.m.ashworth from comment #0)
> I was getting a foreign key constraint error for IDX_500C5F2143BE4620CAC3 on
> the ts_job table when I had use_mailer_queue enabled.
... 
> Email notifications should have been sent.  What appears to be happening is
> the UNIQKEY field in the ts_job table is a primary key but doesn't get

Looking at our schema, the primary key of ts_job table should be jobid field and there's no foreign keys used in these tables at all. As such, this looks like a Oracle specific problem. Our Oracle driver might be hacked to use foreign keys for indexes or Oracle generates them by itself randomly.

Comment 2

[Teemu Mannermaa (:wicked)]

Looks like we already have a bug open for that and happens because Oracle does things non-standard way.