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 »
  • Using CiviCRM (Moderator: Dave Greenberg) »
  • constraint violation custom field in relationship
Pages: [1]

Author Topic: constraint violation custom field in relationship  (Read 1226 times)

sibro

  • I post occasionally
  • **
  • Posts: 47
  • Karma: 2
  • CiviCRM version: 4.4.5
  • CMS version: Drupal 7.27
  • MySQL version: 5.5
  • PHP version: 5.4
constraint violation custom field in relationship
November 25, 2012, 03:50:09 am
I have a relationship from contact to organization with a custom field of position. I can insert the relationship but when I try to add custom field from a drop down box it gives a foreign key failure. The relations were batch loaded and loaded fine, but I am unable to add new ones

Code: [Select]
backTrace

#0 /home/fjmcprod/public_html/sites/all/modules/civicrm/CRM/Core/Error.php(148): CRM_Core_Error::backtrace()
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /home/fjmcprod/public_html/sites/all/modules/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /home/fjmcprod/public_html/sites/all/modules/civicrm/packages/DB.php(968): PEAR_Error->PEAR_Error("DB Error: constraint violation", -3, 16, (Array:2), "INSERT INTO civicrm_value_international_position_3  ( international_title_32,...")
#4 /home/fjmcprod/public_html/sites/all/modules/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-3, 16, (Array:2), "INSERT INTO civicrm_value_international_position_3  ( international_title_32,...")
#5 /home/fjmcprod/public_html/sites/all/modules/civicrm/packages/DB/common.php(1903): PEAR->raiseError(NULL, -3, NULL, NULL, "INSERT INTO civicrm_value_international_position_3  ( international_title_32,...", "DB_Error", TRUE)
#6 /home/fjmcprod/public_html/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 (`fj...")
#7 /home/fjmcprod/public_html/sites/all/modules/civicrm/packages/DB/mysql.php(327): DB_mysql->mysqlRaiseError()
#8 /home/fjmcprod/public_html/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("INSERT INTO civicrm_value_international_position_3  ( international_title_32,...")
#9 /home/fjmcprod/public_html/sites/all/modules/civicrm/packages/DB/DataObject.php(2421): DB_common->query("INSERT INTO civicrm_value_international_position_3  ( international_title_32,...")
#10 /home/fjmcprod/public_html/sites/all/modules/civicrm/packages/DB/DataObject.php(1613): DB_DataObject->_query("INSERT INTO civicrm_value_international_position_3  ( international_title_32,...")
#11 /home/fjmcprod/public_html/sites/all/modules/civicrm/CRM/Core/DAO.php(155): DB_DataObject->query("INSERT INTO civicrm_value_international_position_3  ( international_title_32,...")
#12 /home/fjmcprod/public_html/sites/all/modules/civicrm/CRM/Core/DAO.php(922): CRM_Core_DAO->query("INSERT INTO civicrm_value_international_position_3  ( international_title_32,...", TRUE)
#13 /home/fjmcprod/public_html/sites/all/modules/civicrm/CRM/Core/BAO/CustomValueTable.php(236): CRM_Core_DAO::executeQuery("INSERT INTO civicrm_value_international_position_3  ( international_title_32,...", (Array:5))
#14 /home/fjmcprod/public_html/sites/all/modules/civicrm/CRM/Core/BAO/CustomValueTable.php(340): CRM_Core_BAO_CustomValueTable::create((Array:1))
#15 /home/fjmcprod/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Relationship.php(233): CRM_Core_BAO_CustomValueTable::store((Array:4), "civicrm_relationship", "30307")
#16 /home/fjmcprod/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Relationship.php(128): CRM_Contact_BAO_Relationship::add((Array:15), (Array:3), "2")
#17 /home/fjmcprod/public_html/sites/all/modules/civicrm/CRM/Contact/Form/Relationship.php(561): CRM_Contact_BAO_Relationship::create((Array:15), (Array:3))
#18 /home/fjmcprod/public_html/sites/all/modules/civicrm/CRM/Core/Form.php(246): CRM_Contact_Form_Relationship->postProcess()
#19 /home/fjmcprod/public_html/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Upload.php(151): CRM_Core_Form->mainProcess()
#20 /home/fjmcprod/public_html/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Upload.php(128): CRM_Core_QuickForm_Action_Upload->realPerform(Object(CRM_Contact_Form_Relationship), "upload")
#21 /home/fjmcprod/public_html/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Upload->perform(Object(CRM_Contact_Form_Relationship), "upload")
#22 /home/fjmcprod/public_html/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Contact_Form_Relationship), "upload")
#23 /home/fjmcprod/public_html/sites/all/modules/civicrm/CRM/Core/Controller.php(289): HTML_QuickForm_Page->handle("upload")
#24 /home/fjmcprod/public_html/sites/all/modules/civicrm/CRM/Contact/Page/View/Relationship.php(212): CRM_Core_Controller->run()
#25 /home/fjmcprod/public_html/sites/all/modules/civicrm/CRM/Contact/Page/View/Relationship.php(248): CRM_Contact_Page_View_Relationship->edit()
#26 /home/fjmcprod/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(227): CRM_Contact_Page_View_Relationship->run((Array:4), NULL)
#27 /home/fjmcprod/public_html/sites/all/modules/civicrm/drupal/civicrm.module(500): CRM_Core_Invoke::invoke((Array:4))
#28 [internal function](): civicrm_invoke("contact", "view", "rel")
#29 /home/fjmcprod/public_html/includes/menu.inc(516): call_user_func_array("civicrm_invoke", (Array:3))
#30 /home/fjmcprod/public_html/index.php(21): menu_execute_active_handler()
#31 {main}

