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

Question

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.

Answer 1

Case 1 - JDBC connects as [email protected] 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 [email protected]) 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 [email protected] 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

Answer 2

Which MySQL Version you are using ?

You won't be able to use root user I guess.

Create new application user and use that user for application.

CREATE USER appuser@'%' identified by 'PASSWORD';
Grant SELECT on *.* to appuser@'%';
flush privileges;