Answers.Body: <p>It means you have some records in the event queue that don't have a corresponding job. Probably the job got deleted at some point so I wouldn't be worried about that field and would just clear them out first:</p>
  <p><code>UPDATE civicrm_mailing_event_queue SET job_id = NULL WHERE job_id NOT IN (SELECT id FROM civicrm_mailing_job);</code></p>
  <p>Then, you can fool civi into thinking you're back on 5.66 by doing <code>UPDATE civicrm_domain SET version='5.66.0';</code></p>
  <p>Then re-run the upgrade.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-12T16:28:08.847Z
Answers.Text: Thank you, I've done that and the update has run successfully and now rebuilds the smart group cache no problem. But the long 'outdated tokens in use' section remains. Is it still just hanging around from the previous error and I can safely hide it, or does that mean the problem remains and I still need to fix it somewhere.
Answers.UserDisplayName: somnolentsurfer
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-12T17:09:28.287Z
Answers.Text: The token stuff is a real pain. You have to manually update your customized message templates (Admin - CiviMail - Message Templates - system tab). See also https://docs.civicrm.org/sysadmin/en/latest/upgrade/version-specific/#civicrm-543. Alternatively, revert the template to the default which will get the new tokens and then reapply your customizations.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-19T20:44:26.373Z
Answers.Text: I have what looks like the same problem with an upgrade from 5.66.2 to 5.67.3, so restored the database & code & ran the SQL as you suggested.  It found 3 items to fix but... I get the message - "Warning: #1048 Column 'job_id' cannot be null"  What's the best way round this?
Answers.UserDisplayName: Andy Clark
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-19T21:55:53.873Z
Answers.Text: The system with the 'null' problem - job_id cannot be null - was installed in 2013, and it's the same as another installed in 2012.  Yes more recent installs have null as 'Yes'.  I now know what to do!  Interesting - an upgrade missing sometime in the past. for job_id
Answers.UserDisplayName: Andy Clark
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-21T06:30:53.300Z
Answers.Text: Can you just first alter the table so job_id can be null? That's how it's supposed to be. `ALTER civicrm_mailing_event_queue MODIFY COLUMN job_id int(10) unsigned DEFAULT NULL COMMENT 'Mailing Job';`
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-01-17T13:37:01.617Z
Answers.Text: I have the same issue upgrading Civi from 5.63.4 to 5.69.2.  Should this be considered a core upgrade problem.  Creates a mess.
Answers.UserDisplayName: Dave T
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-12-08T22:33:02.210Z
Answers.LastActivityDate: 2023-12-08T22:33:02.210Z
Answers.UserDisplayName: Demerit
Body: <p>I just tried to update from 5.66.0 to 5.68.0 and it failed with the following message:</p>
  <pre><code>Error executing task &quot;Update civicrm_mailing_event_queue to permit deleting records from civicrm_mailing_job&quot;
  
  In FiveSixtySeven.php line 136:
                                                                                                                                                                                                      
    db error messageconstraint violationmessage DB Error: constraint violation                                                                                                                        
    sql                                                                                                                                                                                               
    user info                                                                                                                                                                                         
          ALTER TABLE `civicrm_mailing_event_queue`                                                                                                                                                   
          ADD CONSTRAINT `FK_civicrm_mailing_event_queue_job_id`                                                                                                                                      
          FOREIGN KEY (`job_id`)                                                                                                                                                                      
          REFERENCES `civicrm_mailing_job`(`id`) ON DELETE SET NULL                                                                                                                                   
         [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`database_name`.`#sql-alter-21481d-19e5278`, CONSTRAINT `FK_civicrm_mailing_event_queue_job_id` FO  
    REIGN KEY (`job_id`) REFERENCES `civicrm_mailing_job` (`id`) ON DELETE SET NULL)]                                                                                                                 
    debug info DB Error: constraint violation                                                                                                                                                         
    debug info                                                                                                                                                                                        
          ALTER TABLE `civicrm_mailing_event_queue`                                                                                                                                                   
          ADD CONSTRAINT `FK_civicrm_mailing_event_queue_job_id`                                                                                                                                      
          FOREIGN KEY (`job_id`)                                                                                                                                                                      
          REFERENCES `civicrm_mailing_job`(`id`) ON DELETE SET NULL                                                                                                                                   
         [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`database_name`.`#sql-alter-21481d-19e5278`, CONSTRAINT `FK_civicrm_mailing_event_queue_job_id` FO  
    REIGN KEY (`job_id`) REFERENCES `civicrm_mailing_job` (`id`) ON DELETE SET NULL)]
  </code></pre>
  <p>I've managed to get the site live again using <code>cv upgrade:db --skip</code> but now I have a bunch of errors in System Status.</p>
  <p>Under 'Scheduled Job Failures' I have:</p>
  <pre><code>Finished execution of Rebuild Smart Group Cache with result: Failure, Error message: DB Error: no such field
  </code></pre>
  <p>Under 'Outdated tokens in use' I have:</p>
  <pre><code>Please review your event_offline_receipt message template and remove references to the token {$dataArray} as it has been replaced by {found within $participants}
  Please review your event_offline_receipt message template and remove references to the token {$paidBy} as it has been replaced by {contribution.payment_instrument_id:label}
  Please review your event_offline_receipt message template and remove references to the token {$totalTaxAmount} as it has been replaced by {contribution.tax_amount}
  Please review your event_offline_receipt message template and remove references to the token {$amount} as it has been replaced by {found within $participants}
  Please review your event_offline_receipt message template and remove references to the token {$checkNumber} as it has been replaced by {contribution.check_number}
  Please review your event_offline_receipt message template and remove references to the token {$register_date} as it has been replaced by {participant.register_date}
  Please review your event_offline_receipt message template and remove references to the token {$receive_date} as it has been replaced by {contribution.receive_date}
  Please review your event_offline_receipt message template and remove references to the token {$is_pay_later} as it has been replaced by {contribution.is_pay_later}
  Please review your event_offline_receipt message template and remove references to the token {$totalAmount} as it has been replaced by {contribution.total_amount}
  Please review your event_offline_receipt message template and remove references to the token {$location} as it has been replaced by {event.location}
  Please review your event_offline_receipt message template and remove references to the token {$isShowLocation} as it has been replaced by {event.is_show_location|boolean}
  Please review your event_offline_receipt message template and remove references to the token {$event.participant_role} as it has been replaced by {participant.role_id:label}
  Please review your event_offline_receipt message template and remove references to the token {balanceAmount} as it has been replaced by {contribution.balance_amount}
  Please review your event_offline_receipt message template and remove references to the token {$financialTypeName} as it has been replaced by {contribution.financial_type_id:label}
  Please review your event_offline_receipt message template and remove references to the token {$trxn_id} as it has been replaced by {contribution.trxn_id}
  Please review your event_online_receipt message template and remove references to the token {`$participant.id`} as it has been replaced by {participant.id}
  </code></pre>
  <p>And under the upgrade headings it still shows 5.66.1 through 5.68.0 as upgrade options, even though the 'powered by' footer at the bottom successfully shows 5.68.0.</p>
  <p>How do I get everything back to full working order?</p>
  <p>Running on Drupal 7.99.</p>
  
