C3P0 Pool connects to the wrong MySQL server

Question

I have a strange case on C3P0 connection pool configured with Hibernate JPA via persistence.xml.

This is my persistence.xml:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit name="public_db" transaction-type="RESOURCE_LOCAL">

        <description>MySQL Persistence Unit</description>
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

        <properties>
            <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
            <property name="javax.persistence.jdbc.url" value="jdbc:mysql://AAA.BBB.CCC.DDD:3306/public_db" />
            <property name="javax.persistence.jdbc.user" value="USERNAME" />
            <property name="javax.persistence.jdbc.password" value="SECRET" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />

            <property name="hibernate.hbm2ddl.auto" value="update" />
            <property name="hibernate.show_sql" value="true" />
            <property name="hibernate.format_sql" value="true" />
            <property name="hibernate.transaction.flush_before_completion" value="false" />

            <property name="hibernate.connection.provider_class"
                      value="org.hibernate.connection.C3P0ConnectionProvider" />
            <property name="hibernate.c3p0.min_size" value="5" />
            <property name="hibernate.c3p0.max_size" value="50" />
            <property name="hibernate.c3p0.timeout" value="500" />
            <property name="hibernate.c3p0.max_statements" value="50" />
            <property name="hibernate.c3p0.idle_test_period" value="2000" />
        </properties>
    </persistence-unit>

<persistence>

After building app JAR packages, if I run on local machine, it works, it can connect to the MySQL Server at AAA.BBB.CCC.DDD.

However, if I execute the JAR app on a remote server, I get error:

    [2017-05-19 11:11:02,509 +0700] [DEBUG] [c.m.v.r.BasicResourcePool] An exception occurred while acquiring a poolable resource. Will retry.
    java.sql.SQLException: Access denied for user 'USRENAME'@'AAA.BBB.CCC.EEE' (using password: YES)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
            at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970)
            at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)
            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:2253)
            at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2284)
            at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2083)
            at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:806)
            at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
            at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
            at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
            at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
            at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
            at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
            at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:410)
            at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:328)
            at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
            at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
            at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
            at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
            at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
            at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
            at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
            at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
            at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)

Strange thing that I configured the server at AAA.BBB.CCC.DDD but the C3P0 data source is trying to connect to the MySQL Server at remote host, which is AAA.BBB.CCC.EEE.

I don't set any place in code with localhost or AAA.BBB.CCC.EEE.

Has anyone experienced this?

Answer 1

After a while digging, this looks like a problem of network and DB configuration. The DB account when running on remote host doesn't have permission to access to DB server. So just fix the permission then it works!

The weird thing of this problem is that if DB connection timeout on C3P0, it falls back to pooling on current machine, so the error message on DB IP address is very hard to dissect the problem.