JBoss don't fail over MySQL slave using datasource

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 : https://stackoverflow.com/questions/9409111/database-fail-over-in-jboss-data-sources)

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




alfresco-datasource
jdbc:mysql://10.1.2.13,10.1.2.14:3306/alfresco
org.gjt.mm.mysql.Driver
alfresco
alfresco

org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter


false
false

SELECT 1


mySQL





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 fr

Indeed it seems that I "forgot" one parameter

true


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 : https://stackoverflow.com/questions/10945261/jboss-alfresco-cannot-write-on-slave-after-failover

StackExchange Database Administrators Q#19453, answer score: 2