How to find and/or fix missing indices?

Question

After upgrading to 4.7.19, I'm now getting a warning: You have missing indices on some tables. This may cause poor performance.

How can I find which indices are missing and then, how can I create them?

Answer 1

Disclaimer, as with other answers, this is aimed at a fairly technical person who kinda understands what they're doing and has made lots of backups :-)

As Sophie points out you can find the exact problem looking at the latest log in the ConfigAndLog directory.

For several of us the problem has been that there's a key defined incorrectly and the script's current response is to try a CREATE INDEX... statement, but it already exists, so it fails. Furthermore dropping this index with a simple DROP KEY <keyname> ON <table> .... cannot be done where a foreign key (FK) depends on having an index on those columns, as pointed out by Aidan

Temporarily disabling FKs can be problematic. The way I got it up and running was as follows:

1. Run the fixer, either from the status page or (for drupal users) with

   drush cvapi System.updateindexes
   

   If it didn't work, read on.

2. Inspect the latest file in ConfigAndLog to find the table where it died. In my case this looked like

   CREATE UNIQUE INDEX UI_case_contact_id ON civicrm_case_contact
   (case_id, contact_id) [nativecode=1061 ** Duplicate key name
   'UI_case_contact_id'
   

   So it's the UI_case_contact_id key that's the problem here.

3. Attempt to delete that key, if this works you can go back to step 1.

   drush civicrm-sql-cli
   MariaDB [your_db]> DROP INDEX UI_case_contact_id ON civicrm_case_contact;
   

4. If deleting the key fails due to a FK constraint, e.g. like this:

   ERROR 1553 (HY000): Cannot drop index 'UI_case_contact_id': needed
   in a foreign key constraint.
   

   Then create the same key with a different name - copy the definition from the error message in (2), e.g.

   CREATE UNIQUE INDEX my_temp_key ON civicrm_case_contact
   (case_id, contact_id);
   

   You should now be able to go back to step 3 and successfully delete the original key since now the FK can depend on your new key called my_temp_key.

5. Re-run the updater, hopefully it's ok (on that table/key at least) now. If so you can then delete your my_temp_key with

   DROP INDEX my_temp_key ON civicrm_case_contact;
   

   Nb. Several of us have had this part fail. This means the update script has not created the index for some reason. If this happens I'd advise it would probably be best practise to reissue the original CREATE INDEX command from the log (step 2 above), then you should be able to drop your temporary key ok. This way you leave the schema exactly as CiviCRM expects it to be, which is definitely going to be helpful in the long term.

I put this solution together standing on the observations shared by others credited above, just thought it might be useful for me to spell out a step by step process. :-)

Answer 2

The pop-up message should have a link "View details and manage alerts" that takes you to the System Status page. That includes an Update Indices button (yes there is a typo in 4.7.19 but it doesn't affect the function).

Update Indices should create the missing indices which may take a while depending on which indices are missing and how much data you have in the affected tables, and then return you to the System Status page without that warning.

However, there is a bug (https://issues.civicrm.org/jira/browse/CRM-20533) that causes the index creation to fail when an index of the same name already exists.

If that affects your system, the warning will persist until a future release when this is fixed. If that bothers you, you can use the 'Hide' option.

Note that this is warning of a problem that has likely been in your system for a while: the problem is not new, but the check and the attempt to fix it is new in 4.7.19.

Kainuk's answer shows how to list the missing indices on a Drupal system, or for any CMS you can use:

cv ev 'print_r(CRM_Core_BAO_SchemaHandler::getMissingIndices())'

and run the update with

cv api System.updateindexes

Answer 3

After upgrading to 4.7.19 I found that indices cannot be created automatically; you can look at your CiviCRM error log to see which database statement is causing a fatal error - you might see problems such as a duplicate index.

In my case I had to run these database statements before/after repairing indices, but it could be different on your site:

ALTER TABLE civicrm_prevnext_cache DROP INDEX index_all;
ALTER TABLE civicrm_option_value DROP FOREIGN KEY `FK_civicrm_option_value_option_group_id`;
ALTER TABLE civicrm_option_value DROP INDEX index_option_group_id_name;
ALTER TABLE civicrm_entity_tag DROP INDEX UI_entity_id_entity_table_tag_id;

Then, click the index repair button, and finally, restore the constraint:

ALTER TABLE civicrm_option_value ADD CONSTRAINT `FK_civicrm_option_value_option_group_id` FOREIGN KEY (`option_group_id`) REFERENCES `civicrm_option_group` (`id`) ON DELETE CASCADE;

Answer 4

Had the same problem when I went from 4.7.14 to 4.7.20, same resolution as @mfb but with a different index. Default location for the error logs is

/sites/default/files/civicrm/ConfigAndLog

There I found an error adding the index UI_case_contact_id . Coincidentally, this was the first index listed when running the drush ev command per @Kainuk above. Still had to fix via db. For me it was:

ALTER TABLE civicrm_case_contact DROP FOREIGN KEY `FK_civicrm_case_contact_contact_id`;
ALTER TABLE civicrm_case_contact DROP FOREIGN KEY `FK_civicrm_case_contact_case_id`;
ALTER TABLE civicrm_case_contact DROP INDEX UI_case_contact_id;

Clicked on the button on the civicrm/a/#/status page, then ran these queries to restore the constraints

ALTER TABLE civicrm_case_contact ADD CONSTRAINT `FK_civicrm_case_contact_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE;
ALTER TABLE civicrm_case_contact ADD CONSTRAINT `FK_civicrm_case_contact_case_id` FOREIGN KEY (`case_id`) REFERENCES `civicrm_case` (`id`) ON DELETE CASCADE;

BTW, even though drush wasn't needed to solve this problem, these sites were helpful getting it installed: https://www.drupal.org/node/2809987#comment-11684107 http://endlessgeek.com/2014/03/install-drush-cpanel-shared-hosting/

Answer 5

Are you using Drupal and do you have access to drush? Then you can use the shell. To show the missing indexes do:

 drush ev 'civicrm_initialize();print_r(CRM_Core_BAO_SchemaHandler::getMissingIndices())'

To create them use

 drush cvapi System.updateindexes

Answer 6

From the system check screen you should be able to click on a button to add them. Most likely this is because you are missing newly added indices

e.g from http://yoursite/civicrm/a/#/status

You should have an option to add the indices. Note that on a large site they may require an outage to add them as they may take a while

Answer 7

I upgraded from 4.7.8 to 4.7.20. After seeing 'missing indices' status msg I saw that table civicrm_case_contact did indeed have foreign keys for both contact_id and case_id- that's about all it has, being a link table. The problem is in the function that builds the existing_sigs, required_sigs arrays for SchemaHandler:findMissingIndices comparing civicrm_case_contact::1::case_id::contact_id with civicrm_case_contact::0::contact_id Other tables listed as 'missing indices' included civicrm_uf_group:name but column name is not constrained to being unique; and civicrm_activity:status_id which is allowed to be null. This is a very useful function in theory, but maybe needs some work? Thank you all