Tags: <upgrade><drupal-7><cv>
Title: Error executing task "Update civicrm_mailing_event_queue to permit deleting records from civicrm_mailing_job"
ThreadText: # Error executing task "Update civicrm_mailing_event_queue to permit deleting records from civicrm_mailing_job"
  
  (Asked by: username_0 on 2023-12-08)
  
  I just tried to update from 5.66.0 to 5.68.0 and it failed with the following message:
  Error executing task "Update civicrm_mailing_event_queue to permit deleting records from civicrm_mailing_job"
  
  In FiveSixtySeven.php line 136:
                                                                                                                                                                                                      
    db error messageconstraint violationmessage DB Error: constraint violation                                                                                                                        
    sql                                                                                                                                                                                               
    user info                                                                                                                                                                                         
          ALTER TABLE `civicrm_mailing_event_queue`                                                                                                                                                   
          ADD CONSTRAINT `FK_civicrm_mailing_event_queue_job_id`                                                                                                                                      
          FOREIGN KEY (`job_id`)                                                                                                                                                                      
          REFERENCES `civicrm_mailing_job`(`id`) ON DELETE SET NULL                                                                                                                                   
         [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`database_name`.`#sql-alter-21481d-19e5278`, CONSTRAINT `FK_civicrm_mailing_event_queue_job_id` FO  
    REIGN KEY (`job_id`) REFERENCES `civicrm_mailing_job` (`id`) ON DELETE SET NULL)]                                                                                                                 
    debug info DB Error: constraint violation                                                                                                                                                         
    debug info                                                                                                                                                                                        
          ALTER TABLE `civicrm_mailing_event_queue`                                                                                                                                                   
          ADD CONSTRAINT `FK_civicrm_mailing_event_queue_job_id`                                                                                                                                      
          FOREIGN KEY (`job_id`)                                                                                                                                                                      
          REFERENCES `civicrm_mailing_job`(`id`) ON DELETE SET NULL                                                                                                                                   
         [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`database_name`.`#sql-alter-21481d-19e5278`, CONSTRAINT `FK_civicrm_mailing_event_queue_job_id` FO  
    REIGN KEY (`job_id`) REFERENCES `civicrm_mailing_job` (`id`) ON DELETE SET NULL)]
  
  I've managed to get the site live again using cv upgrade:db --skip but now I have a bunch of errors in System Status.
  Under 'Scheduled Job Failures' I have:
  Finished execution of Rebuild Smart Group Cache with result: Failure, Error message: DB Error: no such field
  
  Under 'Outdated tokens in use' I have:
  Please review your event_offline_receipt message template and remove references to the token {$dataArray} as it has been replaced by {found within $participants}
  Please review your event_offline_receipt message template and remove references to the token {$paidBy} as it has been replaced by {contribution.payment_instrument_id:label}
  Please review your event_offline_receipt message template and remove references to the token {$totalTaxAmount} as it has been replaced by {contribution.tax_amount}
  Please review your event_offline_receipt message template and remove references to the token {$amount} as it has been replaced by {found within $participants}
  Please review your event_offline_receipt message template and remove references to the token {$checkNumber} as it has been replaced by {contribution.check_number}
  Please review your event_offline_receipt message template and remove references to the token {$register_date} as it has been replaced by {participant.register_date}
  Please review your event_offline_receipt message template and remove references to the token {$receive_date} as it has been replaced by {contribution.receive_date}
  Please review your event_offline_receipt message template and remove references to the token {$is_pay_later} as it has been replaced by {contribution.is_pay_later}
  Please review your event_offline_receipt message template and remove references to the token {$totalAmount} as it has been replaced by {contribution.total_amount}
  Please review your event_offline_receipt message template and remove references to the token {$location} as it has been replaced by {event.location}
  Please review your event_offline_receipt message template and remove references to the token {$isShowLocation} as it has been replaced by {event.is_show_location|boolean}
  Please review your event_offline_receipt message template and remove references to the token {$event.participant_role} as it has been replaced by {participant.role_id:label}
  Please review your event_offline_receipt message template and remove references to the token {balanceAmount} as it has been replaced by {contribution.balance_amount}
  Please review your event_offline_receipt message template and remove references to the token {$financialTypeName} as it has been replaced by {contribution.financial_type_id:label}
  Please review your event_offline_receipt message template and remove references to the token {$trxn_id} as it has been replaced by {contribution.trxn_id}
  Please review your event_online_receipt message template and remove references to the token {`$participant.id`} as it has been replaced by {participant.id}
  
  And under the upgrade headings it still shows 5.66.1 through 5.68.0 as upgrade options, even though the 'powered by' footer at the bottom successfully shows 5.68.0.
  How do I get everything back to full working order?
  Running on Drupal 7.99.
  
  **Tags:** upgrade, drupal-7, cv
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2023-12-08. Score: 1 | Accepted answer
  
  It means you have some records in the event queue that don't have a corresponding job. Probably the job got deleted at some point so I wouldn't be worried about that field and would just clear them out first:
  
  `UPDATE civicrm_mailing_event_queue SET job_id = NULL WHERE job_id NOT IN (SELECT id FROM civicrm_mailing_job);`
  
  Then, you can fool civi into thinking you're back on 5.66 by doing `UPDATE civicrm_domain SET version='5.66.0';`
  
  Then re-run the upgrade.
  
  > **Comments:**
  > **username_0**: Thank you, I've done that and the update has run successfully and now rebuilds the smart group cache no problem. But the long 'outdated tokens in use' section remains. Is it still just hanging around from the previous error and I can safely hide it, or does that mean the problem remains and I still need to fix it somewhere.  
  > **username_1**: The token stuff is a real pain. You have to manually update your customized message templates (Admin - CiviMail - Message Templates - system tab). See also https://docs.civicrm.org/sysadmin/en/latest/upgrade/version-specific/#civicrm-543. Alternatively, revert the template to the default which will get the new tokens and then reapply your customizations.  
  > **username_2**: I have what looks like the same problem with an upgrade from 5.66.2 to 5.67.3, so restored the database & code & ran the SQL as you suggested.  It found 3 items to fix but... I get the message - "Warning: #1048 Column 'job_id' cannot be null"  What's the best way round this?  
  > **username_2**: The system with the 'null' problem - job_id cannot be null - was installed in 2013, and it's the same as another installed in 2012.  Yes more recent installs have null as 'Yes'.  I now know what to do!  Interesting - an upgrade missing sometime in the past. for job_id  
  > **username_1**: Can you just first alter the table so job_id can be null? That's how it's supposed to be. `ALTER civicrm_mailing_event_queue MODIFY COLUMN job_id int(10) unsigned DEFAULT NULL COMMENT 'Mailing Job';`  
  > **username_3**: I have the same issue upgrading Civi from 5.63.4 to 5.69.2.  Should this be considered a core upgrade problem.  Creates a mess.  
  
  ---
  
  