5

Just upgraded to 4.7.23 and am receiving a Performance Warning: Missing Indices.

The warning states:

The following tables have an index key with a mismatch in value. Please delete the key indices listed from the below table and then click on 'Update Indices' button.

Then the following is listed

Table Name - civicrm_case_contact

Key Name - UI_case_contact_id

Fields - case_id, contact_id

I found the Table in my database with phpMyAdmin. Nested inside are Columns and Indexes. Inside Indexes is what looks like the Key Name of: UI_case_contact_id, and also something labeled: FK_civicrm_case_contact_id.

Will simply deleting UI_case_contact_id resolve this issue?

Or, do I need to delete the Field: FK_civicrm_case_contact_id?

Or is there another approach?

My Civi is definitely slower so looking to see resolving this would help.

Attached an image of the Table from PhpMyAdmin

Thanks in advance

Tim

enter image description here

enter image description here

2

I suggest you delete the UI_case_contact_id index as suggested by CiviCRM, and then refresh the system status page. It should then present a button which will automatically add missing indexes.

  • Thanks Coleman. Learning here, and learning how to use Stack Ex so this may come to you twice. I added a screen shot of the Structure. When I clicked on Drop, I received an error stating: #1553 - Cannot drop index UI_case_contact_id: needed in a foreign key constraint. Is Drop the correct action to take to delete? How do I get around it? Thanks – TML100 Sep 4 '17 at 14:18
  • same issue here. – themak Oct 3 '17 at 20:41
  • You can disable key constraints before dropping. – Coleman Nov 22 '17 at 18:06
1

This is hopefully fixed in 4.7.25 - sometime tomorrow it should be downloadable as an rc https://download.civicrm.org/latest

1

This SQL query fixed it for me (don't know if SET foreign_key_checks is needed)

SET foreign_key_checks = 0;
CREATE INDEX `foobar` ON `civicrm`.`civicrm_case_contact` (case_id, contact_id);
DROP INDEX `UI_case_contact_id` on `civicrm`.`civicrm_case_contact`;
CREATE UNIQUE INDEX `UI_case_contact_id` ON `civicrm`.`civicrm_case_contact` (case_id, contact_id);
DROP INDEX `foobar` on `civicrm`.`civicrm_case_contact`;
SET foreign_key_checks = 1;

Source: https://issues.civicrm.org/jira/browse/CRM-20533?focusedCommentId=105725&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-105725

  • Thanks much...going to give this a try. My Case_Contact is also empty so hoping for the same result as it looks like you achieved. Just starting to learn about Civi code at this point...where did you enter these lines? Thanks again – TML100 Sep 11 '17 at 4:38
  • @TML100 You can run this SQL query from the SQL shell, or from within phpMyAdmin (SQL button) – Holland Open Source Sep 11 '17 at 10:58
  • Thanks Holland After some research on using phpMyAdmin gave it a try and received Error #1142 Index command denied to user. But researching that at the host level says I have all possible permissions. Not expecting you to consider options for that one. There seems to be a lot of material out there on it, including syntax which I checked. All of which involve making changes which again I am not experienced enough to make. So going to hold off for a while as my DB seems to be functioning ok and the table itself is empty...so it's really only the annoyance of a Performance Warning. – TML100 Sep 13 '17 at 20:05
  • @TML100 I did it as mysql user "root" – Holland Open Source Sep 14 '17 at 7:09

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.