Author Topic: DB Error: constraint violation when submitting an event form (Read 8001 times)

adixon · October 04, 2012, 10:37:20 am

This problem is occurring in a D7/CiviCRM4.2 installation CiviEvent form here:

http://secure.french-future.org/civicrm/event/register?id=6&reset=1

It was originally setup for last year and worked well. I updated from CiviCRM 4.0 to 4.2 this year, along with a few small label changes, etc.

When i submit an entry at the above URL, i get the error below.

The civievent registration form includes quite a few profiles. The one giving the error is a profile that includes custom participant fields, so I suspect that's where the issue is.

Any hints here? My first guess is that it's a code issue - the custom field table for participants has a foreign key constraint with the participants table, which makes sense, but it's putting 1037 as the entity_id and I think it's pulling that from the contact id instead of the participant id. I've included the backtrace below as well.

Code: [Select]

Database Error Code: Cannot add or update a child row: a foreign key constraint fails (`civicrm_fftf_org`.`civicrm_value_contest_submission_4`, CONSTRAINT `FK_civicrm_value_contest_submission_4_entity_id` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_participant` (`id`) ON DELET), 1452
Additional 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_contest_submission_4  ( school_year_36,category_18,first_prize_choice_19,second_prize_choice_20,third_prize_choice_21,fourth_prize_choice_29,fifth_prize_choice_30,sixth_prize_choice_31,seventh_prize_choice_32,eighth_prize_choice_37,ninth_prize_choice_46,tenth_prize_choice_47,eleventh_prize_choice_48,twelfth_prize_choice_49,thirteenth_prize_choice_50,how_did_you_learn_about_the_cont_33,how_did_you_learn_about_the_cont_28,essay_title_25,your_essay_22,i_affirm_35,entity_id ) VALUES ( '12','Français langue seconde','12 000 $ - U Ottawa','10 000 $ - U Sainte-Anne','10 000 $ - U Moncton','None','None','None','None','None','None','None','None','None','None','Friend','','blah','blah, blah',1,1034 ) ON DUPLICATE KEY UPDATE school_year_36 = '12',category_18 = 'Français langue seconde',first_prize_choice_19 = '12 000 $ - U Ottawa',second_prize_choice_20 = '10 000 $ - U Sainte-Anne',third_prize_choice_21 = '10 000 $ - U Moncton',fourth_prize_choice_29 = 'None',fifth_prize_choice_30 = 'None',sixth_prize_choice_31 = 'None',seventh_prize_choice_32 = 'None',eighth_prize_choice_37 = 'None',ninth_prize_choice_46 = 'None',tenth_prize_choice_47 = 'None',eleventh_prize_choice_48 = 'None',twelfth_prize_choice_49 = 'None',thirteenth_prize_choice_50 = 'None',how_did_you_learn_about_the_cont_33 = 'Friend',how_did_you_learn_about_the_cont_28 = '',essay_title_25 = 'blah',your_essay_22 = 'blah, blah',i_affirm_35 = 1 [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm_fftf_org`.`civicrm_value_contest_submission_4`, CONSTRAINT `FK_civicrm_value_contest_submission_4_entity_id` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_participant` (`id`) ON DELET)]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_value_contest_submission_4  ( school_year_36,category_18,first_prize_choice_19,second_prize_choice_20,third_prize_choice_21,fourth_prize_choice_29,fifth_prize_choice_30,sixth_prize_choice_31,seventh_prize_choice_32,eighth_prize_choice_37,ninth_prize_choice_46,tenth_prize_choice_47,eleventh_prize_choice_48,twelfth_prize_choice_49,thirteenth_prize_choice_50,how_did_you_learn_about_the_cont_33,how_did_you_learn_about_the_cont_28,essay_title_25,your_essay_22,i_affirm_35,entity_id ) VALUES ( '12','Français langue seconde','12 000 $ - U Ottawa','10 000 $ - U Sainte-Anne','10 000 $ - U Moncton','None','None','None','None','None','None','None','None','None','None','Friend','','blah','blah, blah',1,1034 ) ON DUPLICATE KEY UPDATE school_year_36 = '12',category_18 = 'Français langue seconde',first_prize_choice_19 = '12 000 $ - U Ottawa',second_prize_choice_20 = '10 000 $ - U Sainte-Anne',third_prize_choice_21 = '10 000 $ - U Moncton',fourth_prize_choice_29 = 'None',fifth_prize_choice_30 = 'None',sixth_prize_choice_31 = 'None',seventh_prize_choice_32 = 'None',eighth_prize_choice_37 = 'None',ninth_prize_choice_46 = 'None',tenth_prize_choice_47 = 'None',eleventh_prize_choice_48 = 'None',twelfth_prize_choice_49 = 'None',thirteenth_prize_choice_50 = 'None',how_did_you_learn_about_the_cont_33 = 'Friend',how_did_you_learn_about_the_cont_28 = '',essay_title_25 = 'blah',your_essay_22 = 'blah, blah',i_affirm_35 = 1 [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm_fftf_org`.`civicrm_value_contest_submission_4`, CONSTRAINT `FK_civicrm_value_contest_submission_4_entity_id` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_participant` (`id`) ON DELET)]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_value_contest_submission_4  ( school_year_36,category_18,first_prize_choice_19,second_prize_choice_20,third_prize_choice_21,fourth_prize_choice_29,fifth_prize_choice_30,sixth_prize_choice_31,seventh_prize_choice_32,eighth_prize_choice_37,ninth_prize_choice_46,tenth_prize_choice_47,eleventh_prize_choice_48,twelfth_prize_choice_49,thirteenth_prize_choice_50,how_did_you_learn_about_the_cont_33,how_did_you_learn_about_the_cont_28,essay_title_25,your_essay_22,i_affirm_35,entity_id ) VALUES ( '12','Français langue seconde','12 000 $ - U Ottawa','10 000 $ - U Sainte-Anne','10 000 $ - U Moncton','None','None','None','None','None','None','None','None','None','None','Friend','','blah','blah, blah',1,1034 ) ON DUPLICATE KEY UPDATE school_year_36 = '12',category_18 = 'Français langue seconde',first_prize_choice_19 = '12 000 $ - U Ottawa',second_prize_choice_20 = '10 000 $ - U Sainte-Anne',third_prize_choice_21 = '10 000 $ - U Moncton',fourth_prize_choice_29 = 'None',fifth_prize_choice_30 = 'None',sixth_prize_choice_31 = 'None',seventh_prize_choice_32 = 'None',eighth_prize_choice_37 = 'None',ninth_prize_choice_46 = 'None',tenth_prize_choice_47 = 'None',eleventh_prize_choice_48 = 'None',twelfth_prize_choice_49 = 'None',thirteenth_prize_choice_50 = 'None',how_did_you_learn_about_the_cont_33 = 'Friend',how_did_you_learn_about_the_cont_28 = '',essay_title_25 = 'blah',your_essay_22 = 'blah, blah',i_affirm_35 = 1 [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm_fftf_org`.`civicrm_value_contest_submission_4`, CONSTRAINT `FK_civicrm_value_contest_submission_4_entity_id` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_participant` (`id`) ON DELET)]"]
)

Code: [Select]

#0 /var/www/civicrm/4.2/CRM/Core/Error.php(148): CRM_Core_Error::backtrace()
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/www/civicrm/4.2/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /var/www/civicrm/4.2/packages/DB.php(968): PEAR_Error->PEAR_Error("DB Error: constraint violation", -3, 16, (Array:2), "INSERT INTO civicrm_value_contest_submission_4  ( school_year_36,category_18,...")
#4 /var/www/civicrm/4.2/packages/PEAR.php(564): DB_Error->DB_Error(-3, 16, (Array:2), "INSERT INTO civicrm_value_contest_submission_4  ( school_year_36,category_18,...")
#5 /var/www/civicrm/4.2/packages/DB/common.php(1903): PEAR->raiseError(NULL, -3, NULL, NULL, "INSERT INTO civicrm_value_contest_submission_4  ( school_year_36,category_18,...", "DB_Error", TRUE)
#6 /var/www/civicrm/4.2/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 (`ci...")
#7 /var/www/civicrm/4.2/packages/DB/mysql.php(327): DB_mysql->mysqlRaiseError()
#8 /var/www/civicrm/4.2/packages/DB/common.php(1216): DB_mysql->simpleQuery("INSERT INTO civicrm_value_contest_submission_4  ( school_year_36,category_18,...")
#9 /var/www/civicrm/4.2/packages/DB/DataObject.php(2421): DB_common->query("INSERT INTO civicrm_value_contest_submission_4  ( school_year_36,category_18,...")
#10 /var/www/civicrm/4.2/packages/DB/DataObject.php(1613): DB_DataObject->_query("INSERT INTO civicrm_value_contest_submission_4  ( school_year_36,category_18,...")
#11 /var/www/civicrm/4.2/CRM/Core/DAO.php(155): DB_DataObject->query("INSERT INTO civicrm_value_contest_submission_4  ( school_year_36,category_18,...")
#12 /var/www/civicrm/4.2/CRM/Core/DAO.php(922): CRM_Core_DAO->query("INSERT INTO civicrm_value_contest_submission_4  ( school_year_36,category_18,...", TRUE)
#13 /var/www/civicrm/4.2/CRM/Core/BAO/CustomValueTable.php(236): CRM_Core_DAO::executeQuery("INSERT INTO civicrm_value_contest_submission_4  ( school_year_36,category_18,...", (Array:21))
#14 /var/www/civicrm/4.2/CRM/Core/BAO/CustomValueTable.php(340): CRM_Core_BAO_CustomValueTable::create((Array:4))
#15 /var/www/civicrm/4.2/CRM/Contact/BAO/Contact.php(387): CRM_Core_BAO_CustomValueTable::store((Array:38), "civicrm_contact", 1033)
#16 /var/www/civicrm/4.2/CRM/Contact/BAO/Contact.php(1639): CRM_Contact_BAO_Contact::create((Array:31))
#17 /var/www/civicrm/4.2/CRM/Event/Form/Registration/Confirm.php(1098): CRM_Contact_BAO_Contact::createProfileContact((Array:67), (Array:52), NULL, (Array:0), NULL, NULL, TRUE)
#18 /var/www/civicrm/4.2/CRM/Event/Form/Registration/Register.php(1256): CRM_Event_Form_Registration_Confirm->updateContactFields(NULL, (Array:64), (Array:52))
#19 /var/www/civicrm/4.2/CRM/Event/Form/Registration/Register.php(1175): CRM_Event_Form_Registration_Register->processRegistration((Array:1))
#20 /var/www/civicrm/4.2/CRM/Core/Form.php(246): CRM_Event_Form_Registration_Register->postProcess()
#21 /var/www/civicrm/4.2/CRM/Core/QuickForm/Action/Upload.php(151): CRM_Core_Form->mainProcess()
#22 /var/www/civicrm/4.2/CRM/Core/QuickForm/Action/Upload.php(128): CRM_Core_QuickForm_Action_Upload->realPerform(Object(CRM_Event_Form_Registration_Register), "upload")
#23 /var/www/civicrm/4.2/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Upload->perform(Object(CRM_Event_Form_Registration_Register), "upload")
#24 /var/www/civicrm/4.2/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Event_Form_Registration_Register), "upload")
#25 /var/www/civicrm/4.2/CRM/Core/Controller.php(289): HTML_QuickForm_Page->handle("upload")
#26 /var/www/civicrm/4.2/CRM/Core/Invoke.php(228): CRM_Core_Controller->run((Array:3), NULL)
#27 /var/www/civicrm/4.2/drupal/civicrm.module(500): CRM_Core_Invoke::invoke((Array:3))
#28 [internal function](): civicrm_invoke("event", "register")
#29 /var/www/drupal/git-7/includes/menu.inc(516): call_user_func_array("civicrm_invoke", (Array:2))
#30 /var/www/drupal/git-7/index.php(21): menu_execute_active_handler()
#31 {main}

adixon · October 04, 2012, 10:59:24 am

I'm looking at this file, line 387, that you can see in the backtrace.

/CRM/Contact/BAO/Contact.php

which is:

Code: [Select]

CRM_Core_BAO_CustomValueTable::store($params['custom'], 'civicrm_contact', $contact->id);

I think that means it's assuming that all these custom fields are contact fields, so it'll fail for participant custom fields.

I've just looked at the contact object at that point and it doesn't have the participant fields yet, so i'll have to go back in the stack a bit more to see where it can get repaired ...

adixon · October 04, 2012, 11:21:23 am

Okay, i think I need help now ...

1. That line 387 in /CRM/Contact/BAO/Contact.php is an invalid call. There are two ways of dealing with this:

a. look through params['custom'] and remove the non-contact custom fields before this call.
b. fix the "store" function to do that filtering.

I'd think both of these might be a good idea, but I wonder if someone can point me easily to function that can tell me what kind of entity a given custom table is? I can look them all up in the civicrm_uf_field table, but I expect there's a more efficient way.

2. It appears that line 1098 in /CRM/Event/Form/Registration/Confirm.php is where the contact is being created with it's custom fields. Any hints about where the participant object/record is being created, and if those custom field params are being passed there as well?

Donald Lobo · October 04, 2012, 12:30:48 pm

hey alan:

did that custom group change owners at any time? i.e. was it extending contact and then switched to extending particpant? we've seen a few reports of wierd things happening when the above has happened

the code should deal with contact + participant regular and custom fields quite easily and cleanly. we do that a fair bit for all events on civicrm.org (where most of the custom fields are linked to the participnt)

lobo

adixon · October 04, 2012, 02:19:34 pm

re: custom group changing owners - I don't think so, but I also don't understand the question. If you mean, did it switch from being a contact to a a participant group, then no.

BUT, i do have a report that this was working a few days ago, and I'll i've done since then [i thought] was some translations [it's a multilingual site]. I don't know if that's a hint.

Where/how in the code does it split up the custom field groups by entity type?

Donald Lobo · October 04, 2012, 04:55:12 pm

hey alan, yes you answered what i meant

I dont know where exactly the custom fields are split. i'll try taking a look and do a code review of it later tonite

lobo

Eileen · October 04, 2012, 06:32:01 pm

+1

(ie looking at an issue that may be the same problem. Site is on 4.2 & is multilingual. )

Eileen · October 04, 2012, 11:27:56 pm

OK - I took a look at this & I can confirm that there is nothing odd about the field & clearing caches doesn't fix it.

The problem is wider spread than it first appears as it is 'working' for most users - but the data is being stored incorrectly. ie. there are more participant records than contact records in the database so for most contacts having the 2 ids transposed doesn't cause a fatal error.

I struggled (failed) to debug this because I couldn't see any reason why it would ever work. ie. as per Allan's backtrace CRM_Core_BAO_CustomValueTable::store is called with the entity_table set to civicrm_contact. I can't see any reason why being called with this function it would suddenly realise it should be saving with participant id. Conversely, I can't see any reason why the code leading up to that point would call it in any other way.

#15 /var/www/civicrm/4.2/CRM/Contact/BAO/Contact.php(387): CRM_Core_BAO_CustomValueTable::store((Array:38), "civicrm_contact", 1033)

Eileen · October 04, 2012, 11:34:34 pm

I have logged this in JIRA
http://issues.civicrm.org/jira/browse/CRM-10979

adixon · October 05, 2012, 05:24:29 am

Eileen: happy to see my results corroborated! My only thinking about that line #15 in the code is that it's in the Contact.php object at that point, so that code can't make any assumptions about it also being a participant. In other words, that line is probably okay, and it's earlier on (#17, registration confirm) where the registration code should be splitting up the custom fields before branching off to create/save the user and participant records [presumably the user first? not sure ..]. On the other hand, some kind of sanity check or filter of the various custom fields at line #15 is probably a good idea to avoid errors like this.

I'd note that the site did work well last year, when it already had those participant custom fields, in D7/CiviCRM4.0. So either: it was an accident that it worked [the contact id and participant id stayed in parallell?], or it didn't work and nobody noticed, or the code has been broken since then. If it's helpful, i could resurrect last year's site and take a closer look.

As an aside - on a different site, I recently had to do a bunch of custom work to make use of contribution custom fields, which don't seem to be very accessible from public forms [e.g. you can't include contribution profiles on event pages]. Is that just a project waiting to happen or am I designing my site wrong [e.g. i should put those fields into a participant custom field?].

Donald Lobo · October 05, 2012, 07:53:23 am

is this only on multilingual sites? or also on uni-lingual sites?

seems like a 4.2 issue

i'll try reproducing for a uni-lingual site and checking later today

lobo

adixon · October 05, 2012, 08:47:23 am

So far, only identified on multilingual sites, but I haven't tested on non-multilingual sites.

Donald Lobo · October 05, 2012, 11:23:31 am

tried this on a clean sample DB (and deepaks tried it also) and it seemed to work as expected. The custom fields had the right participant entity ID and not the contact entity ID

i'll continue looking. If u'll can reproduce on a test server, get on irc and we can instrument the code and figure out what is happening and what gets saved and when

lobo

SarahG (FountainTribe) · October 05, 2012, 11:42:00 am

This issue seems to be similar to the issue I am having with contributions, which is described at: http://forum.civicrm.org/index.php/topic,26305.0.html

Donald Lobo · October 05, 2012, 11:45:21 am

i switched the db to multi-lingual and it still worked.

so i have no idea what is happening and why. eileen/alan: ping me on irc when convenient

lobo

CiviCRM Community Forums (archive)

News:

Author Topic: DB Error: constraint violation when submitting an event form (Read 8001 times)

adixon

DB Error: constraint violation when submitting an event form

adixon

Re: DB Error: constraint violation when submitting an event form

adixon

Re: DB Error: constraint violation when submitting an event form

Donald Lobo

Re: DB Error: constraint violation when submitting an event form

adixon

Re: DB Error: constraint violation when submitting an event form

Donald Lobo

Re: DB Error: constraint violation when submitting an event form

Eileen

Re: DB Error: constraint violation when submitting an event form

Eileen

Re: DB Error: constraint violation when submitting an event form

Eileen

Re: DB Error: constraint violation when submitting an event form

adixon

Re: DB Error: constraint violation when submitting an event form

Donald Lobo

Re: DB Error: constraint violation when submitting an event form

adixon

Re: DB Error: constraint violation when submitting an event form

Donald Lobo

Re: DB Error: constraint violation when submitting an event form

SarahG (FountainTribe)

Re: DB Error: constraint violation when submitting an event form

Donald Lobo

Re: DB Error: constraint violation when submitting an event form