Hikaricp: Problems with MySQL master/slave allowMasterDownConnections

[jgaiewski]

Hi all,

We started using HikariCP a couple weeks ago and, in general, we are very satisfied with the results. We have noticed a problem though, related to MySQL "allowMasterDownConnections" property - which was working perfectly before introducing HikariCP.

The problem happens when loosing (or shutting down) our master DB, we can't get a (read-only) connection from our slave DB.

When the property "allowMasterDownConnections" is TRUE, Hikari should be able to retrieve connections to our slave DB (read-only). Instead of that, we get an exception.

Investigating a little bit further, we saw that our HikariConfig has a flag "isReadOnly", by default, set to FALSE (which makes sense when master DB is active). This flag is _final_, thus it will be always FALSE.
When master DB is down, MySQL JDBC Connector will switch over to slave DB. By doing this, MySQL JDBC connection's "readOnly" property is internally set to TRUE. So, MySQL JDBC returns a read-only connection to Hikari.
In "PoolBase.java", after getting this read-only connection, Hikari calls the method "setupConnection", which tries to change the connection "readOnly" to FALSE, as seen in the code below:

private void setupConnection(final Connection connection) throws SQLException
{
    if (networkTimeout == UNINITIALIZED) {
        networkTimeout = getAndSetNetworkTimeout(connection, validationTimeout);
    } else {
        setNetworkTimeout(connection, validationTimeout);
    }

    checkDriverSupport(connection);

    connection.setReadOnly(isReadOnly);
    ...

And exactly at that point, an exception is thrown (see full exception on the bottom).

So it seems that Hikari is forcing the connection to be readOnly=FALSE, even when master DB is down, which is causing some trouble.

I would appreciate your attention and ideas about this problem.

PS:

• MySQL Connector/Java 5.1.38
• Hikari 2.4.5
• HikariConfig:

    HikariConfig c = new HikariConfig();

    c.setUsername(...);
    c.setPassword(...);
    c.setRegisterMbeans(true);
    c.setMetricRegistry(...);

    c.setValidationTimeout(SECONDS.toMillis(1));
    c.setConnectionTimeout(SECONDS.toMillis(5));
    c.setIdleTimeout(SECONDS.toMillis(60));
    c.setMaxLifetime(SECONDS.toMillis(120));

    c.setDriverClassName("com.mysql.jdbc.Driver");

    c.setPoolName(POOL_NAME);
    c.setMinimumIdle(minPoolSize);
    c.setMaximumPoolSize(maxPoolSize);
    c.setJdbcUrl("jdbc:mysql:replication://master,slave/data_base?characterEncoding=UTF-8" +
        "&useServerPrepStmts=false" +
        "&exceptionInterceptors=ConnectionInfoExceptionInterceptor" +
        "&allowMasterDownConnections=true" +
        "&connectTimeout=1000" +
        "&socketTimeout=10000");

    dataSource = new HikariDataSource(c);

• Exception:

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_66]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_66]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_66]
at java.lang.reflect.Constructor.newInstance(Constructor.java:422) ~[?:1.8.0_66]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:981) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.MysqlIO.(MysqlIO.java:339) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2253) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2286) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2085) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:795) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:44) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at sun.reflect.GeneratedConstructorAccessor70.newInstance(Unknown Source) ~[?:?]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_66]
at java.lang.reflect.Constructor.newInstance(Constructor.java:422) ~[?:1.8.0_66]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.MultiHostConnectionProxy.createConnectionForHost(MultiHostConnectionProxy.java:341) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.LoadBalancedConnectionProxy.createConnectionForHost(LoadBalancedConnectionProxy.java:357) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.RandomBalanceStrategy.pickConnection(RandomBalanceStrategy.java:73) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.LoadBalancedConnectionProxy.pickNewConnection(LoadBalancedConnectionProxy.java:302) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.LoadBalancedConnectionProxy.(LoadBalancedConnectionProxy.java:214) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.LoadBalancedConnectionProxy.createProxyInstance(LoadBalancedConnectionProxy.java:101) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.NonRegisteringDriver.connectLoadBalanced(NonRegisteringDriver.java:371) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:311) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.ReplicationConnectionProxy.initializeMasterConnection(ReplicationConnectionProxy.java:369) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.ReplicationConnectionProxy.switchToMasterConnection(ReplicationConnectionProxy.java:416) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.ReplicationConnectionProxy.setReadOnly(ReplicationConnectionProxy.java:589) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.ReplicationMySQLConnection.setReadOnly(ReplicationMySQLConnection.java:90) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at sun.reflect.GeneratedMethodAccessor66.invoke(Unknown Source) ~[?:?]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_66]
at java.lang.reflect.Method.invoke(Method.java:497) ~[?:1.8.0_66]
at com.mysql.jdbc.ReplicationConnectionProxy.invokeMore(ReplicationConnectionProxy.java:293) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.MultiHostConnectionProxy.invoke(MultiHostConnectionProxy.java:452) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.sun.proxy.$Proxy43.setReadOnly(Unknown Source) ~[?:?]
at com.zaxxer.hikari.pool.PoolBase.setupConnection(PoolBase.java:344) ~[HikariCP-2.4.5.jar:?]
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:316) ~[HikariCP-2.4.5.jar:?]
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:172) ~[HikariCP-2.4.5.jar:?]
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:439) ~[HikariCP-2.4.5.jar:?]
at com.zaxxer.hikari.pool.HikariPool.access$500(HikariPool.java:66) ~[HikariCP-2.4.5.jar:?]
at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:564) ~[HikariCP-2.4.5.jar:?]
at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:557) ~[HikariCP-2.4.5.jar:?]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_66]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) ~[?:1.8.0_66]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) ~[?:1.8.0_66]
at java.lang.Thread.run(Thread.java:745) ~[?:1.8.0_66]
Caused by: java.net.ConnectException: Connection refused
at java.net.PlainSocketImpl.socketConnect(Native Method) ~[?:1.8.0_66]
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350) ~[?:1.8.0_66]
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206) ~[?:1.8.0_66]
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188) ~[?:1.8.0_66]
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392) ~[?:1.8.0_66]
at java.net.Socket.connect(Socket.java:589) ~[?:1.8.0_66]
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:211) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.MysqlIO.(MysqlIO.java:298) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2253) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2286) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2085) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:795) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:44) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at sun.reflect.GeneratedConstructorAccessor70.newInstance(Unknown Source) ~[?:?]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_66]
at java.lang.reflect.Constructor.newInstance(Constructor.java:422) ~[?:1.8.0_66]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.MultiHostConnectionProxy.createConnectionForHost(MultiHostConnectionProxy.java:341) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.LoadBalancedConnectionProxy.createConnectionForHost(LoadBalancedConnectionProxy.java:357) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.RandomBalanceStrategy.pickConnection(RandomBalanceStrategy.java:73) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.LoadBalancedConnectionProxy.pickNewConnection(LoadBalancedConnectionProxy.java:302) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.LoadBalancedConnectionProxy.(LoadBalancedConnectionProxy.java:214) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.LoadBalancedConnectionProxy.createProxyInstance(LoadBalancedConnectionProxy.java:101) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.NonRegisteringDriver.connectLoadBalanced(NonRegisteringDriver.java:371) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:311) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.ReplicationConnectionProxy.initializeMasterConnection(ReplicationConnectionProxy.java:369) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.ReplicationConnectionProxy.switchToMasterConnection(ReplicationConnectionProxy.java:416) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.ReplicationConnectionProxy.setReadOnly(ReplicationConnectionProxy.java:589) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.ReplicationMySQLConnection.setReadOnly(ReplicationMySQLConnection.java:90) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at sun.reflect.GeneratedMethodAccessor66.invoke(Unknown Source) ~[?:?]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_66]
at java.lang.reflect.Method.invoke(Method.java:497) ~[?:1.8.0_66]
at com.mysql.jdbc.ReplicationConnectionProxy.invokeMore(ReplicationConnectionProxy.java:293) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.mysql.jdbc.MultiHostConnectionProxy.invoke(MultiHostConnectionProxy.java:452) ~[mysql-connector-java-5.1.38.jar:5.1.38]
at com.sun.proxy.$Proxy43.setReadOnly(Unknown Source) ~[?:?]
at com.zaxxer.hikari.pool.PoolBase.setupConnection(PoolBase.java:344) ~[HikariCP-2.4.5.jar:?]
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:316) ~[HikariCP-2.4.5.jar:?]
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:172) ~[HikariCP-2.4.5.jar:?]
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:439) ~[HikariCP-2.4.5.jar:?]
at com.zaxxer.hikari.pool.HikariPool.access$500(HikariPool.java:66) ~[HikariCP-2.4.5.jar:?]
at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:564) ~[HikariCP-2.4.5.jar:?]
at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:557) ~[HikariCP-2.4.5.jar:?]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_66]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) ~[?:1.8.0_66]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) ~[?:1.8.0_66]
at java.lang.Thread.run(Thread.java:745) ~[?:1.8.0_66]

