I'm attempting to implement automatic failover via a MySQL Fabric Server Group using a JNDI Resource, Hibernate, and Connection Pooling. Our DB farm is properly setup with MySQL Fabric and I'm attempting to implement the client/connector.
I have a JNDI resource declared in my server.xml file which has a MySQL Fabric-based URL and Driver, and uses connection pooling with hibernate.
<Resource name="jdbc/myApp" type="javax.sql.DataSource" driverClassName="com.mysql.fabric.jdbc.FabricMySQLDriver" maxWait="1000" url="proper.fabric.url/fabricServerGroup=myFabricGroup" maxActive="45" validationQuery="select 1 from dual" />
The problem I'm having is that the connection pooling and Fabric do not play well together. After taking down the master DB, Fabric automatically promotes a slave as expected. However, when I attempt to make a new connection to the DB I'm getting old now-defunct connections back, most notably I'm getting a read-only connection back when I'm requesting readwrite. As additional info if I restart my Tomcat server and ask for a fresh connection I get the correct results. The problem I'm having specifically occurs because of connection pooling.
What I need to know is if there's a key/value pair which I'm missing that would make my FabricDriver smart enough to know to wipe the connection pool when automatic failover occurs.
The best I've found is to use testOnBorrow, which unfortunately can only use a SELECT query (via validationQuery), which still succeeds when I'm getting a read-only connection back despite asking for readwrite.
Has anybody solved this issue before?
Note: If I attempt to use com.mysql.fabric.jdbc.FabricMySQLDataSource I can't even successfully make any connections. The regular javax.sql.DataSource seems to be the correct value here.
Second Note: I'm using mysql-connector-java version 5.1.35