Answers.Body: <p>As of CiviCRM 4.7.27 this is still an issue on an upgrade from 4.6</p>
  
  <p>Following the reference from Chris led to this answer from Rich Lott (Artful Robot): <a href="https://civicrm.stackexchange.com/questions/18487/how-to-find-and-or-fix-missing-indices/19424#19424">How to find and/or fix missing indices?</a> which answers a potential class of similar issues, but actually gives you the specific answer to your question as the example.</p>
  
  <p>To really simplify it, here's what worked for me:</p>
  
  <ol>
  <li>Run the "fixer" from the status page. Presumably you've tried this and it gives you an error, that's why you're here.</li>
  <li><p>In mysql:</p>
  
  <p><code>CREATE UNIQUE INDEX my_temp_key ON civicrm_case_contact (case_id, contact_id);
  DROP INDEX UI_case_contact_id ON civicrm_case_contact;</code></p></li>
  <li>Run the "fixer" again - now the status page thinks you're all fixed, but you have to reverse your step 2.</li>
  <li><p>In mysql:</p>
  
  <p><code>CREATE UNIQUE INDEX UI_case_contact_id ON civicrm_case_contact (case_id, contact_id);
  DROP INDEX my_temp_key ON civicrm_case_contact;</code></p></li>
  </ol>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-27T13:53:08.090Z
Answers.LastActivityDate: 2017-10-27T13:53:08.090Z
Answers.UserDisplayName: Alan Dixon
Answers.Body: <p>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 <a href="https://github.com/civicrm/civicrm-core/pull/10908" rel="noreferrer">https://github.com/civicrm/civicrm-core/pull/10908</a> as of 4.7.25 or the rc's for 4.7.26 and 4.7.27 as of late October 2017. </p>
  
  <p>When we encounter this we have been runing the following queries in the relevant MySQL database:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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.)</p>
  
  <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-10-30T16:13:28.423Z
Answers.LastActivityDate: 2017-10-30T16:13:28.423Z
Answers.UserDisplayName: Joe Murray
Body: <p>When I run</p>
  
  <pre><code>cv ev 'print_r(CRM_Core_BAO_SchemaHandler::getMissingIndices())'
  </code></pre>
  
  <p>I get the following output:</p>
  
  <pre><code>Array
  (
      [civicrm_case_contact] =&gt; Array
          (
              [0] =&gt; Array
                  (
                      [name] =&gt; UI_case_contact_id
                      [field] =&gt; Array
                          (
                              [0] =&gt; case_id
                              [1] =&gt; contact_id
                          )
  
                      [localizable] =&gt; 
                      [unique] =&gt; 1
                      [sig] =&gt; civicrm_case_contact::1::case_id::contact_id
                  )
          )
  )
  </code></pre>
  
  <p>If I then go into db and try to delete the index in order to fix it I get an error:</p>
  
  <pre><code>mysql&gt; 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
  </code></pre>
  
  <p>Looking at the table, it appears the index is fine:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>What's required to fix this index or otherwise deal with the error message?</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-05-22T19:23:28.877Z
Comments.Text: I've been looking at this over in [CRM-20533: 'Update Indices' System Status action fails when index name already exists](https://issues.civicrm.org/jira/browse/CRM-20533). Braindump over in [chat.civicrm.org ~dev channel](https://chat.civicrm.org/civicrm/pl/9rqoqcrdgirninb9c14d5ios1y) also.
Comments.UserDisplayName: Chris Burgess
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-11-08T20:50:06.087Z
Comments.Text: There are quite a few posts with very similar titles to this one, I'd recommend you put your specific error in the title to help disambiguate it.
Comments.UserDisplayName: Alan Dixon
Tags: <upgrade>
Title: Problem fixing missing indexes after upgrade to 4.7
ThreadText: # Problem fixing missing indexes after upgrade to 4.7
  
  (Asked by: username_0 on 2017-05-22)
  
  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?
  
  **Tags:** upgrade
  
  > **Comments:**
  > **username_1**: I've been looking at this over in [CRM-20533: 'Update Indices' System Status action fails when index name already exists](https://issues.civicrm.org/jira/browse/CRM-20533). Braindump over in [chat.civicrm.org ~dev channel](https://chat.civicrm.org/civicrm/pl/9rqoqcrdgirninb9c14d5ios1y) also.  
  > **username_2**: There are quite a few posts with very similar titles to this one, I'd recommend you put your specific error in the title to help disambiguate it.  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_0 on 2017-10-30. Score: 9 | Accepted answer
  
  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 @username_2'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 by username_2 on 2017-10-27. Score: 4
  
  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?](https://civicrm.stackexchange.com/questions/18487/how-to-find-and-or-fix-missing-indices/19424#19424) 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;`
  
  
  ---
  
  