CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • 4.3 - Groups not showing in contact profile -> DB Error: constraint violation
Pages: [1]

Author Topic: 4.3 - Groups not showing in contact profile -> DB Error: constraint violation  (Read 658 times)

SemperFi

  • I post frequently
  • ***
  • Posts: 177
  • Karma: 3
  • CiviCRM version: 4.5.4
  • CMS version: Wordpress 4.0.1
  • MySQL version: MySQL 5.5.16
  • PHP version: PHP 5.3.17
4.3 - Groups not showing in contact profile -> DB Error: constraint violation
April 17, 2013, 01:50:07 am
Hello,
My upgrade from 4.29 to 4.3 (Drupal 7) went well but I've an issue with the "Groups" tab in the contact profile.
The list of groups the user belongs to is not showing up.
I got this message:

Quote
Sorry but we are not able to provide this at the moment.
DB Error: constraint violation
Return to home page.

With the debbugging mode activated I got these details :
Code: [Select]
backTrace

#0 D:\xampp\htdocs\www\sites\all\modules\civicrm\CRM\Core\Error.php(148): CRM_Core_Error::backtrace()
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 D:\xampp\htdocs\www\sites\all\modules\civicrm\packages\PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 D:\xampp\htdocs\www\sites\all\modules\civicrm\packages\DB.php(969): PEAR_Error->PEAR_Error("DB Error: constraint violation", -3, 16, (Array:2), "INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (\nSELEC...")
#4 D:\xampp\htdocs\www\sites\all\modules\civicrm\packages\PEAR.php(564): DB_Error->DB_Error(-3, 16, (Array:2), "INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (\nSELEC...")
#5 D:\xampp\htdocs\www\sites\all\modules\civicrm\packages\DB\common.php(1905): PEAR->raiseError(NULL, -3, NULL, NULL, "INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (\nSELEC...", "DB_Error", TRUE)
#6 D:\xampp\htdocs\www\sites\all\modules\civicrm\packages\DB\mysql.php(898): DB_common->raiseError(-3, NULL, NULL, NULL, "1452 ** Cannot add or update a child row: a foreign key constraint fails (`if...")
#7 D:\xampp\htdocs\www\sites\all\modules\civicrm\packages\DB\mysql.php(327): DB_mysql->mysqlRaiseError()
#8 D:\xampp\htdocs\www\sites\all\modules\civicrm\packages\DB\common.php(1216): DB_mysql->simpleQuery("INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (\nSELEC...")
#9 D:\xampp\htdocs\www\sites\all\modules\civicrm\packages\DB\DataObject.php(2421): DB_common->query("INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (\nSELEC...")
#10 D:\xampp\htdocs\www\sites\all\modules\civicrm\packages\DB\DataObject.php(1613): DB_DataObject->_query("INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (\nSELEC...")
#11 D:\xampp\htdocs\www\sites\all\modules\civicrm\CRM\Core\DAO.php(155): DB_DataObject->query("INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (\nSELEC...")
#12 D:\xampp\htdocs\www\sites\all\modules\civicrm\CRM\Core\DAO.php(917): CRM_Core_DAO->query("INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (\nSELEC...", TRUE)
#13 D:\xampp\htdocs\www\sites\all\modules\civicrm\CRM\Contact\BAO\GroupContactCache.php(400): CRM_Core_DAO::executeQuery("INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (\nSELEC...")
#14 D:\xampp\htdocs\www\sites\all\modules\civicrm\CRM\Contact\BAO\Query.php(2629): CRM_Contact_BAO_GroupContactCache::load(Object(CRM_Core_DAO))
#15 D:\xampp\htdocs\www\sites\all\modules\civicrm\CRM\Contact\BAO\Query.php(2596): CRM_Contact_BAO_Query->addGroupContactCache((Array:1))
#16 D:\xampp\htdocs\www\sites\all\modules\civicrm\CRM\Contact\BAO\Query.php(2562): CRM_Contact_BAO_Query->savedSearch((Array:5))
#17 D:\xampp\htdocs\www\sites\all\modules\civicrm\CRM\Contact\BAO\Query.php(1389): CRM_Contact_BAO_Query->group((Array:5))
#18 D:\xampp\htdocs\www\sites\all\modules\civicrm\CRM\Contact\BAO\Query.php(1609): CRM_Contact_BAO_Query->whereClauseSingle((Array:5))
#19 D:\xampp\htdocs\www\sites\all\modules\civicrm\CRM\Contact\BAO\Query.php(481): CRM_Contact_BAO_Query->whereClause()
#20 D:\xampp\htdocs\www\sites\all\modules\civicrm\CRM\Contact\BAO\Query.php(442): CRM_Contact_BAO_Query->initialize()
#21 D:\xampp\htdocs\www\sites\all\modules\civicrm\CRM\Contact\BAO\Query.php(3967): CRM_Contact_BAO_Query->__construct((Array:1), (Array:1), NULL, TRUE, FALSE, 1, TRUE, TRUE, FALSE)
#22 D:\xampp\htdocs\www\sites\all\modules\civicrm\CRM\Contact\BAO\GroupContactCache.php(156): CRM_Contact_BAO_Query::apiQuery((Array:1), (Array:1), NULL, NULL, 0, 0, FALSE)
#23 D:\xampp\htdocs\www\sites\all\modules\civicrm\CRM\Contact\BAO\GroupContactCache.php(140): CRM_Contact_BAO_GroupContactCache::add((Array:24))
#24 D:\xampp\htdocs\www\sites\all\modules\civicrm\CRM\Contact\BAO\GroupContactCache.php(461): CRM_Contact_BAO_GroupContactCache::loadAll()
#25 D:\xampp\htdocs\www\sites\all\modules\civicrm\CRM\Contact\Page\View\GroupContact.php(65): CRM_Contact_BAO_GroupContactCache::contactGroup("107")
#26 D:\xampp\htdocs\www\sites\all\modules\civicrm\CRM\Contact\Page\View\GroupContact.php(164): CRM_Contact_Page_View_GroupContact->browse()
#27 D:\xampp\htdocs\www\sites\all\modules\civicrm\CRM\Core\Invoke.php(292): CRM_Contact_Page_View_GroupContact->run((Array:4), NULL)
#28 D:\xampp\htdocs\www\sites\all\modules\civicrm\CRM\Core\Invoke.php(70): CRM_Core_Invoke::runItem((Array:14))
#29 D:\xampp\htdocs\www\sites\all\modules\civicrm\CRM\Core\Invoke.php(52): CRM_Core_Invoke::_invoke((Array:4))
#30 D:\xampp\htdocs\www\sites\all\modules\civicrm\drupal\civicrm.module(436): CRM_Core_Invoke::invoke((Array:4))
#31 [internal function](): civicrm_invoke("contact", "view", "group")
#32 D:\xampp\htdocs\www\includes\menu.inc(517): call_user_func_array("civicrm_invoke", (Array:3))
#33 D:\xampp\htdocs\www\index.php(21): menu_execute_active_handler()
#34 {main}

