Re: Serious OutOfMemory issue while reading with mysql-connector-j-5.0.7 ...

Hi Mark!

Big thanks for your detailed response. I've tested with Connector/J 5.0.3 + 5.0.7 and debugged 5.0.7.

Regarding my debug session. The OutOfMemoryError-Stacktrace showed the execution path up to the readAllResults-method which lead me to com.mysql.jdbc.PreparedStatmenet#executeQuery and #createStreamingResultSet() , which decides, if all results have to be read at once or if a streaming resultset should be used.

    protected boolean createStreamingResultSet() {

        return ((this.resultSetType == java.sql.ResultSet.TYPE_FORWARD_ONLY)
                && (this.resultSetConcurrency == 

    }

So I've set a breakpoint at com.mysql.jdbc.PreparedStatmenet line 1448, which is in the executeQuery() right before createStreamingResultSet() is called. There I could very, that setting useCursorFetch=true did also set useServerPrepStmts=true. My resultSetType was TYPE_FORWARD_ONLY and the concurrency CONCUR_READ_ONLY - but my fetchSize was 0 or 50 - depending on the test-run where I had some with fetchSize set to 50 and some, where I did not set it.

As you suggested, setting fetchSize to Integer.MIN_VALUE fixed the OutOfMemoryException. During my first the debug session I've seen the MIN_VALUE, but I've had a look at the javadoc for setFetchSize which states ...

• @exception SQLException if a database access error occurs, or the
• condition 0 <= <code>rows</code> <= <code>this.getMaxRows()</code>
• is not satisfied.
• @since 1.2

So I would never have tried setting something which should cause a SQLException. I've assumed, that the MIN_VALUE is only used internally. Does anyone have ideas, how this could be made more intuitive without breaking compatibility?

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

Further, as you explained, copying the data to a temp table is really slow and it only works, as long as there is enough disk space for the temp table.

Hm ... up to now I really like working with mysql's myisam tables since they are usually really fast. I almost understand the necessity to copy data to a temp table on the server to provide some kind of isolation for the cursor. Yet I thought that mysql with myisam tables does not provide any transaction handling and therefor no isolation - which was fine for us.

I will tell my customers, that they may need more disk space when they want to import from mysql and that it's as fast, as it can get. What are the chances, that cursors are enhanced on the server?

Best regards,
Paul
> Hi Paul,
>
> The root of the issue is that there is a "clash" with how MySQL's network protocol works, and the JDBC spec.
>
> The JDBC specification says that applications can open as many result sets as they want on a given connection. MySQL only allows one open result set per connection. If you want to issue another query on a given connection while a result set is still open, you have to read all of the rows first. (it's a request/response protocol)
>
> Given that 98% of the applications that use MySQL are OLTP-ish (or they can use the LIMIT clause to window their data), we made the optimization to read in all of the results for a query before returning to the client application with our JDBC driver. For the overwhelming majority of our users this is an optimization, because their result sets are small (a few hundred rows at most), it reduces network traffic (because there are no "acks" or "send me more rows" calls) and it also ends up freeing locks quicker (this is an optimization that _many_ databases use, it's usually called "firehose" mode), and doesn't use complicated, error-prone threadsafe bookeeping to ensure that only one result set is actively being used by a given connection.
>
> For those very few cases where one needs access to millions of rows, I suggest using the MySQL-specific method of "streaming" a result rather than server-side cursors if you can. Server-side cursors in 5.0 (and 5.1) _always_ materialize a temporary table and read from that, which can have I/O overhead. If have "plugins" or database-awareness in your application, and only need to have one result set open per connection, then you can use the "streaming" mode, which you can do by either setting the fetch size to Integer.MIN_VALUE on the statement, and asking for FORWARD_ONLY cursors, or by casting to a com.mysql.jdbc.Statement and calling enableStreamingResults().
>
> I'm a little bewildered at why cursors aren't working in your situation, since that should be mostly transparent to your application, and they're working for others. What _exact_ version of the driver are you using? You said you saw things setup correctly in the debugger, I'm wondering where you had breakpoints? If I were debugging this myself, I'd set one in ServerPreparedStatement.serverExecute(), walk through the execution of the statement, and also look at the RowData instance that the result set holds, it should be a "CursorRowProvider". If you're not getting ServerPreparedStatements from Connection.prepareStatement(), or not getting CursorRowProviders, then I'd say somewhere there's a disconnect between the JDBC configuration parameters your setting for your application and them getting set internally in the driver.
>
> -Mark
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (MingW32)
>
> iD8DBQFG1e9ztvXNTca6JD8RAjjqAJ9Ny6mlTlNosdNjikOIRRMTTPtHsACeMz7k
> pxKNyRqyhaIlRQG7oV4rK0k=
> =GHAp
> -----END PGP SIGNATURE-----
>
>
>

-- 
--
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
_/
_/   BDM Business Data Management GmbH
_/
_/   Firmenbuch: FN 242059w
_/   Gerichtsstand: Landesgericht Wiener Neustadt  
_/   UID: ATU57685758
_/
_/   Grenzgasse 111, Objekt 9/4
_/   2340 Mödling
_/   Tel,Fax: +43 2236 46 393
_/
_/   Ing. Paul Palaszewski, (Hons) B.Sc.
_/   Geschäftsführer
_/   Mobil: +43(650) 37 36 030
_/   E-Mail: paul.palaszewski@bdm-systems.com
_/
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
 


-- 
MySQL Java Mailing List
For list archives: 
http://lists.mysql.com/java
To unsubscribe:    
http://lists.mysql.com/java?unsub=lists@pantek.com