I've messed around unsuccessfully to update indices by following various posts in StackExchange for similar errors. I have the impression that the Update Indices button simply does not work correctly.

If I push it at the moment, I get:

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 '0-civicrm_financial_type-12' for key 'index_entity_id_entity_table_account_relationship']

Does anyone have a good way of removing all indices and recreating them?

I just upgraded from 4.7.25 to 4.7.29 (and am on the latest Wordpress version) if that is relevant.

Tried CiviCRM Update 4.7.27 Operation failed: Update Indices Errno: 150 but it didn't work either (complained about ALTER table civicrm_option_value DROP INDEX index_option_group_id_name with #1091 - Can't DROP 'index_option_group_id_name'; check that column/key exists)

Thanks, David

1 Answer 1


Here is the possible solution for this. Try running the sql queries through shell or phpmyadmin and than re-run the update indices.

Looking at the db error i can see civirm_entity_financial_account have duplicate rows. Please get rid of one row or all with entity_id = 0 as it doesn't make sense and then re-run update indices.

Delete civicrm_entity_financial_account where entity_id = 0 and entity_table = 'civicrm_financial_type';
  • Okay. What error are you getting when you run update indices? Commented Jan 21, 2018 at 18:32
  • Updated my answer. Commented Jan 21, 2018 at 18:35
  • This is what happens: Error Static analysis: 1 errors were found during analysis. Unexpected token. (near "civicrm_entity_financial_account" at position 7) SQL query: delete civicrm_entity_financial_account where entity_id = 0 and entity_table = 'civicrm_financial_type' MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where entity_id = 0 and entity_table = 'civicrm_financial_type'' at line 1 Commented Jan 23, 2018 at 22:16
  • I didn't re-run Update Indices yet Commented Jan 23, 2018 at 22:18
  • Deleted the unnecessary duplicate using SQL and the update ran through successfully. It took a bit of guts to push the delete button, but afterwards, it felt good! ;-) Thanks for the info! Commented Jan 27, 2018 at 15:00

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.