CiviCRM Community Forums (archive)

Every time  I try to make a contribution I get the following error. I tried everything I saw recommended for others with foreign key errors, but nothing works.
 Logging out/in, cleared cache, trying it as an anonymous user from various computers, truncated drupal sessions...

Can someone help me understand what's causing this.
Sorry. A non-recoverable error has occurred.
DB Error: constraint violation

Database Error Code: Cannot add or update a child row: a foreign key constraint fails (`microbicides`.`civicrm_value_exhibitor_s_application_4`, CONSTRAINT `FK_civicrm_value_exhibitor_s_application_4_entity_id` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_contact` (`id`) ON DEL), 1452

Return to home page.

Error Details:
<code>
Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

=> -3
    [message] => DB Error: constraint violation
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_value_exhibitor_s_application_4  ( payment_method_27,description_of_products_and_serv_26,additional_notes_28,entity_id ) VALUES ( 'wire transfer','','',50 ) ON DUPLICATE KEY UPDATE payment_method_27 = 'wire transfer',description_of_products_and_serv_26 = '',additional_notes_28 = '' [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`microbicides`.`civicrm_value_exhibitor_s_application_4`, CONSTRAINT `FK_civicrm_value_exhibitor_s_application_4_entity_id` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_contact` (`id`) ON DEL)]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_value_exhibitor_s_application_4  ( payment_method_27,description_of_products_and_serv_26,additional_notes_28,entity_id ) VALUES ( 'wire transfer','','',50 ) ON DUPLICATE KEY UPDATE payment_method_27 = 'wire transfer',description_of_products_and_serv_26 = '',additional_notes_28 = '' [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`microbicides`.`civicrm_value_exhibitor_s_application_4`, CONSTRAINT `FK_civicrm_value_exhibitor_s_application_4_entity_id` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_contact` (`id`) ON DEL)]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_value_exhibitor_s_application_4  ( payment_method_27,description_of_products_and_serv_26,additional_notes_28,entity_id ) VALUES ( 'wire transfer','','',50 ) ON DUPLICATE KEY UPDATE payment_method_27 = 'wire transfer',description_of_products_and_serv_26 = '',additional_notes_28 = '' [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`microbicides`.`civicrm_value_exhibitor_s_application_4`, CONSTRAINT `FK_civicrm_value_exhibitor_s_application_4_entity_id` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_contact` (`id`) ON DEL)]"]
)
</code>

There isn't a 50. There used to be, but that was deleted a while ago.

Where is that number coming from? It seems to be just auto-incremented number. I just tried it again (twice) and it was 51 and 52.

This is not done by any API calls but by civicrm directly.

Site is running most recent version of Civicrm. Any idea on why this error occurs?

So operating directly on the database, I am able to perform inserts when I use an existing contact id, but if I use something that's not there I get the same error.

What I don't get is the entity_id seems to be auto-incrementing - which means that it will eventually catch up with the contact id and not display the error.

But assuming I continue testing, the entity id will surpass the contact id and the error will happen again.

Unless! Civicrm tries to create a contact with the same id as the entity_id(which it should be doing), but it cannot since the contact ids (also auto-increment) are higher than the entity_id.

Is there a table that keeps track of where everything is (auto-increment id's that is)?

I'll try submitting profile until the entity_id catches up with profile id.

Unfortunately, time doesn't permit me to try to replicate the problem at the moment (I will once I have some time - will also be harder here because I can't look at database to see when scenario will occur).

But I am puzzled by this issue. It seems that this error should occur more often(if my understanding of what is happening is correct).

Unless there is a mechanism in place that ensures that entity_id keeps up with contact_id, this problem should occur more often - as long as contacts are deleted. I guess the only reason it wouldn't normally occur is that you might not delete numerous contacts and/or contacts may only be created by 1 method, so every time a contact is created, entity_id is incremented.

Ideally, there'd be a function like drupal's db_next_id (from version 5 - not sure what version 6 uses), that insures the entity_id is always up to date else what could happen - is what happened on my site.
Contact ids & entity_ids were in-sync until the 40's. Then all contacts in the 40's and 50's were deleted while other contacts were added (so now it's up to like 80) while entity_id stayed at 48. So now when entity_id is incremented again, it's at 49, but there is no contact 49..or 50 or....until 73.

Oh and sure enough, the error went away once the entity_id caught up to contact id (at 61). But there were 5 contacts (67-72) that were also deleted, so the error reoccurred for those 5 again but now is gone because it's caught up again.

Hmm...that's even more weird.

Based on all the errors I've seen, the entity_id has been incrementing with each error.

When I submit a profile as user 1 (which I would assume would mean user 1 is passed), the same thing happens i.e the entity_id just increments. That's the whole reason it works now. The entity_id finally caught up with the contact_id (all while I was submitting profile as contact/user 1).

The error is back (entity_id finally got to a number where there are deleted contacts). Early on, I may have emptied the table before (or after) deleting most contacts.
This is the result of query:
*************************** 1. row ***************************
       Table: civicrm_value_exhibitor_s_application_4
Create Table: CREATE TABLE `civicrm_value_exhibitor_s_application_4` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Default MySQL primary key',
  `entity_id` int(10) unsigned NOT NULL COMMENT 'Table that this extends',
  `description_of_products_and_serv_26` text COLLATE utf8_unicode_ci,
  `payment_method_27` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `additional_notes_28` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_entity_id` (`entity_id`),
  KEY `INDEX_payment_method_27` (`payment_method_27`),
  CONSTRAINT `FK_civicrm_value_exhibitor_s_application_4_entity_id` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.09 sec)

ERROR:
No query specified


Once again, I can confirm that the entity_id value is sequentia and not in line with contact_id (contact id's are in the late 100's - like 170 I think) I've looked at 5 errors in a 4 (3 from 1 user, 1 from another). entity_id started at 98, then it was 99 on next error, then 100, then 101, then 102.