Weird JDBC executeQuery exception

Question

I have the following code:

 public Object RunQuery(String query) throws Exception{
    System.out.println("Trying to run query");
    Statement stmt = null;
    ResultSet rs = null;
    try {
        stmt = conn.createStatement();
        System.out.println("Got Statement");
        rs = stmt.executeQuery(query);
        System.out.println("Query executed");
        ...


    } catch (SQLException ex) {
        // handle any errors
        System.out.println("SQLException: " + ex.getMessage());
        System.out.println("SQLState: " + ex.getSQLState());
        System.out.println("VendorError: " + ex.getErrorCode());
    }
    catch (Exception ex) {
        System.out.println("Exception: " + ex.getMessage());
    }
    finally {
       if (rs != null) {
            try {
                rs.close();
            } catch (SQLException sqlEx) {
            } // ignore
            rs = null;
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException sqlEx) {
            } // ignore
            stmt = null;
        }
       return ret;
    }
}

Which works perfectly fine when running

query = "SELECT * FROM smalltable"

But fails on

query = "SELECT * FROM bigtable"

which has about 200K records. The debugger elegantly ignores the catch blocks and goes straight into the finally block; NetBeans gave me this stack frame when I added stmt.executeQuery(query) to the watch list:

>Exception occurred in target VM: Communications link failure  Last packet sent to the server was 0 ms ago.  
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure  Last packet sent to the server was 0 ms ago.

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)

at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)

at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3009)

at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2895)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3438)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2477)

at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422)

at RunQuery
Caused by: java.net.SocketException: Software caused connection abort: recv failed

at java.net.SocketInputStream.socketRead0(Native Method)

at java.net.SocketInputStream.read(SocketInputStream.java:129)

at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:157)

at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:188)

at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2452)

at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2962)

... 9 more

How can I do large queries in this framework?

EDIT: I'm using J connector and mysql server 5.1; the connection string is

jdbc:mysql://localhost?user=root&password=password

Yes, I know that select * is a bad practice, but as you can see I just begun and this is more or less the second test I'm doing

Answer 1

If it is skipping directly to the finally, is the "exception" thats being thrown inside the other code actually a descendant of Error? That would avoid your catch entirely.

you might have gotten an OutOfMemoryError, then gotten the network exception because the OOME closed some stuff?

Answer 2

The only thing I can think of that would cause the difference is some kind of network-level timeout, and the Netbeans output would certainly point in this direction to. What is the JDBC connection string you are using; perhaps there's a timeout parameter that can be bumped up?

It might also be worth plugging in a network packet sniffer if the problem persists and see what communications are like at that level. Play "spot the difference" with the short and long queries and you might get a good idea of what's making one fail where the other succeeds.

(In general select * is a bad idea simply because of all the extra data it brings back unnecessarily. In this case, selecting all the columns from all the rows in the big table is obviously giving the connection trouble compared to selecting from the smaller table. I know that in this case your question is still valid as sometimes you may need to do this, and besides selecting a subset of an even larger table would probably do the same thing, but I'm just pointing this out as a general guideline in case you were using this in production as a shortcut.)

Answer 3

My immediate thought is that there could be a memory issue with fetching all that data. I don't know how MySQL is set up for this purpose, but you might want to try setting the fetch size in the ResultSet or limiting the ResultSet size by selecting fewer columns. It's possible that the JDBC driver is trying to fetch the entire table in one operation.

As a side note, please please please never do stmt.executeQuery(query). Always use a PreparedStatement and bind your query parameters. In your example there are no parameters, but this is a practice that's critical for security purposes. Never use SQL with parameters that are 'literals' (the exception is when your literals are trusted strings such as constants from your code or non string types such as numbers which you've already parsed).