P4DTI issue job000354

TitleMySQL bug stops replicator from seeing some Bugzilla changes
Statusclosed
Prioritycritical
Assigned userNick Barnes
OrganizationRavenbrook
DescriptionThe replicator may fail to notice some changes to Bugzilla bugs. In particular, the addition of a comment to a Bugzilla bug. This occurs with some versions of MySQL (e.g. 3.23.37) but not others (e.g. 3.22.32).
AnalysisThis has the same underlying cause as job000345: some versions of MySQL fail to return some rows for some LEFT JOIN selects.
To be specific, a LEFT JOIN with a table including a DATETIME column which is NOT NULL, WHERE not_null_datetime IS NULL, returns no rows.
Here's a little example:

Make a small database with these tables and this row:

  create table a (id int);
  create table b (id int, dt datetime not null, f int not null);
  insert into a values (1);

Then each of these two queries correctly returns a single row.

  select a.id from a left join b using (id) where b.f is null;
  select a.id from a left join b using (id) where b.id is null;

But this query returns the empty set:

  select a.id from a left join b using (id) where b.dt is null;

We have such a query in the Bugzilla integration. We can work around this bug in MySQL by using WHERE some_other_field IS NULL. In particular, say "where bugs_activity.fieldid is null" instead of "where bugs_activity.bug_when is null".
How foundcustomer
EvidenceThis is a detailed bug report from a user seeing this problem: <http://info.ravenbrook.com/mail/2001/07/12/14-05-53/0.txt>.
This is mail from a user who found a different workaround: <http://info.ravenbrook.com/mail/2001/07/17/10-51-30/0.txt>.
Observed in1.1.1
Introduced in0.5.0
Test procedure<http://www.ravenbrook.com/project/p4dti/master/test/test_p4dti.py>
Created byNick Barnes
Created on2001-07-16 12:49:01
Last modified byGareth Rees
Last modified on2001-12-10 19:50:05
History2001-07-16 NB Created.

Fixes

Change Effect Date User Description
14183 closed 2001-07-16 18:12:12 Gareth Rees Replicate new bugs in Bugzilla even in buggy MySQL versions like 3.23.37.