Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to set up an high available MySQL cluster, using a Master / Slave configuration. I want to the slave become master (e.g. not read-only but writeable) if the first node come down (like in this question : Database Fail Over in Jboss Data sources)

So, I'm using a datasource with Connector/J drivers :

<datasources>

    <local-tx-datasource>
    <jndi-name>alfresco-datasource</jndi-name>
    <connection-url>jdbc:mysql://10.1.2.13,10.1.2.14:3306/alfresco</connection-url>
    <driver-class>org.gjt.mm.mysql.Driver</driver-class>
    <user-name>alfresco</user-name>
    <password>alfresco</password>
    <exception-sorter-class-name>
        org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter
    </exception-sorter-class-name>


    <connection-property name="readOnly">false</connection-property>
    <failOverReadOnly>false</failOverReadOnly>

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

    <metadata>
      <type-mapping>mySQL</type-mapping>
    </metadata>
  </local-tx-datasource>
</datasources>

I read many many post here dealing about this, but I'm not yet sure this is sufficient (indeed I'm sure it's not ;)).

In order to test my configuration, I just stop the first instance :

service mysql stop

As soon as my application (Alfresco, relying on Activiti), which is using Ibatis, detect there is a failure, it's flooding me of message saying that connection is lost / refused, .... So the fail over don't work.

    GRAVE: Error while closing command context
    org.apache.ibatis.exceptions.PersistenceException: 
    ### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

    The last packet successfully received from the server was 1 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
    ### The error may exist in org/activiti/db/mapping/entity/Job.xml
    ### The error may involve org.activiti.engine.impl.persistence.entity.JobEntity.selectNextJobsToExecute_mysql
    ### The error occurred while executing a query
    ### SQL: select *      from ACT_RU_JOB      where (RETRIES_ > 0)       and (DUEDATE_ is null or DUEDATE_ <= ?)       and (LOCK_OWNER_ is null or LOCK_EXP_TIME_ <= ?)
    ### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

    The last packet successfully received from the server was 1 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:77)
        at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:191)
        at org.activiti.engine.impl.persistence.entity.JobManager.findNextJobsToExecute(JobManager.java:97)
        at org.activiti.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:49)
        at org.activiti.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:32)
        at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)
        at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:42)
        at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
        at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
        at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
        at org.activiti.engine.impl.jobexecutor.JobAcquisitionThread.run(JobAcquisitionThread.java:63)
    Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure


[...]


Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2552)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3002)
    ... 45 more
09:54:00,270 WARN  [LocalManagedConnectionFactory] Destroying connection that is not valid, due to the following exception: com.mysql.jdbc.JDBC4LoadBalancedMySQLConnection@33dd0edd
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 31 875 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.

[...]

09:54:00,355 WARN  [JBossManagedConnectionPool] Destroying connection that could not be successfully matched: org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@34775a91[state=DESTROYED mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@244f22a3 handles=0 lastUse=1339055604851 permit=false trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@3d01c1a context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@53e4f0ea xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@476b0df4 txSync=null]
09:54:01,702 ERROR [STDERR] Thu Jun 07 09:54:01 CEST 2012 WARN: Connection to primary host failed

EXCEPTION STACK TRACE:



** BEGIN NESTED EXCEPTION ** 

And so on ...

I had problem before, because my app server user (alfresco@10.1.2.11) wasn't granted on the databsase. But it's resolved for now.

Here my MySQL configuration files (in case I forgot something) :

Master (10.1.2.13) [mysqld] user = s-mysql port = 3306 basedir =/app/mysql/current datadir =/app/data/mysql log-error =/app/log/mysql/master log =/app/log/mysql/query.log

#clustering
server_id       =1
log-bin         =/app/log/mysql/log-bin/log-bin.master
innodb_flush_log_at_trx_commit=1
sync_binlog     =1
skip-slave-start
expire_logs_days=10

binlog-do-db    =alfresco

Slave (10.1.2.14)

[mysqld]
user            = s-mysql
port            = 3306
basedir         =/app/mysql/current
datadir         =/app/data/mysql
log-error       =/app/log/mysql/slave
log             =/app/log/mysql/query.log

# Clustering
server_id       =2
log-bin         =/app/log/mysql/log-bin/log-bin.slave
expire_logs_days=10
skip-slave-start
relay-log-index =/app/log/mysql/log-relay/relay-log.slave.index
relay-log       =/app/log/mysql/log-relay/relay-log.slave

binlog-do-db    =alfresco
replicate-do-db =alfresco

Slave privileges

SHOW GRANTS for 'alfresco'@'10.1.2.11';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for alfresco@10.1.2.11                                                                                               |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'alfresco'@'10.1.2.11' IDENTIFIED BY PASSWORD     '*F9F457310EF9364D42AABE6D8C24A21712C000B5' |
| GRANT ALL PRIVILEGES ON `alfresco`.* TO 'alfresco'@'10.1.2.11' WITH GRANT OPTION                                    |
+-----------------------------------------------------------------------------------------------------------------+

Could someone give me hints, for solving this issue please ?

share|improve this question

migrated from stackoverflow.com Jun 18 '12 at 17:38

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 2 down vote accepted

Indeed it seems that I "forgot" one parameter

<autoReconnect>true</autoReconnect>

I said forgot with precaution, because as the per doc says : https://community.jboss.org/wiki/SetUpAMysqlDatasource

Automatic reconnect

WARNING: DO NOT ENABLE AUTO RECONNECT IF YOU ARE USING MANAGED TRANSACTIONS

The auto reconnect does not preserve transaction state in the database.

It is ok if you are ALWAYS using auto-commit=true.

I wasn't sure that I could activate it ... and I'm still questionning.

EDIT SPLIT thread, because my first question is (auto)answered. I will add some information about Alfresco, auto commit and managed transaction if I get some.

read-write mode issue : JBoss/Alfresco cannot write on slave after failover

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.