4.7.20 upgrade -- missing indices warning, but index already exists? [duplicate]

Question

Upgraded to 4.7.20 today. As others on this thread 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?

Answer 1

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.