CODEWITHSUNDEEP
javamysqljdbclimitmax
chollinger
chollinger

Reputation: 1117

JDBC/Resultset error

My mysql-query in Java always stops (i.e. freezes and does not continue) at a certain position, which namely is 543,858; even though the table contains approx. 2,000,000 entries. I've checked this by logging the current result-fetching. It is reproducible and happens every time at the very same position.

"SELECT abc from bcd WHERE DATEDIFF(CURDATE(), timestamp) <= '"+days+"'");

Addition: It definitely is a Java error, I've just tried out this statement in Navicat (50s running time).

The query seems to freeze after the log tells me that it's now adding the result of position 543,858. 

try {
    ...
    ResultSet res = new ResultSet();
    PreparedStatement stmt = new PreparedStatement(); // prepare statmenet etc.
    stmt.setFetchSize(Integer.MIN_VALUE);
    res = stmt.executeQuery();
    ...
    System.out.println(res.getStatement());
    ...
    while (res.next())
        treeSet.add(res.getString("userid"));

} catch (Exception e) {
     e.printStackTrace();
}

Edit: We were able to figure out the problem. This method is fine and the returned result (500,000 instead of 2,000,000) is right as well (looked up in the wrong db to verify the amount); the problem was, that the next method-call that used the result of the one posted above takes literally forever, but had no logging-implemented. So I've been fooled by missing console-logs.

Thanks anyways!

Upvotes: 0

Views: 528

Answers (3)

aviad
aviad

Reputation: 8278

  1. I would try to add to your query " LIMIT 543857" and then " LIMIT 543857" and see what happens.
  2. If the above does not help, use the limit directive combined with order by.

I suspect that there is invalid entry in your table and the way to find it is binary search.

Upvotes: 0

soulcheck
soulcheck

Reputation: 36767

In mysql to use streaming ResultSets you have to specify more parameters, not only fetchSize.

Try:

stmt = conn.createStatement('select ...', java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

and see if that works.

It's documented in the ResultSet section.

Strange that it doesn't throw exception, but this is the only suspect I have. Maybe it starts garbage collection/flushes memory to disk and it takes so much time it doesn't get to throw it.

Upvotes: 1

anubhava
anubhava

Reputation: 785246

I think you might be running out of memory after processing half a million records. Try assigning more memory using command line options -Xmx etc. See here for more info about command line options.

Upvotes: 1

Related Questions