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?

  • 1
    I've thankfully found this thread, and I have Drush 7.40 installed and working well on the server. I have a Drupal 7 with Civi 4.7.19 installed and on the Warnings screen also have the indices warning. Clicking the button didn't work, and I followed @Kainuk's answer as well, but when I attempted to run: drush cvapi System.updateindexes I get the error: Array ( [error_code] => already exists [tip] => add debug=1 to your API call to have more info about the error [is_error] => 1 [error_message] => DB Error: already exists ) I have no idea how t – shayaknyc Jun 1 '17 at 15:02

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. :-)

  • I think the command in Step 3 should be "DROP INDEX" instead of "DROP KEY"? I needed the former with mysql, anyway. Also, I was able to follow these instructions to rebuild the indexes with the updater, but get the error "Cannot drop index 'my_temp': needed in a foreign key constraint" when I try to remove my temporary index. Probably doesn't hurt to leave it there, so I'm not too worried about it... – Norris Jul 12 '17 at 7:04
  • Hi Norris, having done this a few times, I've experienced that same problem. I've updated my answer. Also thanks for spotting the key/index thing 😊 – artfulrobot Jul 12 '17 at 7:19
  • Many thanks. Steps 3-5 worked perfectly for me. Note that I did encounter the "common" errors, but the suggested recoveries worked just fine for me. – Karl Rudnick Jul 25 '17 at 18:07
  • Interesting that I successfully ran this fix on my local xampp instance on my laptop. When I went live, my System Status page does not offer the Update Indices button, so I can't go about the same steps. I am running Wordpress 4.8 and updated Civi 4.7.16 to 4.6.22. The System Status function displays nothing on the upper part (where the Update Indices button should be). Any ideas how to restore that? – Karl Rudnick Jul 26 '17 at 6:42
  • The System Status page not fully displaying is actually a separate issue requiring CiviCRM cache to be fully cleared and default paths reset after the upgrade. I usually only do that step when moving the site from one location/machine to another. See civicrm.stackexchange.com/questions/19365/… – Karl Rudnick Jul 26 '17 at 7:24

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

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;
  • Warning: Changing the machine name as you do above (UPDATE civicrm_uf_group SET name...) can have wide repercussions, and in fact rendered my CiviCRM inoperable after I enabled CiviVolunter (issues.civicrm.org/jira/browse/VOL-309). – BobS Jun 27 '17 at 16:12
  • Agreed, removed. For reference here's what you should not do: UPDATE civicrm_uf_group SET name = CONCAT(name, '_', id) WHERE name NOT LIKE CONCAT('%', id); – Alan Dixon Sep 5 '17 at 20:36
  • Thanks, this worked for me. In my case, I only had to drop FK_civicrm_option_value_option_group_id, click the repair button and then add FK_civicrm_option_value_option_group_id back. I was able to use phpMyAdmin to do this. I'm using CiviCRM 4.7.27 and this still isn't fixed. – George Lozier Nov 19 '17 at 15:49

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/

  • After checking for and finding the same issue in [civicrm.files]/ConfigAndLog, this worked for me after CiviCRM 4.7.20 --> 4.7.22. I used phpMyAdmin to run the SQL queries, no issues. My config is LAMP running on AWS t2 micro with WordPress 4.8 as CMS. Thanks, I now have a clean 4.7.22 install. – dvhirst Jul 28 '17 at 18:02

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

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

  • Thanks for this answer. Should the updating of the indices happen in the background? When I click on the button, then a confirmation dialog, then a couple of seconds of whirling CiviCRM logo, then back to the System Status page where it still says that there are missing indices. Refreshing the page does not help. – Jern May 6 '17 at 10:54
  • Had the same. Ran kainuk's offering from terminal. Spat out an array for first, then drush cvapi System.updateindexes Array ( [error_code] => already exists [tip] => add debug=1 to your API call to have more info about the error [is_error] => 1 [error_message] => DB Error: already exists ) – petednz - fuzion May 15 '17 at 1:12

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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