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']

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.
  • 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 Jul 8 '17 at 12:23
  • Updated the answer. – jitendra - fuzion Jul 8 '17 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 Jul 16 '17 at 18:57
  • @RoseLanigan this answer might be helpful civicrm.stackexchange.com/questions/18487/… – William Mortada Jul 27 '17 at 11:43
  • @WilliamMortada perfect, thank you (and thanks to Artful Robot!) – Rose Lanigan Jul 31 '17 at 20:55

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.