Datasource failover, sudden disconnection not working

[sebastianlacuesta]

I have a problem using datasource failover with MySQL. My setup is JBoss at host A, and mysql in host B, replicated in host A. I have followed the instructions in JBossJCADatabaseFailover.
When I stop mysql at machine B with /etc/init.d/mysql stopfailover works flawlessly, but if I disconnect B's network cable, the application just keeps waiting for the query, and no failover is done. The datasource configuration is:

<?xml version="1.0" encoding="UTF-8"?>

<!-- $Id: mysql-ds.xml,v 1.3.2.3 2006/02/07 14:23:00 acoliver Exp $ -->
<!-- Datasource config for MySQL using 3.0.9 available from:http://www.mysql.com/downloads/api-jdbc-stable.html-->


<ha-local-tx-datasource>
<jndi-name>PokUpdateDs</jndi-name>
<connection-url>jdbc:mysql://192.168.10.219:3306/POK2|jdbc:mysql://192.168.10.210:3306/POK2</connection-url>
<url-delimeter>|</url-delimeter>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>JBossAS</user-name>
MyPassword
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<idle-timeout-minutes>10</idle-timeout-minutes>
<blocking-timeout-millis>300</blocking-timeout-millis>
<exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter</exception-sorter-class-name>
<!-- should only be used on drivers after 3.22.1 with "ping" support -->
<valid-connection-checker-class-name>com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name>

<!-- sql to call when connection is created
<new-connection-sql>select sysdate() from dual</new-connection-sql>
-->
<!-- sql to call on an existing pooled connection when it is obtained from pool - MySQLValidConnectionChecker is preferred for newer drivers -->
<check-valid-connection-sql>select sysdate() from dual</check-valid-connection-sql>

<!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->

<type-mapping>mySQL</type-mapping>

</ha-local-tx-datasource>



The hosts are:

A: 192.168.10.210
B: 192.168.10.219

I could keep playing with blocking-timeout-millis, but I prefer to ask before continue. My question is: What should I have to consider in order to solve this? I mean, get failover working when a database stop working suddenly, not by stopping it cleanly.
Thanks.

[weston.price]

Chances are the MySQL driver socket parameters should be tuned to address the case where the DB goes down at the network layer. From what it sounds like, the link goes down and the connections are simply blocked waiting for the socket to timeout.

[sebastianlacuesta]

Correct, I had to add a parameter in the connection url, I set the socketTimeout in 5000 milliseconds in this way:

<connection-url>jdbc:mysql://192.168.10.210:3306/POK2?socketTimeout=5000|jdbc:mysql://192.168.10.219:3306/POK2?socketTimeout=5000</connection-url>

For anyone looking at mysql failover, take a look at here:
http://dev.mysql.com/doc/refman/5.0/es/connector-j-reference-configuration-properties.html
(Not exactly mysql failover, but very usefull)
Thanks