MySQL Fabric failover and Connection Pooling with Hibernate

Question

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

score 1 · Accepted Answer · answered Jun 09 '15 at 23:36

1

Just heard back from Oracle, this is apparently a known bug and is going to be fixed in the upcoming release: 5.1.36

answered Jun 09 '15 at 23:36

StevenNord

11
2

score 0 · Answer 2 · answered Aug 04 '16 at 13:37

I have the same issue with Oracle MySQL Connector/J 5.1.39. I am able to follow the failover requesting a new connection to Fabric only catching the Exception in my code:

...
} catch(java.sql.SQLException e) {
        int errorCode = e.getErrorCode();
        /*
         * java.sql.SQLException: The MySQL server is running with the --read-only option so it cannot execute this statement
         * java.sql.SQLException: Cannot execute statement in a READ ONLY transaction.
         *
         */
        if(errorCode == 1290 || errorCode == 1792) {
            System.out.println("Failover");
            System.out.println(e.toString());
            connection.close();

            rawConnection = DriverManager.getConnection(baseUrl, mysql_user, mysql_password);
            connection = (FabricMySQLConnection) rawConnection;
}

Asking Oracle support about the automatic failover from the connector side they answer "Connector/J doesn't have desired feature".

There is someone able to integrate that catching inside the connection pool?

MySQL Fabric failover and Connection Pooling with Hibernate

2 Answers2