AnsweredAssumed Answered

Schema auto-update failed: CE 3.3g on OS X with Mysql

Question asked by huima on Sep 6, 2010
Latest reply on Nov 30, 2010 by mdahlman
Warning to anyone having Alfresco on OS X.

I lost few hours today trying to debug why Alfresco was giving me hard time and complaining about schema update failed, when starting fresh install of Alfresco. I debugged my own setup configurations and tried to even use clean Alfresco distribution bundle install, which first worked - but later on stopped to work.

Turns out that Mysql had something strange going on inside it, as everything worked just fine if I restarted the mysql server. If I just dropped the database and created it again, then creation of Alfresco tables failed.

Mysql server version: 5.1.48 MySQL Community Server (GPL)

Below is an example of typical log output:

02:01:03,729  INFO  [domain.schema.SchemaBootstrap] Executing database script /Users/huima/Alfresco/tomcat/temp/Alfresco/AlfrescoSchema-MySQLInnoDBDialect-Update-4372452133738171863.sql (Copied from classpath:alfresco/dbscripts/create/3.3/org.hibernate.dialect.MySQLInnoDBDialect/AlfrescoCreate-3.3-RepoTables.sql).
02:01:04,188  INFO  [domain.schema.SchemaBootstrap] Executing database script /Users/huima/Alfresco/tomcat/temp/Alfresco/AlfrescoSchema-MySQLInnoDBDialect-Update-8013764551840333673.sql (Generated).
02:01:21,111  ERROR [domain.schema.SchemaBootstrap] Statement execution failed:
   SQL:     alter table JBPM_TASKACTORPOOL
        add index FK_TASKACTPL_TSKI (TASKINSTANCE_),
        add constraint FK_TASKACTPL_TSKI
        foreign key (TASKINSTANCE_)
        references JBPM_TASKINSTANCE (ID_)
   Error: Duplicate key name 'FK_TASKACTPL_TSKI'
   File: /Users/huima/Alfresco/tomcat/temp/Alfresco/AlfrescoSchema-MySQLInnoDBDialect-Update-8013764551840333673.sql
   Line: 1,153
02:01:21,112  ERROR [domain.schema.SchemaBootstrap] Schema auto-update failed
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate key name 'FK_TASKACTPL_TSKI'
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)


Don't know what happened or what caused it. Kicking myself now for not trying to restart the server before. I could be well sleeping by now, instead of trying to debug why my application started to fail :-)

Hope this helps if anyone else comes across similar situation.

Outcomes