jboss ha datasource configuration to the same host

[brettcave]

Would something like this work?

I am using heartbeat + ldirector for HA and loadbalancing of sql servers.

I find that jboss applications become unavailable for a while if the connection gets lost - which happens occassionally due to packet loss. So in trying to get a stable jboss + mysql infrastructure, I would like to use
<ha-tx-local-datasource>
<connection-url>jdbc:mysql://ha.host:3306/db|jdbc:mysql://ha.host:3306/db</connection-url>

The logic being that ldirector monitors mysql, and when a node goes down, it will immediately re-route to the other node. The datasource above uses connection pooling, so in the event of disconnection, it should automatically re-establish to the next server, which is the same server.

Should this work?

Also, i read somewhere that using background-validation with check-valid-connection-sql can cause a conflict, but cant find further info on it... any recommendations for detecting whether connections are valid?

lastly, is using jdbc:mysql://ha.host:3306/db?autoReconnect=true the same as leaving "autoReconnect=true" off the URL and adding it to <connection-property> ?

[adrian.brock]

"brettcave" wrote:
Would something like this work?

I am using heartbeat + ldirector for HA and loadbalancing of sql servers.

I find that jboss applications become unavailable for a while if the connection gets lost - which happens occassionally due to packet loss. So in trying to get a stable jboss + mysql infrastructure, I would like to use
<ha-tx-local-datasource>
<connection-url>jdbc:mysql://ha.host:3306/db|jdbc:mysql://ha.host:3306/db</connection-url>

I don't think that will work? Looking at the HAXAConnectionFactory the urls
get parsed to an "XAData" which is treated as equal if the urls are the same.

But then again it stores them in a List rather than a Set so it might still work?

Also, i read somewhere that using background-validation with check-valid-connection-sql can cause a conflict, but cant find further info on it... any recommendations for detecting whether connections are valid?

??? It is duplicate work, I don't know it is a conflict since one works on
idle connections, the other on connections just about to be used (i.e. not idle).

lastly, is using jdbc:mysql://ha.host:3306/db?autoReconnect=true the same as leaving "autoReconnect=true" off the URL and adding it to <connection-property> ?

Completely broken if you use any form of transactions.
AFAIK, the reconnect will use a different connection that knows nothing about previous
work in an ongoing transaction.