Author Topic: Cannot add or update a child row: a foreign key constraint fails, 1216 (Read 9502 times)

keokee · December 05, 2008, 11:17:16 am

I am getting the following error when a new member is trying to sign-up for a membership. This is new as it used to work without a problem:

Code: [Select]

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_membership (contact_id , membership_type_id , join_date , start_date , end_date , source , is_override , reminder_date , is_test ) VALUES ( 1 ,  6 ,  20081205 ,  20090101 ,  20091231 , 'Online Contribution: LPOIC Membership Form' ,  0 ,  20091130 ,  1 )  [nativecode=1216 ** Cannot add or update a child row: a foreign key constraint fails]
    [type] => db_error
    [user_info] => INSERT INTO civicrm_membership (contact_id , membership_type_id , join_date , start_date , end_date , source , is_override , reminder_date , is_test ) VALUES ( 1 ,  6 ,  20081205 ,  20090101 ,  20091231 , 'Online Contribution: LPOIC Membership Form' ,  0 ,  20091130 ,  1 )  [nativecode=1216 ** Cannot add or update a child row: a foreign key constraint fails]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_membership (contact_id , membership_type_id , join_date , start_date , end_date , source , is_override , reminder_date , is_test ) VALUES ( 1 ,  6 ,  20081205 ,  20090101 ,  20091231 , 'Online Contribution: LPOIC Membership Form' ,  0 ,  20091130 ,  1 )  [nativecode=1216 ** Cannot add or update a child row: a foreign key constraint fails]"]
)

Any help is appreciated.

[/code]

Dave Greenberg · December 05, 2008, 12:24:11 pm

Any chance that you deleted or disabled a Membership Type (id = 6). The error is complaining about a foreign key problem and there's only the membership_type_id and the contact_id columns in that query that are foreign keys.

Also a bit curious that it's trying to add membership for contact_id = 1. Is this from a test you're doing using that contact?

keokee · December 05, 2008, 12:45:47 pm

Yes, I did rename one membership type and added another and changed some membership prices.

The error is from a test-drive of the contribution page. But, visitors who were trying to sign up on the live page were getting this problem, too. However, the error seems to be different. The live page is at https://lpoic.org/index.php?option=com_civicrm&Itemid=87
Here is a sample of the error code:

Code: [Select]

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_membership_log (membership_id , start_date , end_date , modified_id , modified_date , renewal_reminder_date ) VALUES ( 272 ,  20070101 ,  20071231 ,  275 ,  20081205 ,  NULL )  [nativecode=1216 ** Cannot add or update a child row: a foreign key constraint fails]
    [type] => db_error
    [user_info] => INSERT INTO civicrm_membership_log (membership_id , start_date , end_date , modified_id , modified_date , renewal_reminder_date ) VALUES ( 272 ,  20070101 ,  20071231 ,  275 ,  20081205 ,  NULL )  [nativecode=1216 ** Cannot add or update a child row: a foreign key constraint fails]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_membership_log (membership_id , start_date , end_date , modified_id , modified_date , renewal_reminder_date ) VALUES ( 272 ,  20070101 ,  20071231 ,  275 ,  20081205 ,  NULL )  [nativecode=1216 ** Cannot add or update a child row: a foreign key constraint fails]"]
)

[/code]

keokee · December 05, 2008, 02:11:56 pm

I created a new contribution page thinking I may be able to get around the foreign key constraints error.
However, now I get a different error using the live contribution page at https://lpoic.org/index.php?option=com_civicrm&Itemid=87 (I changed the Contribution ID to the new Contribute form).

Code: [Select]

Sorry. A non-recoverable error has occurred.
Could not find valid Key

Donald Lobo · December 05, 2008, 02:27:23 pm

you might want to think about upgrading. 1.8 is more than a year old and is not supported

did you make the changes to joomla's index.php as mentiond in the documentation: http://wiki.civicrm.org/confluence/display/CRMDOC/Displaying+Online+Contribution+Pages+in+Joomla!+Frontend+Sites (step 3)

lobo

keokee · December 05, 2008, 04:31:42 pm

