Java/JDBC/MySQL: How do I troubleshoot why DriverManager.getConnection() is returning NULL?

Question

I am attempting to troubleshoot an issue with a Java application that is utilizing JDBC to connect to a MySQL database. The surface issue is that when connecting to a valid database, DriverManager.getConnection will sometimes return NULL, while just a few minutes later it will return a valid connection to the exact same database.

I am in the position of trying to troubleshoot this issue, but my understanding of where Java, JDBC, and MySQL meet is fairly limited. I've been doing lots of research on this, but have hit a wall and don't know where to go from here.

Here's what I've done so far:

• On the Java end, I have traced the code all the way to DriverManager.getConnection(). I have determined that the NULL connection is coming from there, but I have no clue what goes on under the hood of getConnection. I've been struggling to find a thorough explanation of this online.
• On the MySQL end, I have verified that there are plenty of connections available to me (around 1000 free connections at times), so I know I'm not exceeding max connections there. Viewing logs, I was able to determine that there are a slightly higher number of connections being aborted during the time-frames where I'm having the most issues, but I don't know how to determine why these connections were aborted (did MySQL abort, JDBC, the Java application?) I'm not sure if there is anything else I need to be looking for on the MySQL end.
• In the middle, with JDBC, I'm pretty lost. I have been reading up on MySQL Connector/J at http://dev.mysql.com/doc/refman/5.1/en/connector-j.html, but am not certain if that info pertains to the JDBC driver being utilized by Java.

Any direction as to where I can go from here would be much appreciated.

Thanks!

EDIT - 2/15, 10:35am CST I apologize for not being more specific. This application is a production application that normally works just fine. It's successfully processing tens of thousands of connections a day without any issues, it's just that this issue will crop up at random times during the day, and will persist for anywhere from 30 seconds to 5 minutes when it does happen.

Here's the code that I've traced all the way down to DriverManager.getConnection:

var dbConn = DatabaseConnectionFactory.createDatabaseConnection('com.mysql.jdbc.Driver','jdbc:mysql://'+ serverName +':' + port + '/' + database, userName, password);

public static DatabaseConnection createDatabaseConnection(String driver, String address, String username, String password) throws SQLException {
        try {
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }

        Properties info = new Properties();
        info.setProperty("user", username);
        info.setProperty("password", password);

        // this property should only be set if it's for embedded database
        info.setProperty("shutdown", "true");

        return new DatabaseConnection(address, info);
    }

public DatabaseConnection(String address, Properties info) throws SQLException {
        logger.debug("creating new database connection: address=" + address + ", " + info);
        this.address = address;
        connection = DriverManager.getConnection(address, info);
    }

I don't believe there is actually any issue with the code, but rather an issue somewhere in between getConnection() and MySQL.

Answer 1

An individual driver can return null for a connect request, the JDBC 4.1 specification says this:

When the DriverManager is trying to establish a connection, it calls that driver’s connect method and passes the driver the URL. If the Driver implementation understands the URL, it will return a Connection object or throw a SQLException if a connection cannot be maded to the database. If the Driver implementation does not understand the URL, it will return null.

However, looking at the code of java.sql.DriverManager (in Java 7 Update 13), it will always throw an SQLException with message No suitable driver found for <url> when all available drivers have returned null for a connect(url, properties) call:

//  Worker method called by the public getConnection() methods.
private static Connection getConnection(
    String url, java.util.Properties info, ClassLoader callerCL) throws SQLException {
// Removed some classloading stuff for brevity
    if(url == null) {
        throw new SQLException("The url cannot be null", "08001");
    }
    // Walk through the loaded registeredDrivers attempting to make a connection.
    // Remember the first exception that gets raised so we can reraise it.
    SQLException reason = null;
    for(DriverInfo aDriver : registeredDrivers) {
        // If the caller does not have permission to load the driver then
        // skip it.
        if(isDriverAllowed(aDriver.driver, callerCL)) {
            try {
                println("    trying " + aDriver.driver.getClass().getName());
                Connection con = aDriver.driver.connect(url, info);
                if (con != null) {
                    // Success!
                    println("getConnection returning " + aDriver.driver.getClass().getName());
                    return (con);
                }
            } catch (SQLException ex) {
                if (reason == null) {
                    reason = ex;
                }
            }
        } else {
            println("    skipping: " + aDriver.getClass().getName());
        }
    }
    // if we got here nobody could connect.
    if (reason != null)    {
        println("getConnection failed: " + reason);
        throw reason;
    }
    println("getConnection: no suitable driver found for "+ url);
    throw new SQLException("No suitable driver found for "+ url, "08001");
}

In other words: what you describe cannot happen (at least not in Java 7 Update 13). A quick peek at the Java 5 Update 22 sources shows an almost identical implementation were it simply cannot return null.

More likely you are swallowing an exception and then attempting to use a Connection variable or field that has value null.

Another possibility would be that you are not obtaining the connection with DriverManager.getConnection(url, ...), but with DriverManager.getDriver(url).connect(...) which can return null because of the rules established above. If that is what you do, it probably points to a bug in the Connector/J driver if you are always using the exact same URL: a driver cannot decide at one point to return a connection for a specific URL and next return null. It should always either return a Connection or throw an SQLException for the same URL.

