Answers.Body: <p>This thread helped me:</p>
  
  <p><a href="https://civicrm.stackexchange.com/questions/18487/how-to-find-and-or-fix-missing-indices/18521?s=5|0.2460#18521">How to find and/or fix missing indices?</a></p>
  
  <p>I've aggregated the SQL listed there below, copy it and run it via phpMyAdmin:</p>
  
  <pre><code>ALTER TABLE civicrm_prevnext_cache DROP INDEX index_all;
  
  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_entity_tag DROP INDEX UI_entity_id_entity_table_tag_id; 
  
  ALTER TABLE civicrm_case_contact DROP FOREIGN KEY 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;
  </code></pre>
  
  <p>then click on the <strong>Update Indices</strong> button.   Then go back to phpMyAdmin and run these SQL commands:</p>
  
  <pre><code>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 ADD CONSTRAINT FK_civicrm_case_contact_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact (id) ON DELETE CASCADE;
  
  ALTER TABLE civicrm_case_contact ADD CONSTRAINT FK_civicrm_case_contact_case_id FOREIGN KEY (case_id) REFERENCES civicrm_case (id) ON DELETE CASCADE;
  </code></pre>
  
  <p>I had this issue moving from 4.7.18 to 4.7.20.   Not sure why this wasn't an issue before, I'm guessing its because I don't use the CASE module on my site.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-14T20:23:48.267Z
