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

Improve this question

3 Answers 3

Reset to default
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.

Improve this answer
3
  • 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
    Commented Sep 4, 2017 at 14:18
  • same issue here.
    – themak
    Commented Oct 3, 2017 at 20:41
  • You can disable key constraints before dropping.
    – Coleman
    Commented Nov 22, 2017 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

Improve this answer
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

Improve this answer
4
  • 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
    Commented Sep 11, 2017 at 4:38
  • @TML100 You can run this SQL query from the SQL shell, or from within phpMyAdmin (SQL button)
    – Holland Open Source
    Commented Sep 11, 2017 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
    Commented Sep 13, 2017 at 20:05
  • @TML100 I did it as mysql user "root"
    – Holland Open Source
    Commented Sep 14, 2017 at 7:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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