Cannot connect to MySQL on localhost using JDBC Driver

Question

I'm unable to connect to a MySQL instance running on my own machine, but I don't understand enough about the situation to diagnose the issue.

public void readDataBase() throws Exception {
    Connection connection = null;
    Class.forName("com.mysql.jdbc.Driver");
   
    String url = "jdbc:mysql://localhost:3306/tmlschedule";
    
    try {

        connection = DriverManager.getConnection(url, "user", "pass");
   
    } catch (Exception e) {
        throw e;
    } finally {
        close();
    }
}

The call to .getConnection() throws with the following stack trace:

Exception in thread "main"
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
Could not create connection to database server.     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:411)     at
com.mysql.jdbc.Util.getInstance(Util.java:386)  at
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)  at
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)   at
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)   at
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)   at
com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2570)
    at
com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2306)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:839)    at
com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)  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:411)     at
com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:421)  at
com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:350)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)     at
java.sql.DriverManager.getConnection(DriverManager.java:247)    at
tmlSchedule.MySQLAccess.readDataBase(MySQLAccess.java:22)   at
tmlSchedule.TmlSchedule.main(TmlSchedule.java:9) Caused by:
java.lang.NullPointerException  at
com.mysql.jdbc.ConnectionImpl.getServerCharacterEncoding(ConnectionImpl.java:3281)
    at com.mysql.jdbc.MysqlIO.sendConnectionAttributes(MysqlIO.java:1940)
    at
com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1866)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1252)    at
com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2488)
    at
com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2521)
    ... 14 more

What I've done so far:

• Confirm table name, user name, and password. I can use all these variables to log in to the mysql shell (mysql -u user -p) and run select & insert commands.
• Replace the username and password with the login details for the root user on the mysql instance.
• Reference mysql-connector-java.jar in the Java Build Path menu on Eclipse
• Add mysql as a trusted service via the GUI for firewalld.
• Running the code with the firewall disabled (sudo systemctl stop firewalld). This made no difference.
• Check that mysql is listening on port 3306. sudo netstat -tuplen includes the following entry: tcp6 0 0 :::3306 :::* LISTEN 27 113879 7224/mysqld

Any help would be much appreciated.

EDIT:

I'm getting very confused trying to figure out versioning on MySQL and the driver.

$ zipgrep 'Bundle-Version' /usr/share/java/mysql-connector-java.jar returns:

META-INF/MANIFEST.MF:Bundle-Version: 5.1.25`

$ mysql --version returns:

mysql Ver 8.0.12 for Linux on x86_64 (MySQL Community Server - GPL)

This suggests that the driver is out of date, but I installed MySQL via the yum repo listed here, which I would imagine would provide me with a driver to match the database. I also tried to install the Connector/J 8.0 via the rpm package listed here, but I get the following message, which just doesn't make sense to me:

package mysql-connector-java-1:5.1.25-3.el7.noarch (which is newer than mysql-connector-java-8.0.12-1.el7.noarch) is already installed

Answer 1

An application may not be able to connect to a database due to many reasons. One of them is not using a correct driver software (aka JDBC driver).

In this case verifying the usage of the jdbc driver version compatible with the database server itself solved the issue.

One can get a correct driver software from here.

One can verify the MySQL server version from command prompt: first, start a MySQL client and from there use the command mysql> select version();

Answer 2

I do not see exactly what is happening with the provided code, however, when you build a data access object, you may want to consider building it in such a way that your Connection is a variable of your class, instead of the class's method.

Furthermore, often times by default a mysql database will expect a secure connection. To remove this default, take a look at the url variable.

public class DataAccessExample {

private Connection connection;
private final String driverClass = "com.mysql.jdbc.Driver";
private final String url = "jdbc:mysql://localhost:3306/tmlschedule?autoReconnect=true&useSSL=false";
private final String userName = "YOUR USERNAME";
private final String password = "YOUR PASSWORD";

//Consider calling this openConnection();
public void readDataBase() throws ClassNotFoundException, SQLException {
    Class.forName(driverClass);
    connection = DriverManager.getConnection(url, userName, password);
}

//Then have a close connection, this will make your life easier in the future
public void closeConnection() throws SQLException {
    if (connection != null) {
        connection.close();
    }
}

}

To use this code:

public static void main(String[] args) {
    DataAccessExample d = new DataAccessExample();

    try {
        d.readDataBase();
        d.closeConnection();
    } catch (ClassNotFoundException | SQLException ex) {
        Logger.getLogger(DataAccessExample.class.getName()).log(Level.SEVERE, null, ex);
    }
}

Finally, make sure your database name is correctly spelled and that your username and password are correct.

I hope this helps.

Answer 3

It's very likely that your driver version is too old if your mysql version is 8.x,the driver class com.mysql.jdbc.Driver is deprecated.The new driver class is com.mysql.cj.jdbc.Driver. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary. The following code comes from the official documentation：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

Connection conn = null;
...
try {
    conn =
       DriverManager.getConnection("jdbc:mysql://localhost/test?" +
                                   "user=minty&password=greatsqldb");

    // Do something with the Connection

    ...
} catch (SQLException ex) {
    // handle any errors
    System.out.println("SQLException: " + ex.getMessage());
    System.out.println("SQLState: " + ex.getSQLState());
    System.out.println("VendorError: " + ex.getErrorCode());
}