Problem fixing missing indexes after upgrade to 4.7

Question

When I run

cv ev 'print_r(CRM_Core_BAO_SchemaHandler::getMissingIndices())'

I get the following output:

Array
(
    [civicrm_case_contact] => Array
        (
            [0] => Array
                (
                    [name] => UI_case_contact_id
                    [field] => Array
                        (
                            [0] => case_id
                            [1] => contact_id
                        )

                    [localizable] => 
                    [unique] => 1
                    [sig] => civicrm_case_contact::1::case_id::contact_id
                )
        )
)

If I then go into db and try to delete the index in order to fix it I get an error:

mysql> alter table civicrm_case_contact drop index UI_case_contact_id;
ERROR 1553 (HY000): Cannot drop index 'UI_case_contact_id': needed in a foreign key constraint

Looking at the table, it appears the index is fine:

civicrm_case_contact | CREATE TABLE `civicrm_case_contact` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique case-contact association id',
  `case_id` int(10) unsigned NOT NULL COMMENT 'Case ID of case-contact association.',
  `contact_id` int(10) unsigned NOT NULL COMMENT 'Contact ID of contact record given case belongs to.',
  PRIMARY KEY (`id`),
  KEY `UI_case_contact_id` (`case_id`,`contact_id`),
  KEY `FK_civicrm_case_contact_contact_id` (`contact_id`),
  CONSTRAINT `FK_civicrm_case_contact_case_id` FOREIGN KEY (`case_id`) REFERENCES `civicrm_case` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_civicrm_case_contact_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

What's required to fix this index or otherwise deal with the error message?

Answer 1

We have found that there are problems with both civicrm_option_value and civicrm_case_contact table indexes that have not been properly fixed by https://github.com/civicrm/civicrm-core/pull/10908 as of 4.7.25 or the rc's for 4.7.26 and 4.7.27 as of late October 2017.

When we encounter this we have been runing the following queries in the relevant MySQL database:

ALTER table civicrm_option_value DROP FOREIGN KEY  FK_civicrm_option_value_option_group_id;
ALTER table civicrm_option_value DROP INDEX index_option_group_id_name;
ALTER table civicrm_option_value ADD CONSTRAINT `FK_civicrm_option_value_option_group_id` FOREIGN KEY (`option_group_id`) REFERENCES `civicrm_option_group`(`id`) ON DELETE CASCADE;

ALTER table civicrm_case_contact DROP FOREIGN KEY FK_civicrm_case_contact_contact_id;
ALTER table civicrm_case_contact DROP INDEX FK_civicrm_case_contact_contact_id;
ALTER table civicrm_case_contact DROP FOREIGN KEY FK_civicrm_case_contact_case_id;
ALTER table civicrm_case_contact DROP INDEX UI_case_contact_id;
ALTER TABLE `civicrm_case_contact`
  ADD CONSTRAINT `FK_civicrm_case_contact_case_id` FOREIGN KEY (`case_id`) REFERENCES `civicrm_case` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `FK_civicrm_case_contact_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE;

After running these queries, we go to the Admin Status Console (civicrm/a/#/status) and click the button that calls the UpdateIndices api, which rebuilds the indexes properly. (In other words, we have not found that @Alan Dixon's step 4 is required after step 3.)

There are reports that some people have duplicate entries in civicrm_case_contact table on case_id and contact_id. You'll need to delete these duplicates.

Answer 2

As of CiviCRM 4.7.27 this is still an issue on an upgrade from 4.6

Following the reference from Chris led to this answer from Rich Lott (Artful Robot): How to find and/or fix missing indices? which answers a potential class of similar issues, but actually gives you the specific answer to your question as the example.

To really simplify it, here's what worked for me:

1. Run the "fixer" from the status page. Presumably you've tried this and it gives you an error, that's why you're here.

2. In mysql:

   CREATE UNIQUE INDEX my_temp_key ON civicrm_case_contact (case_id, contact_id); DROP INDEX UI_case_contact_id ON civicrm_case_contact;

3. Run the "fixer" again - now the status page thinks you're all fixed, but you have to reverse your step 2.

4. In mysql:

   CREATE UNIQUE INDEX UI_case_contact_id ON civicrm_case_contact (case_id, contact_id); DROP INDEX my_temp_key ON civicrm_case_contact;