Sorry but we are not able to provide this at the moment.
DB Error: constraint violation
Code: [Select]
Error Details
http://www2.fjmc.org/civicrm/contact/view/rel?action=update&reset=1&cid=7759&id=30307&rtype=a_b

Return to home page.
$Fatal Error Details = Array ( [callback] => Array ( [0] => CRM_Core_Error [1] => handle ) [code] => -3 [message] => DB Error: constraint violation [mode] => 16 [debug_info] => INSERT INTO civicrm_value_international_position_3 ( international_title_32,international_additional_title_33,international_comments_34,international_weight_35,entity_id ) VALUES ( 'International Vice President','','','',30307 ) ON DUPLICATE KEY UPDATE international_title_32 = 'International Vice President',international_additional_title_33 = '',international_comments_34 = '',international_weight_35 = '' [nativecode=1452
** Cannot add or update a child row: a foreign key constraint fails
(`fjmcprod_crm`.`civicrm_value_international_position_3`, CONSTRAINT `FK_civicrm_value_international_posi_318d889df45bc8d8` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_relationship_sav3`
(`id)] [type] => DB_Error [user_info] => INSERT INTO civicrm_value_international_position_3 ( international_title_32,international_additional_title_33,international_comments_34,international_weight_35,entity_id ) VALUES ( 'International Vice President','','','',30307 ) ON DUPLICATE KEY UPDATE international_title_32 = 'International Vice President',international_additional_title_33 = '',international_comments_34 = '',international_weight_35 = '' [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`fjmcprod_crm`.`civicrm_value_international_position_3`, CONSTRAINT `FK_civicrm_value_international_posi_318d889df45bc8d8` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_relationship_sav3`
(`id)] [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_value_international_position_3 ( international_title_32,international_additional_title_33,international_comments_34,international_weight_35,entity_id ) VALUES ( 'International Vice President','','','',30307 ) ON DUPLICATE KEY UPDATE international_title_32 = 'International Vice President',international_additional_title_33 = '',international_comments_34 = '',international_weight_35 = '' [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`fjmcprod_crm`.`civicrm_value_international_position_3`, CONSTRAINT `FK_civicrm_value_international_posi_318d889df45bc8d8` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_relationship_sav3` (`id)]"] )

Severity debug
Bruce

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: constraint violation custom field in relationship
November 25, 2012, 05:05:36 am

1. what happens when u create a new relationship and add custom data there. Also check what happens when u edit it

2. If the above returns an error, can u try and reproduce on our demo server

thanx

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

sibro

  • I post occasionally
  • **
  • Posts: 47
  • Karma: 2
  • CiviCRM version: 4.4.5
  • CMS version: Drupal 7.27
  • MySQL version: 5.5
  • PHP version: 5.4
Re: constraint violation custom field in relationship
November 25, 2012, 06:22:34 am
Creating the relationship is a two step process. First it creates the relationship and then it tries to add the custom data. I have a number of these relationships on my system, they all seem to fail the same way. The relationship is built ok and then the custom data is added which fails.

From my posting can you identify the failing call so I can investigate it. I will also try to reproduce it on the demo system. 

I believe the batch load was done under 4.1.3 but obviously it did not use the drop-down list. Should we go with the assumption it never worked as opposed to a bug introduced with 4.2
Bruce

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: constraint violation custom field in relationship
November 25, 2012, 07:21:30 am

did u try creating a brand new relationship and attaching custom data to this new relationship?

you can enable backtrace in your debug settings and it will give the exact location of the failure

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

sibro

  • I post occasionally
  • **
  • Posts: 47
  • Karma: 2
  • CiviCRM version: 4.4.5
  • CMS version: Drupal 7.27
  • MySQL version: 5.5
  • PHP version: 5.4
Re: constraint violation custom field in relationship
November 25, 2012, 09:17:09 am
Lobo, I created a brand new relationship. It works fine.

I posted the backtrace above, looks like the problem occurs at line 9.  The entity_id is the foreign key. I need to do more investigation. Do you know of any patches near the area which may help.
Bruce

sibro

  • I post occasionally
  • **
  • Posts: 47
  • Karma: 2
  • CiviCRM version: 4.4.5
  • CMS version: Drupal 7.27
  • MySQL version: 5.5
  • PHP version: 5.4
Re: constraint violation custom field in relationship
November 25, 2012, 05:51:21 pm
I found the reason for the foreign key violation, don't understand how it got this way.

I found a series of tables civicrm_relationship and civicrm_relationship_sav3
The foreign key is id is from civicrm_relationship_sav3, but the id is in civicrm_relationship not in 3

Why are there multiple tables. Why is the foreign key looking at sav3

If I change the foreign keys manually to point to civicrm_relationship which tables do I need to update

Bruce

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: constraint violation custom field in relationship
November 25, 2012, 06:32:11 pm

1. how were the relationships batch loaded? seems like you should fix that script and reload the relationships

2. All custom tables have an entity_id which is an FK to the entry in the main table. Thus in your case civicrm_relationship_sav3 has an FK to civicrm_relationship

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
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM (Moderator: Dave Greenberg) »
  • constraint violation custom field in relationship

This forum was archived on 2017-11-26.