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 »
  • Post-installation Setup and Configuration (Moderator: Dave Greenberg) »
  • Can't updata data: DB Error: constraint violation
Pages: [1]

Author Topic: Can't updata data: DB Error: constraint violation  (Read 928 times)

databell

  • I post occasionally
  • **
  • Posts: 32
  • Karma: -1
  • CiviCRM version: 4.5
  • CMS version: WordPress 4.0.1
  • MySQL version: 5.5.37-cll
  • PHP version: 5.3.25
Can't updata data: DB Error: constraint violation
December 27, 2014, 11:47:54 am
I just imported my database from one DB to another on a WordPress install. Seemed successful but still issues persist. I turned on the Disable foreign key checks function when I exported the database which was explained in the instructions for using PHPmyAdmin.

Now, I just wanted to update the Organization Address and Contact info, since that didn't make the upgrade, and I found the following error when I clicked 'Save':

Code: [Select]
Database Error Code: Cannot add or update a child row: a foreign key constraint fails (`aishla9_civi`.`civicrm_log`, CONSTRAINT `FK_civicrm_log_modified_id` FOREIGN KEY (`modified_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE), 1452
What? Why did this happen and how do you fix it?

Hershel

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4640
  • Karma: 176
    • CiviHosting
  • CiviCRM version: Latest
  • CMS version: Mostly WordPress and Drupal
Re: Can't updata data: DB Error: constraint violation
December 28, 2014, 07:45:29 am
It appears that the issue is that the contact you are editing no longer exists. I'm guessing that your default contact may have been deleted?
CiviHosting and CiviOnline -- The CiviCRM hosting experts, since 2007

See here for the official: What to do if you think you've found a bug.

databell

  • I post occasionally
  • **
  • Posts: 32
  • Karma: -1
  • CiviCRM version: 4.5
  • CMS version: WordPress 4.0.1
  • MySQL version: 5.5.37-cll
  • PHP version: 5.3.25
Re: Can't updata data: DB Error: constraint violation
December 28, 2014, 10:09:10 am
Seems that was deleted or never made the import. However, I don't know how much that really validates everything as you'll see in the example here, there is some data showing up. So it's not completely empty.

Sorry about pixelating a lot of the data. Just wanted to protect any chance of scooping up data.

Where is this record/table located in the DB?

JonGold

  • Ask me questions
  • ****
  • Posts: 638
  • Karma: 81
    • Palante Technology
  • CiviCRM version: 4.1 to the latest
  • CMS version: Drupal 6-7, Wordpress 4.0+
  • PHP version: PHP 5.3-5.5
Re: Can't updata data: DB Error: constraint violation
December 28, 2014, 10:15:13 am
On Civi 4.5, the default organization is also a contact record, stored in the civicrm_contact table.  If you installed directly to Civi 4.5, it's contact ID 1 (or 2? Pretty sure it's 1).  If you upgraded from an earlier version, it may be a different record.
Sign up to StackExchange and get free expert CiviCRM advice: https://civicrm.org/blogs/colemanw/get-exclusive-access-free-expert-help

databell

  • I post occasionally
  • **
  • Posts: 32
  • Karma: -1
  • CiviCRM version: 4.5
  • CMS version: WordPress 4.0.1
  • MySQL version: 5.5.37-cll
  • PHP version: 5.3.25
Re: Can't updata data: DB Error: constraint violation
December 28, 2014, 11:13:16 am
Yes, I'm in rhe civicrm_contact table and there's no 1 or 2. It starts out at 102 and works its way down.

Furthermore, I search for my organization_name and could not find it. That's peculiar.

JonGold

  • Ask me questions
  • ****
  • Posts: 638
  • Karma: 81
    • Palante Technology
  • CiviCRM version: 4.1 to the latest
  • CMS version: Drupal 6-7, Wordpress 4.0+
  • PHP version: PHP 5.3-5.5
Re: Can't updata data: DB Error: constraint violation
December 28, 2014, 01:24:39 pm
It sounds like something didn't come over successfully from the old server - maybe contact ID 1 is in civicrm_contact on the old server?
Sign up to StackExchange and get free expert CiviCRM advice: https://civicrm.org/blogs/colemanw/get-exclusive-access-free-expert-help

databell

  • I post occasionally
  • **
  • Posts: 32
  • Karma: -1
  • CiviCRM version: 4.5
  • CMS version: WordPress 4.0.1
  • MySQL version: 5.5.37-cll
  • PHP version: 5.3.25
Re: Can't updata data: DB Error: constraint violation
December 28, 2014, 02:27:15 pm
On one database dump, no. But another one, I think I found it. You tell me if this is right and I should just dump it in.

NOTE: 'Name of Company' replaces actual name of company.

Code: [Select]
INSERT INTO `civicrm_contact` (`id`, `contact_type`, `contact_sub_type`, `do_not_email`, `do_not_phone`, `do_not_mail`, `do_not_sms`, `do_not_trade`, `is_opt_out`, `legal_identifier`, `external_identifier`, `sort_name`, `display_name`, `nick_name`, `legal_name`, `image_URL`, `preferred_communication_method`, `preferred_language`, `preferred_mail_format`, `hash`, `api_key`, `source`, `first_name`, `middle_name`, `last_name`, `prefix_id`, `suffix_id`, `formal_title`, `communication_style_id`, `email_greeting_id`, `email_greeting_custom`, `email_greeting_display`, `postal_greeting_id`, `postal_greeting_custom`, `postal_greeting_display`, `addressee_id`, `addressee_custom`, `addressee_display`, `job_title`, `gender_id`, `birth_date`, `is_deceased`, `deceased_date`, `household_name`, `primary_contact_id`, `organization_name`, `sic_code`, `user_unique_id`, `employer_id`, `is_deleted`, `created_date`, `modified_date`) VALUES
(1, 'Organization', NULL, 0, 0, 0, 0, 0, 0, NULL, NULL, 'Name of Company', 'Name of Company', NULL, 'Name of Company', NULL, NULL, NULL, 'Both', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, 'Name of Company', NULL, NULL, NULL, 0, NULL, '2014-12-25 23:08:56'),

JonGold

  • Ask me questions
  • ****
  • Posts: 638
  • Karma: 81
    • Palante Technology
  • CiviCRM version: 4.1 to the latest
  • CMS version: Drupal 6-7, Wordpress 4.0+
  • PHP version: PHP 5.3-5.5
Re: Can't updata data: DB Error: constraint violation
December 28, 2014, 02:32:29 pm
This looks vaguely right, but it's hard for me to parse the raw SQL.  I recommend taking a backup of the database, importing it, and seeing if it clears up the issue.
Sign up to StackExchange and get free expert CiviCRM advice: https://civicrm.org/blogs/colemanw/get-exclusive-access-free-expert-help

JonGold

  • Ask me questions
  • ****
  • Posts: 638
  • Karma: 81
    • Palante Technology
  • CiviCRM version: 4.1 to the latest
  • CMS version: Drupal 6-7, Wordpress 4.0+
  • PHP version: PHP 5.3-5.5
Re: Can't updata data: DB Error: constraint violation
December 28, 2014, 02:33:20 pm
I'll mention my concern though - if there's no good explanation for why this record is missing, it's very possible that you have other data missing.  It would suck to fix this problem just to encounter another one, and another one, all over some bad data.
Sign up to StackExchange and get free expert CiviCRM advice: https://civicrm.org/blogs/colemanw/get-exclusive-access-free-expert-help

databell

  • I post occasionally
  • **
  • Posts: 32
  • Karma: -1
  • CiviCRM version: 4.5
  • CMS version: WordPress 4.0.1
  • MySQL version: 5.5.37-cll
  • PHP version: 5.3.25
Re: Can't updata data: DB Error: constraint violation
December 28, 2014, 03:31:04 pm
Well, let me try this. I only need to import that one record. Not an entire DB or even an entire table. I'll back up the DB first and then import the SQL for this one record and see what happens. Hope this works.

databell

  • I post occasionally
  • **
  • Posts: 32
  • Karma: -1
  • CiviCRM version: 4.5
  • CMS version: WordPress 4.0.1
  • MySQL version: 5.5.37-cll
  • PHP version: 5.3.25
Re: Can't updata data: DB Error: constraint violation
December 28, 2014, 03:45:55 pm
It worked! I placed in the record and while the data in the 'Organization Address and Contact Info' area stayed the same, I then entered the new data and it saved. Thank you!

Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Post-installation Setup and Configuration (Moderator: Dave Greenberg) »
  • Can't updata data: DB Error: constraint violation

This forum was archived on 2017-11-26.