Hi all,

I am reposting this as I still haven't found an answer to this problem...

I am executing a simple query and I am consistently getting a java.lang.OutOfMemoryError as I attempt to close the result sets, or as I attempt to incrementally open new resultsets -

I am completely new to MySql, so I have tried to search the newslists for this error, but have not found an answer. I will try my best to provide complete info, any help is greatly appreciated:

Attached is an annotated version of what I am trying to do, along with snippets of everything that I have tried to do to eliminate this exception: - a couple of notes, - I'm aware of using preparedStatements and how that would help my example, but first I would like to eliminate the OutOfMemoryError - In the example below, I constructed everything so that no more than 100 records would be retrieved at a time; this was my initial step in trying to eliminate the OutOfMemory error. - I have run this with java -Xms312m -Xmx512m and I print out the results of "Runtime.getRuntime().freeMemory() before and after each exception is thrown, and the free memory never drops below ~ 324MB, so I don't think this is the problem. - The table being queried has 50,000 records and takes up approximately 50 MB of disk space. - If I reduce the table size to below ~ 40,000 records, this problem disappears. - I have more than 19 gigs available on the database partition - complete system specs are also attached

- here is the sample code, I have noted below were the error is:

# instantiating the driver:

try { Class.forName("com.mysql.jdbc.Driver").newInstance(); } ... try { Properties props = new Properties(); props.put("maxRows", "100"); Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test?user=augur&password=augur&maxRows=100",props); }

# creating lot's of data

stmt = conn.createStatement(); String createQuery = "CREATE TABLE " + table + " " +"( " + "ID BIGINT" + ",GATEWAY VARCHAR(128)" + ",ELEMENT VARCHAR(128)" + ",SUMMARY VARCHAR(255)" + ",PRIORITY INTEGER " + ",DESCRIPTION VARCHAR(255) " + ")";

stmt.executeUpdate( createQuery); rs.close(); rs = null; stmt.close(); stmt = null;

# I then fill this table with 50,000 records # upon retrieving it, I get repeated "java.lang.OutOfMemory" errors at the position indicated below

int setSize = 50000; int incSize = 10000; int position = 0; int rsSize = 100;

stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setMaxRows(rsSize); stmt.setFetchSize(Integer.MIN_VALUE);

query = "SELECT ID, SUMMARY, PRIORITY, DESCRIPTION FROM " + table + " LIMIT " + position + "," + rsSize; rs = stmt.executeQuery( query);

int count =1; while( rs.next() ) { object = new DataObject(); alert.id = rs.getLong(1); alert.summary = rs.getString(2); alert.priority = rs.getInt(3); description = rs.getString(4);

count++; if(count == rsSize ) { System.out.println("id: " + alert.id); position += count; rs.close(); rs = null; query = "SELECT ID, SUMMARY, PRIORITY, DESCRIPTION FROM " + table + " LIMIT " + position + "," + rsSize; System.out.println("query: " + query); try{ rs = stmt.executeQuery(query); // <------------ "OutOfMemory" Errors are thrown here } catch(java.lang.OutOfMemoryError t ) { System.out.println("OutOfMemoryError: " + t ); System.out.println("freeMemory: " + runtime.freeMemory() ); rs = stmt.executeQuery(query); // <------------ this call works }

System.out.println("id: " + alert.id); System.gc() ; count =1; } }

- some system specs:

# java -version java version "1.4.1" Java(TM) 2 Runtime Environment, Standard Edition (build Blackdown-1.4.1-beta) Java HotSpot(TM) Client VM (build Blackdown-1.4.1-beta, mixed mode)

# mysqladmin version mysqladmin Ver 8.23 Distrib 3.23.56, for redhat-linux-gnu on i386 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license

Server version 3.23.56-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 2 hours 29 min 0 sec

Threads: 1 Questions: 795888 Slow queries: 5520 Opens: 132 Flush tables: 1 Open tables: 1 Queries per second avg: 89.026 erickson@gandalf:/home/mysql$

# uname -ar Linux gandalf 2.4.22-09032003a-smp #1 SMP Wed Sep 3 11:26:08 EDT 2003 i686 athlon i386 GNU/Linux

#df -h /home/mysql Filesystem Size Used Avail Use% Mounted on /dev/hda5 28G 8.3G 19G 31% /home