Answer 2

Yes, DriverManager is the class that gets the connection for you.

It manages this using the JDBC driver classes that you get with the MySQL Connector-J JAR. That JAR has to be in your CLASSPATH when you start.

Start by making sure you can connect to MySQL from the machine where you run the Java app. Log into the MySQL admin app successfully and you've gotten past the first hurdle.

I'll offer you a class to doctor for your situation. The methods might be generally useful for you. Modify the connection, credentials, and query for your situation and try it out. I know this code works.

package persistence;

import java.sql.*;
import java.util.*;

/**
 * util.DatabaseUtils
 * User: Michael
 * Date: Aug 17, 2010
 * Time: 7:58:02 PM
 */
public class DatabaseUtils {
/*
    private static final String DEFAULT_DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String DEFAULT_URL = "jdbc:oracle:thin:@host:1521:database";
    private static final String DEFAULT_USERNAME = "username";
    private static final String DEFAULT_PASSWORD = "password";
*/
/*
    private static final String DEFAULT_DRIVER = "org.postgresql.Driver";
    private static final String DEFAULT_URL = "jdbc:postgresql://localhost:5432/party";
    private static final String DEFAULT_USERNAME = "pgsuper";
    private static final String DEFAULT_PASSWORD = "pgsuper";
*/
    private static final String DEFAULT_DRIVER = "com.mysql.jdbc.Driver";
    private static final String DEFAULT_URL = "jdbc:mysql://localhost:3306/party";
    private static final String DEFAULT_USERNAME = "party";
    private static final String DEFAULT_PASSWORD = "party";

    public static void main(String[] args) {
        long begTime = System.currentTimeMillis();

        String driver = ((args.length > 0) ? args[0] : DEFAULT_DRIVER);
        String url = ((args.length > 1) ? args[1] : DEFAULT_URL);
        String username = ((args.length > 2) ? args[2] : DEFAULT_USERNAME);
        String password = ((args.length > 3) ? args[3] : DEFAULT_PASSWORD);

        Connection connection = null;

        try {
            connection = createConnection(driver, url, username, password);
            DatabaseMetaData meta = connection.getMetaData();
            System.out.println(meta.getDatabaseProductName());
            System.out.println(meta.getDatabaseProductVersion());

            String sqlQuery = "SELECT PERSON_ID, FIRST_NAME, LAST_NAME FROM PERSON ORDER BY LAST_NAME";
            System.out.println("before insert: " + query(connection, sqlQuery, Collections.EMPTY_LIST));

            connection.setAutoCommit(false);
            String sqlUpdate = "INSERT INTO PERSON(FIRST_NAME, LAST_NAME) VALUES(?,?)";
            List parameters = Arrays.asList("Foo", "Bar");
            int numRowsUpdated = update(connection, sqlUpdate, parameters);
            connection.commit();

            System.out.println("# rows inserted: " + numRowsUpdated);
            System.out.println("after insert: " + query(connection, sqlQuery, Collections.EMPTY_LIST));
        } catch (Exception e) {
            rollback(connection);
            e.printStackTrace();
        } finally {
            close(connection);
            long endTime = System.currentTimeMillis();
            System.out.println("wall time: " + (endTime - begTime) + " ms");
        }
    }

    public static Connection createConnection(String driver, String url, String username, String password) throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        if ((username == null) || (password == null) || (username.trim().length() == 0) || (password.trim().length() == 0)) {
            return DriverManager.getConnection(url);
        } else {
            return DriverManager.getConnection(url, username, password);
        }
    }

    public static void close(Connection connection) {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public static void close(Statement st) {
        try {
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void rollback(Connection connection) {
        try {
            if (connection != null) {
                connection.rollback();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static List<Map<String, Object>> map(ResultSet rs) throws SQLException {
        List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
        try {
            if (rs != null) {
                ResultSetMetaData meta = rs.getMetaData();
                int numColumns = meta.getColumnCount();
                while (rs.next()) {
                    Map<String, Object> row = new HashMap<String, Object>();
                    for (int i = 1; i <= numColumns; ++i) {
                        String name = meta.getColumnName(i);
                        Object value = rs.getObject(i);
                        row.put(name, value);
                    }
                    results.add(row);
                }
            }
        } finally {
            close(rs);
        }
        return results;
    }

    public static List<Map<String, Object>> query(Connection connection, String sql, List<Object> parameters) throws SQLException {
        List<Map<String, Object>> results = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);

            int i = 0;
            for (Object parameter : parameters) {
                ps.setObject(++i, parameter);
            }
            rs = ps.executeQuery();
            results = map(rs);
        } finally {
            close(rs);
            close(ps);
        }
        return results;
    }

    public static int update(Connection connection, String sql, List<Object> parameters) throws SQLException {
        int numRowsUpdated = 0;
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);

            int i = 0;
            for (Object parameter : parameters) {
                ps.setObject(++i, parameter);
            }
            numRowsUpdated = ps.executeUpdate();
        } finally {
            close(ps);
        }
        return numRowsUpdated;
    }
}

After compiling, run it with this command:

java -classpath .;<Connector-J driver path here> persistence.DatabaseUtils