[nitincchauhan]

@jgaiewski set c.setReadOnly(true);

[jgaiewski]

Hi @nitincchauhan,
we have done this already, but thanks for your comment.
Now we run into a different issue...
With readOnly=true, we get connections for a while. But then, at some point (usually after a couple minutes), the pool becomes full. And then no connections are retrieved. Checking why, turns out that all connections are marked as IN_USE (but there is really no connection being actively used). It is hard to say why, and it does not make sense that those connections are in use.
I'm not sure if it this problem is Hikari's fault, though would appreciate any ideas about it.

PS: setting a very small pool size (obviously) causes the problem to happen faster

[brettwooldridge]

@jgaiewski If all connections are showing up IN_USE then there is likely a leak occuring somewhere. You'll need to set the leakDetectionThreshold in order to find it.

Regarding, the MySQL ReplicationDriver and looking at the documentation, it is hard to see how the driver can be made to work well with HikariCP (or any pool, IMHO).

MySQL has chosen to override the standard behavior of the Connection.setReadOnly() method to control whether connections are obtained from the master or slaves, and also whether a current connection should be dropped and automatically balacned to a master or slaves.

HikariCP is unlikely to support this kind of foolishness, but you may find older/simpler pools that do. If you want my opinion, I would create two pools, one for read-only queries and one for read/write queries, and create a load-balancing group over each.

