CiviCRM 4.7.29 Update Indices fails

Question

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

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