Local and Remote connection pools, Java DBCP

Question

I have the following use-case: I have a system that needs to use two different connection pools, One is for 'local' database (Meaning a database running on the local machine) and the other one is a 'remote' database. (Meaning a database that is running on a remote different server)

The remote database is a configuration sharing database, while the local one is has different kinds of data.

I've created two classes in order to connect to those datababase:

public class ConnectionPool {

    private static BasicDataSource ds = createNewDatasource();

    private static BasicDataSource createNewDatasource() {
        BasicDataSource ds = new BasicDataSource();

        String url = "jdbc:mysql://127.0.0.1:3306/SOME_DB"
        ds.setUrl(url);
        ds.setUsername(...);
        ds.setPassword(...);

        return ds;
    }

    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

}

The other class looks exactly the same, Only it's called RemoteConnection and the url is changed to:

String url = "jdbc:mysql://<REMOTE_IP>:3306/SOME_DB_2"

Running the above classes, I keep receiving the following message in my logs:

ERROR (RemoteConnection.java:40) - Failed on getConnection
java.sql.SQLException: Cannot create PoolableConnectionFactory (Access denied for user '...'@'<MACHINE_LOCAL_IP>' (using password: YES))
at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2291)
at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2038)
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1533)
at Censored.RemoteConnection.getConnection(RemoteConnection.java:59)
...
Caused by: java.sql.SQLException: Access denied for user '...'@'<MACHINE_LOCAL_IP>' (using password: YES)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:873)
at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1710)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1226)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2188)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2219)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2014)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:776)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.GeneratedConstructorAccessor31.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:57)
at java.lang.reflect.Constructor.newInstance(Constructor.java:437)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:39)
at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:256)
at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:2301)
at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2287)
...

Firstly, The error above is weird. I've never used the machine local IP. So I don't understand where it came from. In addition, It doesn't seems like a privilege problem since I've tried logging into remote database through cli, using:

mysql -u'...' -p'...' -h <REMOTE_IP> SOME_DB_2

And it connected successfully. It smells to me like a JDBC Driver or connection definition problem but I can't seem to find the problematic spot.

Any idea what I'm doing wrong?

score 0 · Accepted Answer · answered Feb 28 '19 at 16:50

I've found the problem. It was an SSL issue. The user I've used had to connect with SSL certificate.

That's also the reason why I've seen the local machine IP in the error.

A way to check is:

Select * from mysql.user where user='...';

What I've found was that:

ssl_type != ''

So I had to define:

jdbc:mysql://<REMOTE_IP>:3306/SOME_DB_2&useSSL=true

Configure the relevant certificates, and all was well.

Local and Remote connection pools, Java DBCP

1 Answers1