CiviCRM Community Forums (archive)

I once had a dodgy email address bring CiviMail to a halt half-way through a mailing. It got stuck at that address and refused to make any progress. When I fixed the email address, it kept going.

I wrote the following query and found it quite helpful in tracking down the problem.

SELECT j.id AS Job, q.id AS Queue, c.sort_name AS Contact, e.email AS Email
FROM
  civicrm_mailing_event_queue AS q,
  civicrm_mailing_job AS j,
  civicrm_email AS e,
  civicrm_contact AS c
WHERE
  j.id = q.job_id
  AND j.status IN ('Running', 'Paused')
  AND j.is_test = 0
  AND q.email_id = e.id
  AND q.contact_id = c.id
  AND NOT EXISTS (
    SELECT *
    FROM civicrm_mailing_event_delivered AS d
    WHERE d.event_queue_id = q.id
  )
ORDER BY Job, Queue
LIMIT 25
Notes ...

• This query shows a work-in-progress snapshot of where CiviMail is up to.
• Each mailing Job creates a mail Queue entry for each Contact being sent an email. The Email address is selected from one which belongs to that contact.
• Only jobs which are running or paused are shown. Those which have completed or have been cancelled or have not started are ignored.
• Test jobs are ignored (you can remove this condition; I found I had too many test jobs which had failed for uninteresting reasons).
• Queue entries which have already been delivered are ignored.
• Jobs and queue entries are shown in order-of-creation order.
• The limit is applied because (in my case) the next Queue entry to be processed (ie, the first shown) is the interesting one.
• Please note that I have not kept this query up-to-date. I wrote it for 2.0 and it still runs for 2.2.0, but I haven't tested it. It's a diagnostic tool for finding dodgy data ... I hope it helps others but don't lean on it too hard!