The problem is not the memory on the database server, but on the client running the JDBC connection. Seems that when you retrieve the result it is trying to store everything in memory at once.
Here is some info from the mysql docs that looks like it pertains, see http://dev.mysql.com/doc/connector/j/en/index.html for more. "By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and can not allocate heap space in your JVM for the memory required, you can tell the driver to 'stream' the results back one row at-a-time. To enable this functionality, you need to create a Statement instance in the following manner: stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to "stream" result sets row-by-row. After this any result sets created with the statement will be retrieved row-by-row. There are some caveats with this approach. You will have to read all of the rows in the result set (or close it) before you can issue any other queries on the connection, or an exception will be thrown. Also, any tables referenced by the query that created the streaming result will be locked until all of the results have been read or the connection closed." John McCaskey On Wed, 2004-10-20 at 14:20 -0500, [EMAIL PROTECTED] wrote: > MySQL 5.0 Alpha > Jdbc Driver: Connector J > OS: Windows 2000 Professional > > Table Size 1 Mil. Records. > > Table Structure: > Table1 > logid - autoincrement int(11) > Timestamp - timestamp > Timestamp2 - timestamp > Tag - varchar(15) > > > Query/Code: > ----------- > PreparedStatement pst = con.prepareStatement("Select > logid,timestamp,Timestamp2,Tag from Table1"); > Resultset rs = pst.executeQuery(); //getting > java.lang.outOfMemoryError here, even before retrieving the resultset > below. > While (rs.next()) > { > //check something, do something...... > } > > ... Tried the same thing above doing it 3 times, with limits of 400k at > a time. The first loop goes through fine. > 2nd loop which has a limit of 360k-760k again gives out of memory > exception. > > > What shall I do? > I have to iterate through all the Mil. Records.. > (This is a once-a-week operation). > > I have increased Max_Allowed_Packet to 200M , and it still gives the > same errors, so I know that's not the problem here. > > Please advice. > -- John A. McCaskey Software Development Engineer Klir Technologies, Inc. [EMAIL PROTECTED] 206.902.2027 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
