2

During the db upgrade portion of a D7+CiviCRM 5.58.1->5.60.0 upgrade, I'm hitting the error below. Anyone else? Clues?

ALTER TABLE civicrm_job_log 
     ADD CONSTRAINT FK_civicrm_job_log_job_id FOREIGN KEY ( `job_id` ) REFERENCES civicrm_job ( id )  ON DELETE SET NULL [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm_iatsdemo_civicrm_ca`.`#sql-1_63`, CONSTRAINT `FK_civicrm_job_log_job_id` FOREIGN KEY (`job_id`) REFERENCES `civicrm_job` (`id`) ON DELETE SET NULL)]
2

4 Answers 4

2

You have some rows in civicrm_job_log that have job_id's that don't exist in civicrm_job. The line just before that is supposed to deal with that but must have failed. I would run UPDATE civicrm_job_log job_log LEFT JOIN civicrm_job job ON job.id = job_log.id SET job_id = NULL WHERE job.id IS NULL manually and see what it says.

1
  • Thank you, your explanation helps. Here's the sql outcome: UPDATE civicrm_job_log job_log LEFT JOIN civicrm_job job ON job.id = job_log.id SET job_id = NULL WHERE job.id IS NULL -> ; Query OK, 124 rows affected (0.462 sec) Rows matched: 208552 Changed: 124 Warnings: 0
    – Alan Dixon
    Apr 7, 2023 at 16:11
2

There was a bug in an upgrader step in CiviCRM 5.60 that causes this problem (and also the potential loss of other historical job log data).

This will be fixed by Civi 5.64.4 (and 5.63-esr) which should come out within the next 24 hours. So if you're version is <5.60 and you can wait until 7 Sep 2023 to upgrade then this should not be a problem.

1

If the other SQL update command doesn't fix it, try this: UPDATE civicrm_job_log SET job_id = NULL WHERE job_id NOT IN (SELECT id FROM civicrm_job);

1
  • This is the one that worked for me, thank you again :D Jun 18, 2023 at 18:12
0

After restoring the CiviCRM database from the backup, I did the following:

  1. Removed all records from civicrm_job_log table by command: DELETE FROM civicrm_job_log;

  2. Re-run the update process from CiviCRM main window, and it finished successfully.

1
  • 1
    In case it's not obvious to others with the same problem ... this removes all the log entries relating to scheduled jobs - which is fine if you never look at them, but you might want to try the other SQL fixes first.
    – Aidan
    May 26, 2023 at 15:15

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.