3

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.

Improve this question
2
  • Same problem for me on WP civicrm 4.7.25 installation. Commented Oct 11, 2017 at 8:50
  • I am still getting this after upgrade to 4.7.27. Help please. Commented Nov 7, 2017 at 16:46

1 Answer 1

Reset to default
3

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.

Improve this answer
6
  • 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; Commented Oct 12, 2017 at 12:14
  • 1
    Also, missing you at the sprint! Commented Oct 12, 2017 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!] Commented Oct 12, 2017 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 Commented Oct 23, 2017 at 16:03
  • Could this have something to do with not having CiviCase enabled? Commented Nov 7, 2017 at 17:02

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.