3

Upgraded from 4.7.17 to 4.7.21 and got the following message that I don't know how to fix. I don't have drush and the other answers to this problem don't include this index or don't fix this particular problem. I tried "ALTER TABLE civicrm_financial_type DROP INDEX index_entity_id_entity_table_account_relationship" but MySQL can't do it and says 'check that column/key exists'. Also tried 'ALTER TABLE civicrm_entity_financial_account DROP INDEX index_entity_id_entity_table_account_relationship' with the same result

Operation failed: Update Indices DB Error: already exists Debug information: CREATE UNIQUE INDEX index_entity_id_entity_table_account_relationship ON civicrm_entity_financial_account (entity_id, entity_table, account_relationship) [nativecode=1062 ** Duplicate entry '5-civicrm_financial_type-3' for key 'index_entity_id_entity_table_account_relationship']

Improve this question
3
  • This issues appears to have re-surfaced. Just upgrade a Wordpress installation from 4.7.14 to 4.7.22 and got the same error. ![enter image description here](i.sstatic.net/UdmzT.jpg)
    – Len Inkster
    Commented Jul 14, 2017 at 15:21
  • Hi Len. This isn't an Answer so should have been added as a Comment but perhaps you don't have enough 'credit' to make comments. I don't think the Answer by Jitendra implied this has been fixed in 4.7.22, rather he has explained how you can sort this out by removing the Index in question. hope that helps
    – petednz - fuzion ♦
    Commented Jul 14, 2017 at 20:48
  • Possible duplicate of civicrm.stackexchange.com/questions/18487/… ?
    – William Mortada
    Commented Jul 27, 2017 at 11:47

1 Answer 1

Reset to default
4

You can apply this PR - and reload System status page. This will let you know in advance which indices are already present in DB and would result in DB error on hitting Update Indices button. So what need to be done is -

  • Apply this PR.
  • Reload System status page.
  • Some duplicate indices would be shown above Update Indices button including a message of what needs to be done.
  • Delete indices manually from the DB (which are listed on above step).
  • Execute Update Indices button.

Update: just saw the error again - So the error say you have two rows which contain the same values for (entity_id, entity_table, account_relationship) as 5, civicrm_financial_type and 3 respectively. This should not be there as this group of fields are meant to be unique. To remove this error, you can -

  • Open civicrm_entity_financial_account table.
  • Search for rows that match entity_id = 5, entity_table = civicrm_financial_type and account_relationship = 3. You'll get two rows(This table should allow only one after indexing), delete one of them and re-execute Update Indices button. This is not provided by core as of now, so we have to do this manually.
Improve this answer
5
  • I applied the PR suggested, and then Civi listed one index that I removed which wasn't the one listed above. Then reran the update and got exactly the same error as above. So no further forward. Suggestions? Shouldn't Civi be able to cope with problems like this? Is there a reason this has to be sorted manually?
    – Andy Clark
    Commented Jul 8, 2017 at 12:23
  • Updated the answer.
    – jitendra - fuzion
    Commented Jul 8, 2017 at 13:05
  • Applied the PR. The System Status now says there is an issue with civicrm_case_contact.case_id and .contact_id, but it won't let me drop these two because they're needed in a foreign key constraint. Tried to remove the foreign key (ALTER TABLE civicrm_case_contact DROP FOREIGN KEY civicrm_case_contact.case_id ;) but then get the message "Can't DROP 'case_id'; check that column/key exists". I have tried the 'Update indices' button but it just reloads the System Status page with no changes. Any ideas?
    – Rose Lanigan
    Commented Jul 16, 2017 at 18:57
  • @RoseLanigan this answer might be helpful civicrm.stackexchange.com/questions/18487/…
    – William Mortada
    Commented Jul 27, 2017 at 11:43
  • @WilliamMortada perfect, thank you (and thanks to Artful Robot!)
    – Rose Lanigan
    Commented Jul 31, 2017 at 20:55

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.