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

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.

• Same problem for me on WP civicrm 4.7.25 installation. – Frank Oct 11 '17 at 8:50
• I am still getting this after upgrade to 4.7.27. Help please. – The Amateur Nov 7 '17 at 16:46

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.

• Hi - on behalf of the OP - this is not working for us. We still encounter the "dop index ui_case_contact_id on civicrm_case_contact cannot drop index ui_case_contact_id needed in a foreign key constraint". This is after running the commands exactly as above including set foreign_key_checks=0; – JohnFF Oct 12 '17 at 12:14
• Also, missing you at the sprint! – JohnFF Oct 12 '17 at 12:15
• @JohnFF To state the obvious, it sounds like foreign_key_checks isn't being set to 0. I've no idea why that should be the case for you. [OT: Hope the sprint is fun and that someone's brought a hacky sack; it's just the weather for it!] – Christian Wach Oct 12 '17 at 12:58
• Thanks for the suggestion Christian. When I run this I get the following error: Error SQL query: DROP INDEX UI_case_contact_id on civicrm_case_contact MySQL said: Documentation #1553 - Cannot drop index 'UI_case_contact_id': needed in a foreign key constraint – The Amateur Oct 23 '17 at 16:03
• Could this have something to do with not having CiviCase enabled? – The Amateur Nov 7 '17 at 17:02