Body: <p>I'm trying to upgrade a system from 5.66.2 to 5.67.3 &amp; this is the second problem I've had with this system, both to do with mailings. BTW this system was installed in 2013 and there have been a couple of problems where database settings have differed from more recent implementations.</p>
  <p><em>Here's the error:</em></p>
  <pre><code>Type    DB_Error
  Code    -3
  Message DB Error: constraint violation
  Mode    16
  UserInfo    UPDATE civicrm_mailing_event_queue q 
  INNER JOIN civicrm_mailing_job job ON job.id = q.job_id 
  SET q.mailing_id = job.mailing_id, q.is_test=job.is_test 
  WHERE q.id &gt;= 31 AND q.id &lt;= 267217 AND q.mailing_id IS NULL 
  [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint 
  fails (`dcmadmin_civicrm`.`civicrm_mailing_event_queue`, 
  CONSTRAINT `civicrm_mailing_event_queue_ibfk_1` 
  FOREIGN KEY (`mailing_id`) 
  REFERENCES `civicrm_mailing` (`id`) ON DELETE SET NULL)]
  </code></pre>
  <p><em>Also this may be useful:</em></p>
  <pre><code>Civi\Core\Exception\DBQueryException: DB Error: constraint violation in 
  /home/dcmadmin/public_html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php on line 945
  - DB_Error: DB Error: constraint violation in unknown on line unknown.
  </code></pre>
  <p>Can anyone help, please?</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-12-21T06:31:23.113Z
Comments.Text: Is this the same as https://civicrm.stackexchange.com/questions/46045/error-executing-task-update-civicrm-mailing-event-queue-to-permit-deleting-reco/46049 ?
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-12-21T08:29:28.980Z
Comments.Text: Almost - I had that problem so restored the database, ran the SQL to fix, and then ran the upgrade again  last evening.  The title of the first step that crashed was 'Update civicrm_mailing_event_queue to permit deleting records from civicrm_mailing_job' and this step is 'Backfill civicrm_mailing_event_queue to permit deleting records from civicrm_mailing_job'
Comments.UserDisplayName: Andy Clark
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-12-21T14:13:41.170Z
Comments.Text: Looking at the foreign key constraints for civicrm_mailing_event_queue I see there are - none! This db was created in 2013 & has somehow missed out on db updates.  One possible way to fix this is to delete all mailings, then add the FK constraints.  Is that going to work? I'm thinking that simply adding the FK constraints with a lot of data might cause problems.
Comments.UserDisplayName: Andy Clark
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-12-21T14:26:49.013Z
Comments.Text: It will be slow to add if there's lots of mailings but if you run it in the backend it shouldn't time out. If you do want to delete all mailings, you could just recreate the table completely to match a good db.
Comments.UserDisplayName: Demerit
Tags: <upgrade>
Title: Error executing 'Backfill civicrm_mailing_event_queue' in upgrade to 5.67.3
ThreadText: # Error executing 'Backfill civicrm_mailing_event_queue' in upgrade to 5.67.3
  
  (Asked by: username_0 on 2023-12-20)
  
  I'm trying to upgrade a system from 5.66.2 to 5.67.3 & this is the second problem I've had with this system, both to do with mailings. BTW this system was installed in 2013 and there have been a couple of problems where database settings have differed from more recent implementations.
  Here's the error:
  Type    DB_Error
  Code    -3
  Message DB Error: constraint violation
  Mode    16
  UserInfo    UPDATE civicrm_mailing_event_queue q 
  INNER JOIN civicrm_mailing_job job ON job.id = q.job_id 
  SET q.mailing_id = job.mailing_id, q.is_test=job.is_test 
  WHERE q.id >= 31 AND q.id <= 267217 AND q.mailing_id IS NULL 
  [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint 
  fails (`dcmadmin_civicrm`.`civicrm_mailing_event_queue`, 
  CONSTRAINT `civicrm_mailing_event_queue_ibfk_1` 
  FOREIGN KEY (`mailing_id`) 
  REFERENCES `civicrm_mailing` (`id`) ON DELETE SET NULL)]
  
  Also this may be useful:
  Civi\Core\Exception\DBQueryException: DB Error: constraint violation in 
  /home/dcmadmin/public_html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php on line 945
  - DB_Error: DB Error: constraint violation in unknown on line unknown.
  
  Can anyone help, please?
  
  **Tags:** upgrade
  
  > **Comments:**
  > **username_1**: Is this the same as https://civicrm.stackexchange.com/questions/46045/error-executing-task-update-civicrm-mailing-event-queue-to-permit-deleting-reco/46049 ?  
  > **username_0**: Almost - I had that problem so restored the database, ran the SQL to fix, and then ran the upgrade again  last evening.  The title of the first step that crashed was 'Update civicrm_mailing_event_queue to permit deleting records from civicrm_mailing_job' and this step is 'Backfill civicrm_mailing_event_queue to permit deleting records from civicrm_mailing_job'  
  > **username_0**: Looking at the foreign key constraints for civicrm_mailing_event_queue I see there are - none! This db was created in 2013 & has somehow missed out on db updates.  One possible way to fix this is to delete all mailings, then add the FK constraints.  Is that going to work? I'm thinking that simply adding the FK constraints with a lot of data might cause problems.  
  > **username_1**: It will be slow to add if there's lots of mailings but if you run it in the backend it shouldn't time out. If you do want to delete all mailings, you could just recreate the table completely to match a good db.  
  
  ---
  
  ## 0 answers
  
  