mysql master/slave replication issue: Cannot create PoolableConnectionFactory

Question

I have spent a lot of time but could not figure it out. I know that, If I use the BasicDataSource then the configuration needs to passed in the openjpa.ConnectionProperties property. The openjpa.ConnectionProperties is comma (,) separated properties which get mapped to the DataSource instance. Now MySQL also expects the hosts in comma (,) separated format. So not able to figure out, How do I create an DataSource with MySQL replication?*

I am trying to setup master/slave db with openjpa and it fails with the below exception while creating the createEntityManager() from the EntityManagerFactory.

Here is the code:

String driver ="com.mysql.jdbc.ReplicationDriver";
String url = "jdbc:mysql:replication://master:3306,slave:3306/db";
String user = "abc";
String password = "123";
String connProps = "DriverClassName={0},Url={1},Username={2},Password={3}";

public void method() {
    connProps = MessageFormat.format(connProps, driver, url, user, password);
    Properties props = new Properties();
    props.setProperty("openjpa.ConnectionProperties", connProps);
    props.setProperty("openjpa.ConnectionDriverName", "org.apache.commons.dbcp.BasicDataSource");
    EntityManagerFactory factory = Persistence.createEntityManagerFactory("mysql", props);
    EntityManager manager = factory.createEntityManager();
}

I getting the following exception:

Exception in thread "main" <openjpa-2.4.1-r422266:1730418 fatal general error> org.apache.openjpa.persistence.PersistenceException: Cannot create PoolableConnectionFactory (Must specify at least one slave host to connect to for master/slave replication load-balancing functionality)
    at org.apache.openjpa.jdbc.sql.DBDictionaryFactory.newDBDictionary(DBDictionaryFactory.java:106)
    at org.apache.openjpa.jdbc.conf.JDBCConfigurationImpl.getDBDictionaryInstance(JDBCConfigurationImpl.java:603)
    at org.apache.openjpa.jdbc.meta.MappingRepository.endConfiguration(MappingRepository.java:1520)
    at org.apache.openjpa.lib.conf.Configurations.configureInstance(Configurations.java:533)
    at org.apache.openjpa.lib.conf.Configurations.configureInstance(Configurations.java:458)
    at org.apache.openjpa.lib.conf.PluginValue.instantiate(PluginValue.java:121)
    at org.apache.openjpa.conf.MetaDataRepositoryValue.instantiate(MetaDataRepositoryValue.java:68)
    at org.apache.openjpa.lib.conf.ObjectValue.instantiate(ObjectValue.java:83)
    at org.apache.openjpa.conf.OpenJPAConfigurationImpl.newMetaDataRepositoryInstance(OpenJPAConfigurationImpl.java:967)
    at org.apache.openjpa.conf.OpenJPAConfigurationImpl.getMetaDataRepositoryInstance(OpenJPAConfigurationImpl.java:958)
    at org.apache.openjpa.kernel.AbstractBrokerFactory.makeReadOnly(AbstractBrokerFactory.java:642)
    at org.apache.openjpa.kernel.AbstractBrokerFactory.newBroker(AbstractBrokerFactory.java:202)
    at org.apache.openjpa.kernel.DelegatingBrokerFactory.newBroker(DelegatingBrokerFactory.java:154)
    at org.apache.openjpa.persistence.EntityManagerFactoryImpl.createEntityManager(EntityManagerFactoryImpl.java:226)
    at org.apache.openjpa.persistence.EntityManagerFactoryImpl.createEntityManager(EntityManagerFactoryImpl.java:153)
    at org.apache.openjpa.persistence.EntityManagerFactoryImpl.createEntityManager(EntityManagerFactoryImpl.java:59)
    at ExampleJPA.method(ExampleJPA.java:22)
    at ExampleJPA.main(ExampleJPA.java:27)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Must specify at least one slave host to connect to for master/slave replication load-balancing functionality)
    at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
    at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
    at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
    at org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:110)
    at org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:86)
    at org.apache.openjpa.jdbc.sql.DBDictionaryFactory.newDBDictionary(DBDictionaryFactory.java:90)
    ... 22 more
Caused by: java.sql.SQLException: Must specify at least one slave host to connect to for master/slave replication load-balancing functionality
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
    at com.mysql.jdbc.NonRegisteringDriver.connectReplicationConnection(NonRegisteringDriver.java:414)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:313)
    at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
    at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
    at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
    at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
    ... 27 more

The master slave setup of the database is seems to working fine. I have checked the connectivity of the slave from master through telnet.

Answer 1

This solution seems silly after spending so much time on it. I got this working. I need to pass the jdbc url with double quotes ("jdbc:mysql:replication://master:3306,slave:3306/db") and then works fine.

I will provide the more information soon.

Answer 2

i think the error's reason is using openjpa.ConnectionProperties to set properties in one string, with a comma as separator, while the property URL have a value that also is comma separated

as per @rkosegi answer, something is messed up related to the comma "," to solve that i think you have to use separated properties not 1 string. or at least consider removing Url={1}, and set that using openjpa.ConnectionURL

here is what i think you need to do in method():

props.setProperty("openjpa.ConnectionDriverName", "org.apache.commons.dbcp.BasicDataSource");
props.setProperty("openjpa.ConnectionUserName", user);
props.setProperty("openjpa.ConnectionPassword", password);
props.setProperty("openjpa.ConnectionURL", url);

remove the line:

props.setProperty("openjpa.ConnectionProperties", connProps);

don't use String connProps = "DriverClassName={0},Url={1},Username={2},Password={3}"; anymore.

P.S: just a heads-up you are using "org.apache.commons.dbcp.BasicDataSource" not driver parameter. just make sure that is what you want.

Answer 3

JDBC URL passed to MySQL driver is not what you think is. MySQL driver is trying to parse URL and split it using "," token

    if ((hostStuff != null) && (hostStuff.trim().length() > 0)) {
        List<String> hosts = StringUtils.split(hostStuff, ",", ALLOWED_QUOTES, ALLOWED_QUOTES, false);

but it can't find more then one, so it yields error:

    int numHosts = Integer.parseInt(parsedProps.getProperty(NUM_HOSTS_PROPERTY_KEY));

    if (numHosts < 2) {
        throw SQLError.createSQLException("Must specify at least one slave host to connect to for master/slave replication load-balancing functionality",
                SQLError.SQL_STATE_INVALID_CONNECTION_ATTRIBUTE, null);
    }

Most probably JDBC URL is modified/broken by DBCP or OpenJPA.

Link to source