Glassfish cannot connect to remote MySQL but localhost can

Question

please give me a chance to explain my mysterious situation.

My Glassfish server cannot connect to the remote MySQL database.

I checked following instructions

1. ping to remote host - OK
2. connect via mysql command (mysql -h remote -u user -p) - OK
3. ssh port forwarding (ssh -L13306:localhost:3306 remote) and configure the glassfish to use it (jdbc://127.0.0.0:13306/mydb) - OK

I think my configurations (server name, database name, user name and password) are correct. But still cannot connect to the database.

My environment:

local side

• Ubuntu 17.04
• Oracle Java 1.8.0 u131
• Glassfish 4.1 (build 13)
• MySQL Connector/J 5.1.41

remote side

• FreeBSD 11
• MySQL 5.5.46

I have googled last night, but cannot found the answer.

28 Apr. Edited:

My server.log says:

[2017-04-26T09:03:07.932+0900] [glassfish 4.1] [WARNING] [test.connection.pool.failed] [javax.enterprise.resource.resourceadapter.com.sun.enterprise.connectors.service] [tid: _ThreadID=98 _ThreadName=admin-listen
er(6)] [timeMillis: 1493164987932] [levelValue: 900] [[
  RAR8054: Exception while creating an unpooled [test] connection for pool [ MysqlMacms ], Connection could not be allocated because: 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.]]

[2017-04-26T09:03:07.940+0900] [glassfish 4.1] [SEVERE] [] [org.glassfish.admingui] [tid: _ThreadID=49 _ThreadName=admin-listener(5)] [timeMillis: 1493164987940] [levelValue: 1000] [[
  RestResponse.getResponse() gives FAILURE.  endpoint = 'http://localhost:4848/management/domain/resources/ping-connection-pool.json'; attrs = '{id=admtkMysqlMacms}']]

Answer 1

The first reason for the connection failure might is your jdbc url. You show

jdbc://127.0.0.0:13306/mydb

Although 172.0.0.0 is a valid loopback address (all 127.0.0.0/8 are) localhost normally is configured to be 127.0.0.1. You are missing as well the mysql specification:

So change that to

jdbc:mysql://127.0.0.1:13306/mydb

The second possible reason for the failure: How is access to mysql configured? Has the user access when connecting from 127.0.0.1? When glassfish connects to the local ssh tunnel endpoint, for the mysql server it looks as somebody from it's mysql-server-localhost is connecting.

Can you login into the server that glassfish is running on and do a

mysql -h 127.0.0.1 --port 13306 -u user -p

from there. If this is not working you check the access configuration for users accessing from localhost and 127.0.0.1

the third reason might be a missing installation of the mysql connector library in Glassfish. This is described in the mysql doc, basically:

Once GlassFish is installed, make sure it can access MySQL Connector/J. To do this, copy the MySQL Connector/J jar file to the domain-dir/lib directory. For example, copy mysql-connector-java-5.1.30-bin.jar to C:\glassfish-install-path\domains\domain-name\lib. Restart the GlassFish Application Server. For more information, see “Integrating the JDBC Driver” in GlassFish Server Open Source Edition Administration Guide, available at GlassFish Server Documentation.

Update

added the :mysql: part in the connection url and reference to connector installation.

Answer 2

I tried it again, the problem no longer reproduced.

I am not sure if my configuration somewhere wrong.