CiviCRM Community Forums (archive)

During the 'Upgrade DB to 4.5.alpha1: SQL' step, I get the following error ...

UPDATE civicrm_line_item SET contribution_id = entity_id WHERE contribution_id IS NULL and entity_table = 'civicrm_contribution' [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`cbf_civicrm_442`.`civicrm_line_item`, CONSTRAINT `FK_civicrm_contribution_id` FOREIGN KEY (`contribution_id`) REFERENCES `civicrm_contribution` (`id`) ON DELETE SET NULL)]

It appears the contribution_id field is being set to the id of a contribution that doesn't exist, and the FK constraint doesn't like that. See CRM-14197.

On my database, running ...

select
  li.*
from
  civicrm_line_item li
where
  li.contribution_id IS NULL
  and li.entity_table = 'civicrm_contribution'
  and not exists (select * from civicrm_contribution c where c.id = li.entity_id)
... yields 60 line-items where the contribution doesn't exist.

As the foreign key constraint that has been added to the line-item table is "ON DELETE SET NULL", I gather that the code copes with a NULL contribution_id, so I think it's safe to change the upgrade script (line 553 of CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl) to ...

-- update line items for contributions with contribution id
UPDATE civicrm_line_item li
SET li.contribution_id = li.entity_id
WHERE li.contribution_id IS NULL and li.entity_table = 'civicrm_contribution'
and EXISTS (SELECT * FROM civicrm_contribution c WHERE c.id = li.entity_id);

Joe,

The answer is still 60. The extra condition just repeats a condition already in the where clause so it has no effect.

Could you explain what you're looking for in English and then I can play and have a look at my data?

Ken