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 »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • [solved]Hard-coded location_type_id's in upgrade script => constraint violations
Pages: [1]

Author Topic: [solved]Hard-coded location_type_id's in upgrade script => constraint violations  (Read 695 times)

myles

  • I post frequently
  • ***
  • Posts: 263
  • Karma: 11
[solved]Hard-coded location_type_id's in upgrade script => constraint violations
October 15, 2014, 06:30:40 am
[Error: Upgrade DB to 4.5.alpha1: SQL]

This part of the script...

INSERT INTO `civicrm_uf_field` (`uf_group_id`, `field_name`, `is_required`, `is_reserved`, `weight`, `visibility`, `in_selector`, `is_searchable`, `location_type_id`, `label`, field_type) VALUES (@uf_group_id_honoree_individual, 'prefix_id', 0, 1, 1, 'User and User Admin Only', 0, 1, NULL, 'Individual Prefix', 'Individual'), (@uf_group_id_honoree_individual, 'first_name', 0, 1, 2, 'User and User Admin Only', 0, 1, NULL, 'First Name', 'Individual'), (@uf_group_id_honoree_individual, 'last_name', 0, 1, 3, 'User and User Admin Only', 0, 1, NULL, 'Last Name', 'Individual'), (@uf_group_id_honoree_individual, 'email', 0, 1, 4, 'User and User Admin Only', 0, 1, 1, 'Email Address', 'Individual')

Results in this error:

[nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`ablaze_civicrm`.`civicrm_uf_field`, CONSTRAINT `FK_civicrm_uf_field_location_type_id` FOREIGN KEY (`location_type_id`) REFERENCES `civicrm_location_type` (`id`) ON DELETE SET NULL)]

This is because in our database, for whatever reason, location_type_id's start at 6, rather than 1

What do you recommend? -

1) Changing our data so that location_type_id's start at 1 and all updating all FK fields and is therefore as expected by developers?
2) Changing the hard-coding in the script so that we can upgrade and forgetting about it until the next time?
3) Working out how create an update query that references the actual values in the location_type table?

Option 3 seems most communitarian but although the name column is an alternate key the first four of the records are not set as system reserved and hence the names could also be changed.

I intend to follow option 1 as that would provide some us protection for future changes by bringing us back into alignment with the rest of the community.

Having installed a fresh install (thanks Bitnami) I can see that  I just have to provide id 1 with the values from 6; 2 with 7; 3 with 8; 4 with 9; 5 with 10 -

Happily there's space in the table to add the records before changing the fields that reference them.

I just used the edit facility in phpMyAdmin to add a character to the end of the name of each location type [because they are alternate unique keys] and then the copy facility inserting the desired id each time.

Once I've done that I can list all key constraints that reference civicrm_location_type.id and identify what table and what field I need to change.

USE information_schema;
SELECT TABLE_NAME, COLUMN_NAME
FROM
  KEY_COLUMN_USAGE
  WHERE
  REFERENCED_TABLE_NAME = 'civicrm_location_type'
  AND REFERENCED_COLUMN_NAME = 'id';

reveals just two tables affected:

civicrm_mapping_field   
civicrm_uf_field

both using field location_type_id

So to change these I need to execute this query (Since there are so few I changed the values in the query rather than writing a stored procedure)

UPDATE civicrm_mapping_field
SET location_type_id = 1
WHERE location_type_id = 6;

All that remains is to delete the now redundant location type records and enjoy watching the upgrade process run through to completion.
« Last Edit: October 16, 2014, 01:41:38 am by myles »

Coleman Watts

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 2346
  • Karma: 183
  • CiviCRM version: The Bleeding Edge
  • CMS version: Various
Re: Hard-coded location_type_id's in 4.5apha upgrade script => constraint violations
October 15, 2014, 08:28:22 am
Option 3 isn't that scary :)
It's just a matter of setting a sql variable, like this:
https://github.com/civicrm/civicrm-core/pull/4369/files
Try asking your question on the new CiviCRM help site.

myles

  • I post frequently
  • ***
  • Posts: 263
  • Karma: 11
Re: [solved]Hard-coded location_type_id's in upgrade script => constraint violations
October 16, 2014, 01:44:02 am
Thanks for that link - but the scary bit is the field I would use to select on [the alternate key 'name'] could also be changed - then what!

Coleman Watts

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 2346
  • Karma: 183
  • CiviCRM version: The Bleeding Edge
  • CMS version: Various
Re: [solved]Hard-coded location_type_id's in upgrade script => constraint violations
October 16, 2014, 05:45:05 am
Dave already thought of that and came up with a more flexible solution (just grab the "primary" location id), which he's committed to 4.5.2.
Try asking your question on the new CiviCRM help site.

myles

  • I post frequently
  • ***
  • Posts: 263
  • Karma: 11
Re: [solved]Hard-coded location_type_id's in upgrade script => constraint violations
October 16, 2014, 08:19:45 am
Agh, nice one Dave -

Thanks for following through and enlightening me.

Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • [solved]Hard-coded location_type_id's in upgrade script => constraint violations

This forum was archived on 2017-11-26.