MySQL Bugs: #81128: Master host list overwritten by slave list when loadBalanceConnectionGroup used

Bug #81128	Master host list overwritten by slave list when loadBalanceConnectionGroup used
Submitted:	18 Apr 2016 13:41	Modified:	21 Apr 2016 15:00
Reporter:	Mathieu ROUSSELLE	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.38	OS:	Any
Assigned to:	Filipe Silva	CPU Architecture:	Any
Tags:	loadBalanceConnectionGroup, master host list, replication

Description:
In 5.1.38 release, replication proxy connections have been revamped to use internally 2 LoadBalanced proxies.

Slaves LoadBalanced proxy is initialized before Master one to allow masterDownConnection if specified in the connection string.

When loadBalanceConnectionGroup is defined in the connection string, during the master Loadbalanced proxy, master host list is replaced by loadBalanceGroup hosts (slaves one)

In LoadBalancedConnectionProxy the faulty code is line 135 :

if (group != null) {
  this.connectionGroup = ConnectionGroupManager.getConnectionGroupInstance(group);
  if (enableJMX) {
    ConnectionGroupManager.registerJmx();
  }
  this.connectionGroupProxyID = this.connectionGroup.registerConnectionProxy(this, hosts);
  hosts = new ArrayList<String>(this.connectionGroup.getInitialHosts());
}

How to repeat:
Configure connection string like this :

jdbc:mysql:replication//master,slave1,slave2/schema?loadBalanceConnectionGroup=group

Monitor write operation on cluster nodes (ex. via a StatementInterceptor):
 public ResultSetInternalMethods preProcess(String sql, Statement interceptedStatement, Connection connection) throws SQLException {

        if (LOG.isDebugEnabled()) {
            if (sql != null) {
                String sqlLower = sql.toLowerCase();
                if (sqlLower.contains("update")
                        || sqlLower.contains("insert")
                        || sqlLower.contains("delete")) {
                    LOG.logDebug("Writing to:" + connection.getHost());
                }
            } else {
                if (interceptedStatement instanceof PreparedStatement) {
                    PreparedStatement preparedStatement=(PreparedStatement)interceptedStatement;
                    if (!preparedStatement.isSelectQuery()) {
                        LOG.logDebug("Writing to:" + connection.getHost());
                    }
                }
            }
        }

        return null;
    }

Connector/J is writing to slaves...

Suggested fix:
Makes LoadBalancerProxy use ReplicationConnectionGroup instead of ConnectionGroup when used as Slave or Master connection for ReplicationConnectionProxy.

Hi Mathieu,

Thank you for this bug report.

This situation here is that you can't set the property 'loadBalanceConnectionGroup' in replication aware connections. All dynamic hosts management has to be done through the class 'com.mysql.jdbc.ReplicationConnectionGroupManager' or it's JMX counterpart 'com.mysql.jdbc.jmx.ReplicationGroupManager' while setting the property 'replicationConnectionGroup', as documented here (http://dev.mysql.com/doc/connector-j/5.1/en/connector-j-master-slave-replication-connectio...).

I just noticed that the property 'replicationConnectionGroup' is not listed in the connection properties page and this should be fixed.

For now, what we can do is to ignore the option 'loadBalanceConnectionGroup' on replication connections so that a misconfiguration won't cause more troubles. Is there any other reason I'm not seeing for you to want to use the option 'loadBalanceConnectionGroup' in such case?

Thanks again,

Hi Filipe,

In fact there is no reason to use "loadBalanceConnectionGroup" more than access to JMX bean to add/remove hosts.

Thanks for pointing me the replicationConnectionGroup option. It should fit our needs.

In fact we were stuck on this issue because we switched our configuration from a multi-master Galera cluster to a Master/Slave cluster with the replication driver and the loadBalanceConnectionGroup option remained in the connection string. I spent 1 day firguring why the driver was writing to slaves until I found the side effect of this option.

I think that ignoring this option in replication mode may be an improvment.

Best regards

Thanks Mathieu.

I'm setting this report as verified and as for the fix we should:
- add the documentation for the connection property 'replicationConnectionGroup'
- ignore the option 'loadBalanceConnectionGroup' in replication connections.