553296 - [Oracle] TheSchewartz ts_job table unique constraint broken on Oracle

Status: NEW

(Bugzilla :: Database, defect)

Description

[David Taylor]

User-Agent:       Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2) Gecko/20100115 Firefox/3.6
Build Identifier: bugzilla 3.6.0

This index:
            ts_job_funcid_idx => {FIELDS => [qw(funcid uniqkey)],
                                  TYPE   => 'UNIQUE'},

Causes Bugzilla to fail if an attempt is made to insert more than one email in to the job table at one time.  This is due to Oracle's non standard way of treating NULL fields in a UNIQUE index.  Against (most interpretations of) the SQL standard, Oracle considers NULL fields to match when checking for violations of a unique index.  As Bugzilla leaves the uniqkey field NULL for all jobs, only one job can exist at each time (per funcid).

It is possible to work around this using a function based index, e.g.:

create unique index TS_JOB_IDX_DT on TS_JOB
  (
    DECODE(UNIQKEY,
      NULL, NULL,  -- if uniqkey is null, return NULL
                   -- funcid can't be null
      TO_CHAR(FUNCID)||UNIQKEY) -- both non-null, return concatenation
  );

See also http://forums.oracle.com/forums/thread.jspa?threadID=374915 and http://bugs.mysql.com/bug.php?id=8173

Reproducible: Always

Steps to Reproduce:
1. Install Bugzilla and TheSchwartz with an Oracle database.
2. Cause two email jobs to exist at the same time.

Actual Results:  
Internal server error due to unique constraint violation.

Expected Results:  
Both emails queued and sent.

Comment 1

[Max Kanat-Alexander]

  WTG, Oracle.

  Xiaoou: If we fix this, I'd only want a fix inside Bugzilla::DB::Oracle, not in Bugzilla's code itself.

  Another solution to this might be to use TheSchwartz::Simple, which uses an existing DBI connection for insertions, and so would run through our code.