MYSQL JDBC java.sql.SQLException: Operation not allowed after ResultSet closed

Question

I have a program that queries a database using different jdbc drivers. This error is specific to the MySQL driver.

Here's the basic rundown.

I have another query runner class that uses a postgresql jdbc driver that works just fine. Note the line conn.close(); this works fine on my postgresql query runner, but for this SQL runner it comes up with the error.

I have removed the line conn.close(); and this code works fine, but over time it accumulates sleeping connections in the database. How can I fix this?

New Relic is a third party application that I am feeding data to, if you dont know what it is, don't worry it's not very relevant to this error.

MAIN CLASS

public class JavaPlugin {
public static void main(String[] args) {
    try {
        Runner runner = new Runner();
        runner.add(new MonitorAgentFactory());
        runner.setupAndRun(); // never returns
    }
    catch (ConfigurationException e) {
        System.err.println("ERROR: " + e.getMessage());
        System.exit(-1);
    }
    catch (Exception e) {
        System.err.println("ERROR: " + e.getMessage());
        System.exit(-1);
        }
    }
}

MYSQL QUERY RUNNER CLASS

import com.newrelic.metrics.publish.util.Logger;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;



public class MySQLQueryRunner {
    private static final Logger logger = Logger.getLogger(MySQLQueryRunner.class);
    private String connectionStr;
    private String username;
    private String password;


    public MySQLQueryRunner(String host, long port, String database, String username, String password) {
        this.connectionStr = "jdbc:mysql://" + host + ":" + port + "/" + database + "?useSSL=false";
        this.username = username;
        this.password = password;
    }


    private void logError(String message) {
        logger.error(new Object[]{message});
    }


    private void logDebugger(String message) {
        logger.debug(new Object[]{message});
    }


    private Connection establishConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");

        } catch (ClassNotFoundException e) {
            logError("MySQL Driver could not be found");
            e.printStackTrace();
            return null;
        }
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(connectionStr, username, password);
            logDebugger("Connection established: " + connectionStr + " using " + username);
        } catch (SQLException e) {
            logError("Connection Failed! Check output console");
            e.printStackTrace();
            return null;
        }
        return connection;
    }



    public ResultSet run(String query) {
        Connection conn = establishConnection();
        if (conn == null) {
            logError("Connection could not be established");
            return null;
        }
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            conn.close();
            return rs;
        } catch (SQLException e) {
            logError("Failed to collect data from database");
            e.printStackTrace();
            return null;
        }

    }
}

AGENT CLASS

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
import com.newrelic.metrics.publish.Agent;

public class LocalAgent extends Agent {
    private MySQLQueryRunner queryRunner;
    private String name;
    private Map<String, Object> thresholds;
    private int intervalDuration;
    private int intervalCount;

    public LocalAgent(String name, String host, long port, String database, String username, String password, Map<String, Object> thresholds, int intervalDuration) {
        super("com.mbt.local", "1.0.0");
        this.name = name;
        this.queryRunner = new MySQLQueryRunner(host, port, database, username, password);
        // this.eventPusher = new NewRelicEvent();
        this.thresholds = thresholds;
        this.intervalDuration = intervalDuration;
        this.intervalCount = 0;
    }

    /**
     * Description of query
     */
    private void eventTestOne() {
        String query = "select count(1) as jerky from information_schema.tables;";
        ResultSet rs = queryRunner.run(query);
        try {
            while (rs.next()) {
                NewRelicEvent event = new NewRelicEvent("localTestOne");
                event.add("jerky", rs.getInt("jerky"));
                event.push();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * blah
     */
    private void eventTestTwo() {
        String query = "SELECT maxlen FROM information_schema.CHARACTER_SETS;";
        ResultSet rs = queryRunner.run(query);
        try {
            while (rs.next()) {
                NewRelicEvent event = new NewRelicEvent("localTestTwo");
                event.add("beef", rs.getString("maxlen"));
                event.push();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    @Override
    public void pollCycle() {
        if (this.intervalCount % this.intervalDuration == 0) {
            eventTestOne();
            eventTestTwo();
        this.intervalCount = 0;
        }
        // Always incrementing intervalCount, keeping track of poll cycles that have passed
        this.intervalCount++;
    }

    @Override
    public String getAgentName() {
        return this.name;
    }
}

Answer 1

The problem is that you are trying to access the ResultSet after the connection is closed.

You should open and close the connection in the method that is calling run() this way the connection will be open when you access and loop through the Resultset and close it in the finally block of the calling method.

Even better would be if you can just loop through the ResultSet in the run() method and add the data to an object and return the object, this way you can close it in the finally block of the run() method.