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 username_1 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;`
  
  
  ---
  
  