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.

  • 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 Doctor Nov 7 '17 at 16:46
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.

  • 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
  • 1
    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 Doctor Oct 23 '17 at 16:03
  • Could this have something to do with not having CiviCase enabled? – The Doctor Nov 7 '17 at 17:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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