AnsweredAssumed Answered

Install Alfresco 4.0.d on JBoss with MySQL master/slave

Question asked by jean-rémyrevy on Sep 18, 2012
Hi folks.

I'm currently (re) installing Alfresco 4.0.D (and even 4.0.1). It's powered by JBoss 5.1 and MySQL 5.1.16 in Master Salve replication. I know this sounds much more like an enterprise configuration than a community one … and that's it. So don't hesitate to tell me if it's offtopic.

I have configured a datasource :

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
        <local-tx-datasource>
                <jndi-name>alfresco-datasource</jndi-name>
                <connection-url>jdbc:mysql://10.1.2.13,10.1.2.14:3306/alfresco?user=alfresco&amp;password=alfresco</connection-url>
                <driver-class>com.mysql.jdbc.Driver</driver-class>

                <user-name>alfresco</user-name>
                <password>alfresco</password>


                <check-valid-connection-sql>/* ping */ SELECT 1</check-valid-connection-sql>

                <min-pool-size>5</min-pool-size>
                <max-pool-size>80</max-pool-size>

                <exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter</exception-sorter-class-name>
        <valid-connection-checker-class-name>com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name>
                <metadata>
                                <type-mapping>mySQL</type-mapping>
                </metadata>
        </local-tx-datasource>
</datasources>

For failover mode, which seemed to be implied by the use of 2 urls, mode auto commit is activated by default. If it fails, then JBoss / MySQLDriver try te request on the second node (which is read only, more information here : http://dba.stackexchange.com/questions/19452/jboss-alfresco-cannot-write-on-slave-after-failover).

During installation, when loadbalancing is not activated ( use only 1 adress), it fails, then rollbacks, and pass to next item correctly.
But with failover, the installer fails :

11:57:24,311 INFO  [STDOUT] 2012-09-18 11:57:24,311  ERROR [domain.schema.SchemaBootstrap] [main] Statement execution failed:
   SQL: CREATE TABLE alf_content_url
(
   id BIGINT NOT NULL AUTO_INCREMENT,
   content_url VARCHAR(255) NOT NULL,
   content_url_short VARCHAR(12) NOT NULL,
   content_url_crc BIGINT NOT NULL,
   content_size BIGINT NOT NULL,
   orphan_time BIGINT NULL,
   UNIQUE INDEX idx_alf_conturl_cr (content_url_short, content_url_crc),
   INDEX idx_alf_conturl_ot (orphan_time),
   PRIMARY KEY (id)
) ENGINE=InnoDB
   Error: Connection is read-only. Queries leading to data modification are not allowed.
   File: /tmp/Alfresco/AlfrescoSchema-MySQLInnoDBDialect-Update-7557159645974832030.sql
   Line: 42

11:57:24,317 INFO  [STDOUT] 2012-09-18 11:57:24,315  ERROR [domain.schema.SchemaBootstrap] [main] Schema auto-update failed
java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:756)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:730)
        at sun.reflect.GeneratedMethodAccessor431.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at com.mysql.jdbc.LoadBalancingConnectionProxy$ConnectionErrorFiringInvocationHandler.invoke(LoadBalancingConnectionProxy.java:103)
        at com.mysql.jdbc.FailoverConnectionProxy$FailoverInvocationHandler.invoke(FailoverConnectionProxy.java:52)
        at $Proxy264.execute(Unknown Source)

The only solution, at this moment, is to desactivate the use of 2 MySQL Nodes during installation.

Does someone know what I can do else ? Would I have some issues in the future (legal rollback that would failed and lead to corrupted database) ?

Outcomes