Upgrade to 5.70.2 fails with DB error

Question

Upgrading CiviCRM to 5.70.2 from 5.64.0 fails with the below error. Any pointers are appreciated.

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 (`mif10_civicrm`.`#sql-4f687h_5fuvo`, CONSTRAINT `FK_civicrm_ma  
  iling_event_queue_job_id` FOREIGN 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 (`mif10_civicrm`.`#sql-4f687h_5fuvo`, CONSTRAINT `FK_civicrm_ma  
  iling_event_queue_job_id` FOREIGN KEY (`job_id`) REFERENCES `civicrm_mailing_job` (`id`) ON DELETE SET NULL)] 

Answer 1

There are orphan data in civicrm_mailing_event_queue table. Below query should return you the count

SELECT COUNT(*) 
FROM civicrm_mailing_event_queue q 
  LEFT JOIN civicrm_mailing_job j 
      ON j.id = q.job_id 
WHERE j.id IS NULL;

To delete you can use

DELETE q.*
FROM civicrm_mailing_event_queue q 
  LEFT JOIN civicrm_mailing_job j 
    ON j.id = q.job_id 
WHERE j.id IS NULL;

HTH

Pradeep