Hi,
I've search here and a couple of people have reported a bug that
generates temp files that won't go away until you restart the
application. I was suffering from the same issue and there wasn't a
solution.
I've managed to reproduce the bug, and kind of pin point the problem,
and also a work around. I'm not sure if it is h2 or java's GC causing
the issue as you'll see from my workaround.
Typically the temp.db files doesn't get removed and piles up,
sometimes it does get removed. But some of the time, it'll keep piling
up and you'll run out of disk space. I've only noticed it when you do
a SELECT on big tables, > 10,000 rows with atleast 1 column having
varchar. I just tried with all columns as bigint and its okay, only 1
or 2 temp file remains.
Not sure how to attach, so I'll paste the code (its a bit crude)
import java.sql.*;
import java.util.*;
public class h2testbug
{
public static void main(String[] args)
{
Connection localConn;
PreparedStatement ps;
ResultSet rs;
String query;
try
{
Class.forName("org.h2.Driver");
String localURL =
"jdbc:h2:testbug;MODE=PostgreSQL;DATABASE_TO_UPPER=false;AUTOCOMMIT=false;CACHE_SIZE=196608";
Properties localProps = new Properties();
localProps.setProperty( "user", "sa" );
localConn = DriverManager.getConnection( localURL,
localProps );
localConn.setAutoCommit( false );
query = "show tables";
ps = localConn.prepareStatement( query );
rs = ps.executeQuery();
if( !rs.next() )
{
// Empty db, populate with 50k records
query = "CREATE TABLE tt( ttid bigint NOT NULL,
ttid2 bigint,
ttid3 bigint, ttid4 bigint, ttid5 character varying(128) )";
ps = localConn.prepareStatement( query );
ps.execute();
query = "INSERT INTO tt VALUES( ?, ?, ?, ?, ?
)";
ps = localConn.prepareStatement( query );
for( int i = 0; i < 50000; i ++ )
{
ps.setObject( 1, i );
ps.setObject( 2, i );
ps.setObject( 3, i );
ps.setObject( 4, i );
ps.setObject( 5, "Testing testing
testing testing" );
ps.execute();
}
localConn.commit();
}
h2testbug tt = new h2testbug();
while( true )
{
System.out.println( tt.buggy(localConn) );
sleep();
}
}
catch( Exception e )
{
e.printStackTrace();
}
}
public static void sleep()
{
long t0, t1;
t0 = System.currentTimeMillis();
do {
t1 = System.currentTimeMillis();
}
while( t1 - t0 < 500 );
}
public Long buggy( Connection conn ) throws Exception
{
String query = "SELECT * FROM tt ORDER BY ttid DESC LIMIT 1";
PreparedStatement ps = conn.prepareStatement( query );
ResultSet rs = ps.executeQuery();
if( rs.next() )
{
return rs.getLong( "ttid" );
}
else
{
return 0L;
}
}
}
The work around is the close the ResultSet before you return the
function, doing so will clear out any temp files that were created.
public Long buggy( Connection conn ) throws Exception
{
String query = "SELECT * FROM tt ORDER BY ttid DESC LIMIT 1";
PreparedStatement ps = conn.prepareStatement( query );
ResultSet rs = ps.executeQuery();
if( rs.next() )
{
Long id = rs.getLong("ttid");
rs.close();
return id;
}
else
{
rs.close();
return 0L;
}
}
I hope this helps people that were having the same issue.
I'm using version 1.3.160, as of this posting its the latest version.
Java 1.6
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