Could you please help ?
Thanks

SemperFi

  • I post frequently
  • ***
  • Posts: 177
  • Karma: 3
  • CiviCRM version: 4.5.4
  • CMS version: Wordpress 4.0.1
  • MySQL version: MySQL 5.5.16
  • PHP version: PHP 5.3.17
Re: 4.3 - Groups not showing in contact profile -> DB Error: constraint violation
April 20, 2013, 06:20:52 am
Hello,
I updated to 4.3.1 hoping the problem would be solved but still got the issue.
I still get the message error here above when clicking on the group tab in the contact profile.

Here a few more details on the error :
Code: [Select]
Code d'erreur de la base de données : Cannot add or update a child row: a foreign key constraint fails (`ifpcivicrm`.`civicrm_group_contact_cache`, CONSTRAINT `FK_civicrm_group_contact_cache_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE), 1452
Détails additionnels :

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -3
    [message] => DB Error: constraint violation
    [mode] => 16
    [debug_info] => INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (
SELECT 36 as group_id, contact_id as id
FROM   civicrm_group_contact
WHERE  civicrm_group_contact.status = 'Added'
  AND  civicrm_group_contact.group_id = 36 ); [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`ifpcivicrm`.`civicrm_group_contact_cache`, CONSTRAINT `FK_civicrm_group_contact_cache_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
    [type] => DB_Error
    [user_info] => INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (
SELECT 36 as group_id, contact_id as id
FROM   civicrm_group_contact
WHERE  civicrm_group_contact.status = 'Added'
  AND  civicrm_group_contact.group_id = 36 ); [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`ifpcivicrm`.`civicrm_group_contact_cache`, CONSTRAINT `FK_civicrm_group_contact_cache_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (
SELECT 36 as group_id, contact_id as id
FROM   civicrm_group_contact
WHERE  civicrm_group_contact.status = 'Added'
  AND  civicrm_group_contact.group_id = 36 ); [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`ifpcivicrm`.`civicrm_group_contact_cache`, CONSTRAINT `FK_civicrm_group_contact_cache_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]"]
)


Could you please help, this is very blocking.
Thanks

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: 4.3 - Groups not showing in contact profile -> DB Error: constraint violation
April 20, 2013, 06:57:54 am

in general FK constraints are not resolved via upgrades

from the below query, seems like you have entries in your civicrm_group_contact table that do not have entries in the contact table, i.e.missing FK's and hence the constraint error

this typically happens when the DB was / is MyISAM which does not implement FK's and cascaded constraints

u'll need to clean up the bad contact ids and maybe check other parts of the DB also. Some knowledge of sql is needed

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

SemperFi

  • I post frequently
  • ***
  • Posts: 177
  • Karma: 3
  • CiviCRM version: 4.5.4
  • CMS version: Wordpress 4.0.1
  • MySQL version: MySQL 5.5.16
  • PHP version: PHP 5.3.17
Re: 4.3 - Groups not showing in contact profile // RESOLVED
April 20, 2013, 07:35:39 am
Thanks! I exported both tables "civicrm_group_contact" and "contact" and check the matching on the "contact_id" field in Excel.
I manually deleted the record in "civicrm_group_contact" for which I didn't find a match in the "contact" table.
It fixed the bug ! Now the tab is working well again.
I still don't understand why it appears after upgrading on 4.3... is there something to change in the FK to avoid this going further?
Thank you very much !

 :)
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • 4.3 - Groups not showing in contact profile -> DB Error: constraint violation

This forum was archived on 2017-11-26.