CiviCRM version: 5.19.4

CMS: Drupal 7.69

PHP version: 7.1

When trying to view the Mailing Details Report page, I get the following error:

DB Error: Constraint violation

Looking in the error logs, it is due to the group_id column as you can see here:

[nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`prefix`.`civicrm_group_contact`, CONSTRAINT `FK_civicrm_group_contact_group_id` FOREIGN KEY (`group_id`) REFERENCES `civicrm_group` (`id`) ON DELETE CASCADE)]"])

The important bit being:

CONSTRAINT `FK_civicrm_group_contact_group_id` FOREIGN KEY (`group_id`) REFERENCES `civicrm_group` (`id`)

I sort of understand why this error has come about, it's due to a group_id in the civicrm_group_contact table not being found in under the group_id column of the civicrm_group table (I think).

I have tried running the following to try and find which row(s) is causing this problem:

SELECT * FROM `civicrm_group_contact` WHERE group_id NOT IN (SELECT id from civicrm_group)

However, this returned no results.

I have also truncated the cache tables which is the solution to a few of the other problems I have found whilst researching and cleared normal CiviCRM cache.

It's not just me getting this error, it's anyone else trying to access the same page getting the error as well.

I've found a lot of similar questions regarding this but most of them have not have any solutions and the ones that do, I have either tried them or do not apply to me.

Any help would be greatly appreciated.

EDIT: Added the backtrace from the ConfigAndLog file in the hope it helps solve my problem.

#6 /home/.../public_html/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-3, NULL, NULL, "\nREPLACE INTO civicrm_group_contact ( group_id, contact_id, status )\nVALUES...", "1452 ** Cannot add or update a child row: a foreign key constraint fails (`cr...")
  • To help you get help - what have you tried? - Also is it possible to include the full error message from your Civicrm ConfigAndLog log file - ideally it would be great to see the full sql statement that fails. (civicrm.org/extensions/civicrm-log-viewer - if you need help finding) Feb 13 '20 at 19:37
  • I've tried what's already mentioned in my question, the error is from the ConfigAndLog file, I just excluded most of it and included the bit I thought was important, I'll edit my question to include a bit more of it though :)
    – Jade
    Feb 14 '20 at 7:24

Turned out to be a filter value in the report that was referencing a mailing group that no longer exists. It's 'id' was therefore missing from the civicrm_group table.


So I feel rather silly, all that was needed was to go into the report and edit the filters so that it was no longer looking at the group that no longer existed rather than going to the report via the shortcut we have which ran the report and hence brought up the error.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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