Bug #63354 jdbc cannot make new connections if master is down
Submitted: 21 Nov 2011 11:36 Modified: 6 Nov 2013 23:33
Reporter: Alessandro none Email Updates:
Status: Closed Impact on me:
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.18 OS:Linux
Assigned to: Alexander Soklakov CPU Architecture:Any
Tags: Connections, master, replication

[21 Nov 2011 11:36] Alessandro none
When using jdbc:mysql:replication and a connection url like:


When master goes down, existing connections can still serve read only queries from slaves, but new connections cannot be created even though they will be used for read only queries too.

How to repeat:
Use a master / slave setup and try to setup a connection when master is down.
[1 Jan 2012 14:39] Valeriy Kravchuk
Do you have autoCommit property set to true for new connections that you want to fail over to slaves?
[2 Feb 2012 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[2 Feb 2012 15:07] Alessandro none
Hello. Sorry for the delay.

Here is the Java code we are using:

public class TestMysqlTomcat {
        final static int POOL_SIZE = 5;

        public static void main(String[] args) throws Exception {
            Connection con = null;
            Statement stmt = null;
            ResultSet rs = null;
            PoolProperties p = new PoolProperties();
            p.setConnectionProperties("loadBalanceBlacklistTimeout=5000; ");
            DataSource datasource = new DataSource();

            for(int i = 0; i<100000; i++) {
                try {            
                  con = datasource.getConnection();
                catch(Exception ex) {
                  stmt = con.createStatement();
                  rs = stmt.executeQuery("select field from table limit 1");
                  while (rs.next()) {
                catch(Exception ex) {
                finally {
                  if (rs!=null) try {rs.close();}catch (Exception e) {}
                  if (stmt!=null) try {stmt.close();}catch (Exception e) {}
                  if (con!=null) try {con.close();}catch (Exception e) {}

The code in the case MySql master is down but both slaves are up, gets this exception after datasource.getConnection():

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

As you can see, we tried setting AutoCommit to false and ReadOnly to true in PoolProperties object to force it to connect to a slave.

[10 Apr 2012 9:10] Tonci Grgin

Exception should be expected here

        stmt = con.createStatement();
        rs = stmt.executeQuery("select field from table limit 1");
        while (rs.next()) {
    } catch(Exception ex) {
        ex.printStackTrace(); <<<<

but SQLException, so I'm asking what happens if you remove the code in trap and changes what you catch like this:
        con.commit(); //remove!
    } catch(SQLException e) {
        //expected, do nothing, wait for failover

this conn.commit(); call is puzzling as there's nothing to commit so removed.
[17 Apr 2012 14:22] Alessandro none
Hello and thanks.

Removing the commit() after the select statement make it work as expected. That is, if the Master goes down, select are still served by the Slaves and also when we shut down a Slave, the driver correctly start using the other one and it also recognizes when a Slave is back up. For failover, this is very good.

But it does not do load balancing over the Slaves. If we add the commit() call, connections are being load balanced over the Slaves. That is, if we do 100k selects, 50k are handled by Slave1 and 50k by Slave2 if we use commit(). But in this case, if the Master goes down, read-only connections stop working too.

We need to distribute the select and to have good failover. Is it possible? Is there another way?

[17 Apr 2012 14:47] Tonci Grgin
Alessandro, there is no load-balance strategy chosen... Please see examples in manual or search the web for this, say, http://mysqlblog.fivefarmers.com/2012/02/24/connectorj-extension-points-load-balancing-str...
[17 Apr 2012 16:36] Alessandro none
Hello and thanks for the pointer.

We made some more test, both with replication and the standard driver with jdbc:mysql:loadbalance://
In all the cases and even specifying a loadBalanceStrategy to the driver, the distribution of the select statements to more than one slave server happens only if we add an explicit commit() statement after the select. If we don't add this commit(), all the select are sent to a single server. Fault tolerance works as expected both for slaves and master server.

Are we doing something really wrong here?

Also, if the master is down before we start the program, connection is not established to the slave servers but the initialization aborts with an exception. This means that if we have an hardware problem on the master server and Tomcat servers are restarted, they won't be able to work even though they only needed read only connections.

[17 Apr 2012 17:08] Tonci Grgin
Alessandro, yes, COMMIT is the boundary. You can not balance between servers without some clear delimiter saying that it is safe to go to other server. For the rest, I will have to check.
[6 Mar 2013 10:32] Alexander Soklakov
It's really impossible to establish replication connection when master is down. We should fix this.
[2 Aug 2013 21:38] vinay pothnis
Hello, Any update/progress on this?
[5 Aug 2013 10:52] Alexander Soklakov
Hi Vinay,

The work is in progress, but the solution is not ready yet.
[5 Aug 2013 12:01] Alexander Soklakov
Hi Alessandro,

Did you resolve the problem with load-balance on slaves? Autocommit=true and loadBalanceAutoCommitStatementThreshold > 0 should help with this.
[6 Aug 2013 15:33] vinay pothnis
Thank you for the update Alexander!
[5 Nov 2013 12:13] Alexander Soklakov
Connector/J 5.1.27 introduced new multi-master support in replication connection. Please, take a look at Todd's post for details: http://mysqlblog.fivefarmers.com/2013/11/04/multi-master-support-in-mysql-connectorjava/

The problem described in this bug report was resolved as a part of this update,  new connection property allowMasterDownConnections=[false|true] now allows to choose the behavior, whether you still want to get failure on connection establishing when master is down or connect as read-only in such case.
[6 Nov 2013 23:33] Daniel So
Augmented an earlier changelog entry for a closely related problem to get this bug covered:

"In a replication-aware deployment, when the master was not reachable, new connections could not be made, or the replication driver just hanged. As part of the new multiple-master support feature, users can now set the property allowMasterDownConnections=true to allow connections to be established even when no master hosts are available."