5

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?

5

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.

  • 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" – Aidan Jun 15 '17 at 10:21
  • If can help test proposed fixes for this issue, see issues.civicrm.org/jira/browse/CRM-20533 – Aidan Jun 15 '17 at 10:26
  • 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! – Prónay Jun 16 '17 at 6:56
  • I removed the apostrophe/quote from the line. – Neil Z Jun 16 '17 at 11:37
  • 1
    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 (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? – BobS Jun 27 '17 at 14:27

Not the answer you're looking for? Browse other questions tagged or ask your own question.