Answers.Text: I used these SQL commands and it seemed to work in removing the warning.   However, now I cannot create a new Civicase.   (I did use a test copy of the database.)   Has anyone tested creating a new case after this procedure?
Answers.UserDisplayName: Dave T
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-29T22:35:16.230Z
Answers.Text: I removed UPDATE civicrm_uf_group SET name = CONCAT(name, '_', id) WHERE name NOT LIKE CONCAT('%', id); from the answer because it seems wrong, has nothing to do with the issue I think and might cause problems.
Answers.UserDisplayName: Hans Rossel
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-15T10:21:16.823Z
Answers.Text: Re "I had this issue moving from 4.7.18 to 4.7.20. Not sure why this wasn't an issue before" - the indices may still have been "missing" in earlier versions, but there was no detection of that.  What was added in 4.7.19 was a check to identify missing indices and an option to add them, but the additions fail in some situations.  Although it says 'missing', it really means "index does not exist, or does not match with the expected specification"
Answers.UserDisplayName: Aidan
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-15T10:26:31.787Z
Answers.Text: If can help test proposed fixes for this issue, see https://issues.civicrm.org/jira/browse/CRM-20533
Answers.UserDisplayName: Aidan
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-16T06:56:24.737Z
Answers.Text: Was a little leery of running any of the SQL on the other thread, but Neil's instructions worked like a charm. Just one correction -- there's a typo at the end of the second command:
  `ALTER TABLE civicrm_option_value DROP FOREIGN KEY FK_civicrm_option_value_option_group_id`;`
  Removed the apostrophe between "FK_civicrm_option_value_option_group_id" and the semicolon, and it worked great. Thanks Neil!
Answers.UserDisplayName: Prónay
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-16T11:37:19.680Z
Answers.Text: I removed the apostrophe/quote from the line.
Answers.UserDisplayName: Neil Z
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-18T21:57:06.397Z
Answers.Text: I also got a list of tables missing indices going to civi 4.7.20; among them, civicrm_case_contact.contact_id. In SchemaHandler:getMissingIndices the array of required and existing indices are gathered from the schema xml, but when they're compared, for civicrm_case_contact  the string in required sigs is "civicrm_case_contact::1::case_id::contact_id" and in existing sigs it is "civicrm_case_contact::0::contact_id"  I can propose a fix; in the meantime other indices listed as missing seem to really be missing.
Answers.UserDisplayName: Lesley Evensen
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-27T14:27:50.480Z
Answers.Text: The fourth line in the solution beginning with "ALTER TABLE civicrm_entity_tag" contains a second SQL command: 
  "UPDATE civicrm_uf_group SET name = CONCAT(name, '_', id) WHERE name NOT LIKE CONCAT('%', id);"
  
  Changing the machine name can have wide repercussions, and in fact rendered my CiviCRM inoperable after I enabled CiviVolunter (https://issues.civicrm.org/jira/browse/VOL-309). 
  
  It's not clear why this was necessary to solve the index problem. Was this intentional and required? If so, after fixing the indexes, can/should the original names be restored?
Answers.UserDisplayName: BobS
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-28T18:25:53.477Z
Answers.Text: BobS .. not sure why that was in the original fix.  That said, that line caused issues with some of my searches that use built-in group ID# 1.  I changed the name back to the original and that fixed it.
Answers.UserDisplayName: Neil Z
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-06-14T20:04:03.723Z
Answers.LastActivityDate: 2017-08-31T15:28:49.573Z
Answers.LastEditDate: 2017-08-31T15:28:49.573Z
Answers.UserDisplayName: Neil Z
Body: <p>Upgraded to 4.7.20 today. As others <a href="https://civicrm.stackexchange.com/questions/18487/how-to-find-and-or-fix-missing-indices">on this thread</a> have experienced in earlier release(s), I promptly got the "missing indices on some tables" warning. Of course, I tried the "Update Indices" button, and of course, all it gave me was a spinning logo for about two seconds followed by no change in status.</p>
  
  <p>Looking at my error log, this jumped out at me:</p>
  
  <blockquote>
    <p>wp-content/plugins/civicrm/civicrm/packages/DB.php(984): PEAR_Error->__construct("DB Error: already exists", -5, 16, (Array:2), "CREATE UNIQUE INDEX UI_case_contact_id ON civicrm_case_contact (case_id, cont...")</p>
  </blockquote>
  
  <p>So I opened phpMyAdmin, and sure enough, the UI_case_contact_id index does in fact exist on the civicrm_case_contact table. Which makes me wonder if I'm getting a missing indices warning because the system "perceives" incorrectly that there's no UI_case_contact_id index when in fact there already is.</p>
  
  <p>Since I know just enough about Civi's database structure to be dangerous (but not nearly enough to competently fix anything that can go wrong with it), might anyone please enlighten me?</p>
  
Tags: <database><upgrade><missing>
Title: 4.7.20 upgrade -- missing indices warning, but index already exists?
ThreadText: # 4.7.20 upgrade -- missing indices warning, but index already exists?
  
  (Asked by: username_0 on 2017-06-14)
  
  Upgraded to 4.7.20 today. As others [on this thread](https://civicrm.stackexchange.com/questions/18487/how-to-find-and-or-fix-missing-indices) have experienced in earlier release(s), I promptly got the "missing indices on some tables" warning. Of course, I tried the "Update Indices" button, and of course, all it gave me was a spinning logo for about two seconds followed by no change in status.
  
  Looking at my error log, this jumped out at me:
  
  > wp-content/plugins/civicrm/civicrm/packages/DB.php(984): PEAR\_Error->\_\_construct("DB Error: already exists", -5, 16, (Array:2), "CREATE UNIQUE INDEX UI\_case\_contact\_id ON civicrm\_case\_contact (case\_id, cont...")
  
  So I opened phpMyAdmin, and sure enough, the UI\_case\_contact\_id index does in fact exist on the civicrm\_case\_contact table. Which makes me wonder if I'm getting a missing indices warning because the system "perceives" incorrectly that there's no UI\_case\_contact\_id index when in fact there already is.
  
  Since I know just enough about Civi's database structure to be dangerous (but not nearly enough to competently fix anything that can go wrong with it), might anyone please enlighten me?
  
  **Tags:** database, upgrade, missing
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2017-06-14. Score: 5 | Accepted answer
  
  This thread helped me:
  How to find and/or fix missing indices?
  I've aggregated the SQL listed there below, copy it and run it via phpMyAdmin:
  ALTER TABLE civicrm_prevnext_cache DROP INDEX index_all;
  
  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_entity_tag DROP INDEX UI_entity_id_entity_table_tag_id; 
  
  ALTER TABLE civicrm_case_contact DROP FOREIGN KEY 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;
  
  then click on the Update Indices button.   Then go back to phpMyAdmin and run these SQL commands:
  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 ADD CONSTRAINT FK_civicrm_case_contact_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact (id) ON DELETE CASCADE;
  
  ALTER TABLE civicrm_case_contact ADD CONSTRAINT FK_civicrm_case_contact_case_id FOREIGN KEY (case_id) REFERENCES civicrm_case (id) ON DELETE CASCADE;
  
  I had this issue moving from 4.7.18 to 4.7.20.   Not sure why this wasn't an issue before, I'm guessing its because I don't use the CASE module on my site.
  
  > **Comments:**
  > **username_4**: Re "I had this issue moving from 4.7.18 to 4.7.20. Not sure why this wasn't an issue before" - the indices may still have been "missing" in earlier versions, but there was no detection of that.  What was added in 4.7.19 was a check to identify missing indices and an option to add them, but the additions fail in some situations.  Although it says 'missing', it really means "index does not exist, or does not match with the expected specification"  
  > **username_4**: If can help test proposed fixes for this issue, see https://issues.civicrm.org/jira/browse/CRM-20533  
  > **username_0**: Was a little leery of running any of the SQL on the other thread, but Neil's instructions worked like a charm. Just one correction -- there's a typo at the end of the second command: `ALTER TABLE civicrm_option_value DROP FOREIGN KEY FK_civicrm_option_value_option_group_id`;` Removed the apostrophe between "FK_civicrm_option_value_option_group_id" and the semicolon, and it worked great. Thanks Neil!  
  > **username_1**: I removed the apostrophe/quote from the line.  
  > **username_5**: I also got a list of tables missing indices going to civi 4.7.20; among them, civicrm_case_contact.contact_id. In SchemaHandler:getMissingIndices the array of required and existing indices are gathered from the schema xml, but when they're compared, for civicrm_case_contact  the string in required sigs is "civicrm_case_contact::1::case_id::contact_id" and in existing sigs it is "civicrm_case_contact::0::contact_id"  I can propose a fix; in the meantime other indices listed as missing seem to really be missing.  
  > **username_6**: The fourth line in the solution beginning with "ALTER TABLE civicrm_entity_tag" contains a second SQL command:  "UPDATE civicrm_uf_group SET name = CONCAT(name, '_', id) WHERE name NOT LIKE CONCAT('%', id);"  Changing the machine name can have wide repercussions, and in fact rendered my CiviCRM inoperable after I enabled CiviVolunter (https://issues.civicrm.org/jira/browse/VOL-309).   It's not clear why this was necessary to solve the index problem. Was this intentional and required? If so, after fixing the indexes, can/should the original names be restored?  
  > **username_1**: username_6 .. not sure why that was in the original fix.  That said, that line caused issues with some of my searches that use built-in group ID# 1.  I changed the name back to the original and that fixed it.  
  > **username_2**: I used these SQL commands and it seemed to work in removing the warning.   However, now I cannot create a new Civicase.   (I did use a test copy of the database.)   Has anyone tested creating a new case after this procedure?  
  > **username_3**: I removed UPDATE civicrm_uf_group SET name = CONCAT(name, '_', id) WHERE name NOT LIKE CONCAT('%', id); from the answer because it seems wrong, has nothing to do with the issue I think and might cause problems.  
  
  ---
  
  