MySQL Bugs: #63354: jdbc cannot make new connections if master is down

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:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.18	OS:	Linux
Assigned to:	Alexander Soklakov	CPU Architecture:	Any
Tags:	Connections, master, replication

Description:
When using jdbc:mysql:replication and a connection url like:

jdb:mysql:replication://master,slave1,slave2/testdb

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.

Do you have autoCommit property set to true for new connections that you want to fail over to slaves?

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".

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.setUrl("jdbc:mysql:replication://192.168.10.155,192.168.10.156,192.168.10.157/test1");
            p.setDriverClassName("com.mysql.jdbc.ReplicationDriver");
            p.setUsername("test");
            p.setPassword("testpwd");
            p.setMaxActive(POOL_SIZE);
            p.setInitialSize(5);
            p.setMaxIdle(POOL_SIZE);
            p.setMinIdle(1);
            p.setConnectionProperties("loadBalanceBlacklistTimeout=5000; ");
            p.setDefaultAutoCommit(false);
            p.setDefaultReadOnly(true);
            DataSource datasource = new DataSource();
            datasource.setPoolProperties(p); 

            for(int i = 0; i<100000; i++) {
                try {            
                  con = datasource.getConnection();
                }
                catch(Exception ex) {
                    ex.printStackTrace();
                }
                try{
                  stmt = con.createStatement();
                  rs = stmt.executeQuery("select field from table limit 1");
                  while (rs.next()) {
                      System.out.println(rs.getString("field"));
                  }
                  rs.close();
                  stmt.close();
                  con.commit(); 
                } 
                catch(Exception ex) {
                    ex.printStackTrace();
                }
                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.

Thanks

Alessandro,

Exception should be expected here

    try{
        stmt = con.createStatement();
        rs = stmt.executeQuery("select field from table limit 1");
        while (rs.next()) {
            System.out.println(rs.getString("field"));
        }
        rs.close();
        stmt.close();
        con.commit(); 
    } 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.

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?

Thanks

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...

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.

Thanks,
Alessandro

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.

It's really impossible to establish replication connection when master is down. We should fix this.

Hello, Any update/progress on this?

Hi Vinay,

The work is in progress, but the solution is not ready yet.

Hi Alessandro,

Did you resolve the problem with load-balance on slaves? Autocommit=true and loadBalanceAutoCommitStatementThreshold > 0 should help with this.

Thank you for the update Alexander!

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.

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."