What to do about "Operation failed: Update Indices DB Error: unknown error Debug" after upgrading to 4.7.25

Question

This is related to a question I posted yesterday (How does one delete "key indices"?), which was considered a duplicate of another question (Performance Warning: Missing Indices. Mismatched Index Key). I am posting this new question because it seems to be a somewhat different issue and because adding a comment to one of those other posts didn't quite seem to fit with this.

It was suggested that installing 4.7.25 would resolve that issue. However, it seems to have produced a new one.

With 4.7.25 installed under Drupal 7, I now get this warning:

Performance warning: Missing indices

The following tables have missing indices. Click 'Update Indices' button to create them.

Table Name Key Name Expected Indices civicrm_option_value index_option_group_id_name name(128), option_group_id civicrm_case_contact UI_case_contact_id case_id, contact_id

But when I click the Update Indices button, I get this error:

Operation failed: Update Indices DB Error: unknown error Debug information: DROP INDEX index_option_group_id_name ON civicrm_option_value [nativecode=1553 ** Cannot drop index 'index_option_group_id_name': needed in a foreign key constraint]

Any help on what I can do about this would be appreciated. Please spell it out for me though; I'm not an expert at this stuff. Thanks.

Thanks.

Answer 1

You'll have to work on your database directly. The following works for me - although the Status screen still shows the error until the Update Indices button is clicked again after the repair has been done (I presume it simply verifies the fix):

USE your_civicrm_db;
SHOW CREATE TABLE civicrm_case_contact;
SET foreign_key_checks = 0;
DROP INDEX `UI_case_contact_id` on civicrm_case_contact;
CREATE UNIQUE INDEX `UI_case_contact_id` ON civicrm_case_contact (case_id, contact_id);
SET foreign_key_checks = 1;

If you do this via PhpMyAdmin (or similar), you can skip USE your_civicrm_db; since you'll have already navigated there.