[solved]Hard-coded location_type_id's in upgrade script => constraint violations

[myles]

[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.

[Coleman Watts]

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

[myles]

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]

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.

[myles]

Agh, nice one Dave -

Thanks for following through and enlightening me.