I would upgrade to a newer version but we are a bit in a dilemma about it. We are maintaining this site for a non-profit organization that doesn't have the budget to upgrade. Also, our server is still on php 4 and mysql 4. And, I know higher versions of CiviCRM require php 5 and mysql 5. We'll upgrade to php 5 and mysql 5 soon. But until now I am trying to get this working again.

I am surprised how the contribution form suddenly failed to work. And, yes, I did do the changes to Joomla's index.php page. For over a year now everything used to work like a charm up until yesterday.

I'd appreciate any other suggestions you might have.

Donald Lobo · December 05, 2008, 07:00:16 pm

can you check and ensure that someone else has not done a security upgrade of joomla yesterday. seems like something must have changed for things to stop working

lobo

keokee · December 08, 2008, 10:17:12 am

I'll check.

nocabt · December 09, 2008, 10:51:54 am

We're getting an almost identical error, that just started today. Running Joomla 1.x and CiviCRM 2.0.4. Error is:

Code: [Select]

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_membership_log (membership_id , start_date , end_date , modified_id , modified_date , renewal_reminder_date ) VALUES ( 88 ,  20090909 ,  20100908 ,  321 ,  20081209 ,  20100808 )  [nativecode=1216 ** Cannot add or update a child row: a foreign key constraint fails]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_membership_log (membership_id , start_date , end_date , modified_id , modified_date , renewal_reminder_date ) VALUES ( 88 ,  20090909 ,  20100908 ,  321 ,  20081209 ,  20100808 )  [nativecode=1216 ** Cannot add or update a child row: a foreign key constraint fails]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_membership_log (membership_id , start_date , end_date , modified_id , modified_date , renewal_reminder_date ) VALUES ( 88 ,  20090909 ,  20100908 ,  321 ,  20081209 ,  20100808 )  [nativecode=1216 ** Cannot add or update a child row: a foreign key constraint fails]"]
)

I apologize if I should have created a new thread for this, it seemed very close to the original poster's problem. No one has modified any settings (that they're admitting to), or upgrades.[/code]

keokee · December 09, 2008, 10:55:28 am

Interesting to see we are not the only ones with this issue.

Has it worked for you before and it suddenly produced this error? Or, has it never worked before?

I am still figuring out how to fix this. I am so far unable to find a solution.

But, as soon as I find out I'll let you know.

nocabt · December 09, 2008, 01:34:42 pm

Yes, it worked before, it just started two days ago on a CiviCRM install that has been working fine for a few months.

keokee · December 09, 2008, 02:23:06 pm

Did you add/edit/delete any membership type in CiviMember recently, too?

keokee · December 09, 2008, 06:24:28 pm

I discovered deleted membership records would cause an MySQL error 1216 to occur if a former member attempts to create a new membership with the email address he/she used in the past. The membership record is deleted but it seems to me related records are not which causes the db to err because of foreign key constraints.

I am hoping, Lobo, this issue has occurred before and someone may have resolved it already in the past.
I am wondering if someone has maybe created a SQL statement that would fix the foreign key constraint in relationship to deleted records in the civicrm_membership table.

More help is appreciated!

CiviCRM Community Forums (archive)

News:

Author Topic: Cannot add or update a child row: a foreign key constraint fails, 1216 (Read 9502 times)

keokee

Cannot add or update a child row: a foreign key constraint fails, 1216

Dave Greenberg

Re: Cannot add or update a child row: a foreign key constraint fails, 1216

keokee

Re: Cannot add or update a child row: a foreign key constraint fails, 1216

keokee

Re: Cannot add or update a child row: a foreign key constraint fails, 1216

Donald Lobo

Re: Cannot add or update a child row: a foreign key constraint fails, 1216

keokee

Re: Cannot add or update a child row: a foreign key constraint fails, 1216

Donald Lobo

Re: Cannot add or update a child row: a foreign key constraint fails, 1216

keokee

Re: Cannot add or update a child row: a foreign key constraint fails, 1216

nocabt

Re: Cannot add or update a child row: a foreign key constraint fails, 1216

keokee

Re: Cannot add or update a child row: a foreign key constraint fails, 1216

nocabt

Re: Cannot add or update a child row: a foreign key constraint fails, 1216

keokee

Re: Cannot add or update a child row: a foreign key constraint fails, 1216

keokee

Re: Cannot add or update a child row: a foreign key constraint fails, 1216