[jgaiewski]

Thanks @brettwooldridge, my final words on this issue are:

MySQL Replication Driver simply does NOT work together with HikariCP. We had to split into 2 pools (1 for read only and 1 for read/write connections) and completely replace MySQL Replication Driver by the simple Driver.
Now things are working better.

[brettwooldridge]

For future readers of this issue, see this post in our discussion group:

https://groups.google.com/d/msg/hikari-cp/KtKgzR8COrE/higEHoPkAwAJ

[DanyUP]

Is this still true? I'm using HikariCP and I need to use Replication Driver with a MySQL cluster.

What issues would I notice with this configuration? Connection would remain "in use", filling up connection pool?

I read the post about using ProxySQL, but I'm afraid DBAs won't approve this solution...

[brettwooldridge]

@DanyUP If you want to use a pool that supports the Replication Driver you're only choice is Tomcat DBCP. It only works because by default it does not reset connection state changes such as application changes to transaction isolation, auto-commit, catalog, or read-only state.

This makes the Tomcat DBCP vulnerable to pool polution, where one thread changing a connection's transaction isolation from READ_COMMITTED to READ_UNCOMMITTED will affect the next thread's use of that connection as well. If your DBAs are willing to go with the MySQL JDBC replication hack ... well, there you go.

There are several replication solutions available that are employed and endorsed by heavy users of MySQL -- including ProxySQL, MariaDB's MaxScale (which works with MySQL), and HAProxy recipes. If your organization is serious about replication I suggest starting with one of these.

[DanyUP]

Thanks @brettwooldridge for your reply.

Can I ask you what issues would rise when using HikariCP with Replication Driver? There would be problems with connection failure management or else?

I've heard of other developers using C3P0 (I don't like it) with Replication Driver. Do you think it's possibile or C3P0 would have the same issues of HikariCP?

I'm sorry to disturb you again but I'm going to make hard decisions and I'm trying to have a full understanding of the problem. Let me know if this is the right place of such questions.
Thanks again!