0

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.

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
6
  • 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.
    – somnolentsurfer
    Commented Dec 12, 2023 at 16:28
  • The token stuff is a real pain. You have to manually update your customized message templates (Admin - CiviMail - Message Templates - system tab). See also docs.civicrm.org/sysadmin/en/latest/upgrade/version-specific/…. Alternatively, revert the template to the default which will get the new tokens and then reapply your customizations.
    – Demerit
    Commented Dec 12, 2023 at 17:09
  • 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?
    – Andy Clark
    Commented Dec 19, 2023 at 20:44
  • 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
    – Andy Clark
    Commented Dec 19, 2023 at 21:55
  • 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';
    – Demerit
    Commented Dec 21, 2023 at 6:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.