AnsweredAssumed Answered

2.1 upgrade : Schema auto-update failed, Duplicate key name

Question asked by stk137 on Jul 26, 2007
I haven't found another post on this yet, please point me to one if it exists, but this looks like a bug to me in the Explicit Indexes script.

Upgrading Alfresco 1.4 -> 2.1
with MySQL 5 on  Linux

I get the following ERROR

09:56:06,106 ERROR [domain.schema.SchemaBootstrap] Statement execution failed:
   SQL:  CREATE INDEX idx_avm_np_name ON avm_node_properties (qname)
   Error: Duplicate key name 'idx_avm_np_name'
   File: /usr/local/tomcat1/temp/Alfresco/AlfrescoSchemaUpdate-org.hibernate.dialect.MySQLInnoDBDialect-12485.sql
   Line: 46
09:56:06,108 ERROR [domain.schema.SchemaBootstrap] Schema auto-update failed
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Duplicate key name 'idx_avm_np_name'

Here's the script that is running, it looks like it's not autogenerated by hibernate but rather something the Alfresco team added for these indexes, so it seems like it should be altered to avoid this error thrown from trying to create an index that already exists

– Title:      Explicit indexes
– Database:   Generic
– Since:      V2.0 Schema 38
– Author:     Derek Hulley

– Please contact if you need assistance with the upgrade.

– None of the Hibernate dialects will generate statements for explicit indexes
– when creating new tables for an existing database.  We need to check that the
– indexes for the AUDIT, JBPM and AVM tables are present.

– This script must be executed after Hibernate-generated scripts have been executed.
– Where the updates are being applied long after the tables may have been created,
– the index creation statements are optional.  The the select statements ensure that
– the script is only executed once the appropriate tables have been created.

– JBPM tables

– Audit tables
SELECT COUNT(*) FROM alf_audit_date;
CREATE INDEX adt_q_idx ON alf_audit_date (quarter);(optional)
CREATE INDEX adt_dow_idx ON alf_audit_date (day_of_week);(optional)
CREATE INDEX adt_date_idx ON alf_audit_date (date_only);(optional)
CREATE INDEX adt_y_idx ON alf_audit_date (year);(optional)
CREATE INDEX adt_hy_idx ON alf_audit_date (halfYear);(optional)
CREATE INDEX adt_wom_idx ON alf_audit_date (week_of_month);(optional)
CREATE INDEX adt_dom_idx ON alf_audit_date (day_of_month);(optional)
CREATE INDEX adt_m_idx ON alf_audit_date (month);(optional)
CREATE INDEX adt_doy_idx ON alf_audit_date (day_of_year);(optional)
CREATE INDEX adt_woy_idx ON alf_audit_date (week_of_year);(optional)
SELECT COUNT(*) FROM alf_audit_fact;
CREATE INDEX adt_user_idx ON alf_audit_fact (user_id);(optional)
CREATE INDEX adt_store_idx ON alf_audit_fact (store_protocol, store_id, node_uuid);(optional)
SELECT COUNT(*) FROM alf_audit_source;
CREATE INDEX app_source_met_idx ON alf_audit_source (method);(optional)
CREATE INDEX app_source_app_idx ON alf_audit_source (application);(optional)
CREATE INDEX app_source_ser_idx ON alf_audit_source (service);(optional)

– AVM tables: These are new so are not optional
CREATE INDEX idx_avm_np_name ON avm_node_properties (qname);
CREATE INDEX idx_avm_sp_name ON avm_store_properties (qname);
CREATE INDEX idx_avm_vr_version ON avm_version_roots (version_id);

– Record script finish

DELETE FROM alf_applied_patch WHERE id = 'patch.db-V2.0-ExplicitIndexes';
INSERT INTO alf_applied_patch
  (id, description, fixes_from_schema, fixes_to_schema, applied_to_schema, target_schema, applied_on_date, applied_to_server, was_executed, succeeded, report)
    'patch.db-V2.0-ExplicitIndexes', 'Manually executed script upgrade V2.0: Explicit Indexes',
    0, 37, -1, 38, null, 'UNKOWN', 1, 1, 'Script completed'