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

[huima]

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.

[mdahlman]

Thanks for posting your issue. It helped assure me of my sanity when I got nearly the same thing. In my case it's coming during an install of JasperReports Server.

[advanced-sql] Failed to execute:   alter table JIDomainDatasourceDSRef add index FKFDA42FC7106B699 (ref_id), add constraint FKFDA42FC7106B699 foreign key (ref_id) references JIResource (id)
[advanced-sql] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate key name 'FKFDA42FC7106B699'

After getting this error while executing a large SQL script, I can successfully run the exact same alter table statement manually. I cannot figure out why MySQL occasionally rejects some alter table statements. Restarting MySQL indeed solves the problem [for an unspecified but temporary time period].

I'm using exactly the same build of MySQL on Mac OS. We could optimistically hope that this is a MySQL problem limited to version 5.1.48 and/or limited to Mac OS… but I would feel a lot more comfortable if I could track down a bug report in the MySQL tracker.

If any MySQL gurus come across this thread… I love an opinion on what is causing this problem.

Regards,
Matt