Mysql – Unable to connect to Mysql via JDBC but works fine with Mysql

jdbcMySQL

I have a mysql server running locally to which I am able to connect using 

mysql -h localhost -u root

It does not require a password. But when I try to connect with the same attributes (jdbcurl = "jdbc:mysql://localhost:3306/mysql", user = root) via JDBC, I get this weird exception

he last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
    at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
    at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:91)
    at com.mysql.cj.NativeSession.connect(NativeSession.java:144)
    at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:956)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:826)
    ... 33 more
Caused by: java.net.ConnectException: Connection refused (Connection refused)
    at java.base/java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.base/java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:399)
    at java.base/java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:242)
    at java.base/java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:224)
    at java.base/java.net.SocksSocketImpl.connect(SocksSocketImpl.java:403)
    at java.base/java.net.Socket.connect(Socket.java:591)
    at com.mysql.cj.protocol.StandardSocketFactory.connect(StandardSocketFactory.java:155)
    at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:65)
    ... 36 more

What could be the issue here?

EDIT

Tried out the suggestion given in the comments to try to connect to mysql database via mysql as being done in java.

mysql -h localhost -u root mysql

This works and I get connected to the mysql db.

Best Answer

Case 1 - JDBC connects as root@127.0.0.1 but privilege is not granted:

It is possible that your mysql CLI is connecting via Unix domain socket (as root@localhost) while your Java application attempts to connect via TCP (as root@127.0.0.1) but privilege is not granted.

To verify, run status command in the mysql client:

mysql -h localhost -u root -e 'status'

The Connection field should show if you are connecting to Localhost via UNIX socket or 127.0.0.1 via TCP/IP

If you are connecting via UNIX socket, you may try forcing the client to use TCP instead:

mysql -h localhost -u root --protocol TCP -e 'status'

If it fails, you may check if you have the user allowed from the host 127.0.0.1:

SELECT * FROM mysql.user WHERE Host = '127.0.0.1'\G

An empty result set indicates that you don't have that user root@127.0.0.1 yet. You may create it with:

CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY '';
GRANT ALL ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;

You may then retry the TCP and Java application.

Case 2 - localhost translated to IPv6

If you have no problem connecting via TCP as described above, then another possibility is that the localhost string is translated by your Java application to the IPv6 ::1 while your MySQL server isn't configured to listen to the IPv6 interface.

You may try using 127.0.0.1 in your jdbcurl, e.g.: jdbc:mysql://127.0.0.1:3306/mysql.

If for some reasons you cannot change jdbcurl, you may try launching your Java application with the -Djava.net.preferIPv4Stack=true option, e.g.:

java -Djava.net.preferIPv4Stack=true -jar myapp.jar
Related Question