Cannot upgrade from 3.1.5 to 3.2- FOREIGN KEY constraint fails

[jords]

Hi

I'm trying to upgrade my 3.1.5 site to 3.2, and the upgrade process failed with:

Code:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -3
    [message] => DB Error: constraint violation
    [mode] => 16
    [debug_info] => 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_summary,           'phone' ,1,        0, 1,     'User and User Admin Only',   0,   0, 1,   'Home Phone', 'Contact'),
   ( @uf_group_id_summary,           'phone' ,1,      0, 2,     'User and User Admin Only',   0,      0, 2,   'Home Mobile', 'Contact' ),
   ( @uf_group_id_summary, 'street_address',   1,    0, 3,   'User and User Admin Only',   0,   0, NULL,    'Primary Address',         'Contact' ),
   ( @uf_group_id_summary, 'city',   1,    0, 4,   'User and User Admin Only',   0,   0, NULL,    'City',  'Contact' ),
   ( @uf_group_id_summary, 'state_province',   1,    0, 5,   'User and User Admin Only',   0,   0, NULL,    'State',  'Contact' ),
   ( @uf_group_id_summary, 'postal_code',   1,    0, 6,   'User and User Admin Only',   0,   0, NULL,    'Postal Code',  'Contact' ),
   ( @uf_group_id_summary, 'email',   1,    0, 7,   'User and User Admin Only',   0,   0, NULL,    'Primary Email',  'Contact' ),
   ( @uf_group_id_summary, 'group',   1,    0, 8,   'User and User Admin Only',   0,   0, NULL,    'Groups',  'Contact' ),
   ( @uf_group_id_summary, 'tag',   1,    0, 9,   'User and User Admin Only',   0,   0,      NULL,    'Tags', 'Contact' ),
   ( @uf_group_id_summary,           'gender',    1,         0,  10,  'User and User Admin Only',   0,    0,  NULL,  'Gender',  'Individual' ),
   ( @uf_group_id_summary,      'birth_date',   1,         0, 11, 'User and User Admin Only',   0,   0, NULL,   'Date of Birth',     'Individual' ) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`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)]
    [type] => DB_Error
    [user_info] => 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_summary,           'phone' ,1,        0, 1,     'User and User Admin Only',   0,   0, 1,   'Home Phone', 'Contact'),
   ( @uf_group_id_summary,           'phone' ,1,      0, 2,     'User and User Admin Only',   0,      0, 2,   'Home Mobile', 'Contact' ),
   ( @uf_group_id_summary, 'street_address',   1,    0, 3,   'User and User Admin Only',   0,   0, NULL,    'Primary Address',         'Contact' ),
   ( @uf_group_id_summary, 'city',   1,    0, 4,   'User and User Admin Only',   0,   0, NULL,    'City',  'Contact' ),
   ( @uf_group_id_summary, 'state_province',   1,    0, 5,   'User and User Admin Only',   0,   0, NULL,    'State',  'Contact' ),
   ( @uf_group_id_summary, 'postal_code',   1,    0, 6,   'User and User Admin Only',   0,   0, NULL,    'Postal Code',  'Contact' ),
   ( @uf_group_id_summary, 'email',   1,    0, 7,   'User and User Admin Only',   0,   0, NULL,    'Primary Email',  'Contact' ),
   ( @uf_group_id_summary, 'group',   1,    0, 8,   'User and User Admin Only',   0,   0, NULL,    'Groups',  'Contact' ),
   ( @uf_group_id_summary, 'tag',   1,    0, 9,   'User and User Admin Only',   0,   0,      NULL,    'Tags', 'Contact' ),
   ( @uf_group_id_summary,           'gender',    1,         0,  10,  'User and User Admin Only',   0,    0,  NULL,  'Gender',  'Individual' ),
   ( @uf_group_id_summary,      'birth_date',   1,         0, 11, 'User and User Admin Only',   0,   0, NULL,   'Date of Birth',     'Individual' ) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`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)]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="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_summary,           'phone' ,1,        0, 1,     'User and User Admin Only',   0,   0, 1,   'Home Phone', 'Contact'),
   ( @uf_group_id_summary,           'phone' ,1,      0, 2,     'User and User Admin Only',   0,      0, 2,   'Home Mobile', 'Contact' ),
   ( @uf_group_id_summary, 'street_address',   1,    0, 3,   'User and User Admin Only',   0,   0, NULL,    'Primary Address',         'Contact' ),
   ( @uf_group_id_summary, 'city',   1,    0, 4,   'User and User Admin Only',   0,   0, NULL,    'City',  'Contact' ),
   ( @uf_group_id_summary, 'state_province',   1,    0, 5,   'User and User Admin Only',   0,   0, NULL,    'State',  'Contact' ),
   ( @uf_group_id_summary, 'postal_code',   1,    0, 6,   'User and User Admin Only',   0,   0, NULL,    'Postal Code',  'Contact' ),
   ( @uf_group_id_summary, 'email',   1,    0, 7,   'User and User Admin Only',   0,   0, NULL,    'Primary Email',  'Contact' ),
   ( @uf_group_id_summary, 'group',   1,    0, 8,   'User and User Admin Only',   0,   0, NULL,    'Groups',  'Contact' ),
   ( @uf_group_id_summary, 'tag',   1,    0, 9,   'User and User Admin Only',   0,   0,      NULL,    'Tags', 'Contact' ),
   ( @uf_group_id_summary,           'gender',    1,         0,  10,  'User and User Admin Only',   0,    0,  NULL,  'Gender',  'Individual' ),
   ( @uf_group_id_summary,      'birth_date',   1,         0, 11, 'User and User Admin Only',   0,   0, NULL,   'Date of Birth',     'Individual' ) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`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)]"]
)

It is trying to insert a row with location_type_id 2, but the rows in my civicrm_location_type table are:
1-home
3-old
4-work
5-billing

dlobo mentioned that this is likely a bug in civiCRM?

Thanks
Jordan[/code]

[Donald Lobo]

it looks like for phone mobile we are using location type id = 2 rather than using phone_type_id in the upgrade script. we should fix this (IMO)

lobo

[jords]

I might go find the script and fix it in order to get my site running

EDIT: Great Success! edited CRM/Upgrade/Incremental/sql/3.2.beta3.mysql.tpl and beta4 to have the home mobile inserted with location type = 1 (cheap hack and it appears to have worked, upgrade finished at least.

[Kurund Jalmi]

I think instead of hardcoding we should juts pick default site location type. We should not change location type to phone type.

Kurund