.h2.db Space not freed up when loading multiple SQL scripts overwriting the same table

[GoogleCodeExporter]

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. Create a SQL script that drops a table, creates it again and then insert
a large amount of data into it.
2. Write a simple program that creates a connection to the database and
runs the SQL script several times (only tested using the
RunScript.execute(Connection conn, Reader reader) method, my be a common
problem), without disconnecting from the database.

What is the expected output? What do you see instead?
Even though the table is dropped when the script runs the second time, the
space is never freed up from the .h2.db file.

What version of the product are you using? On what operating system, file
system, and virtual machine?
java version "1.6.0_17"
Java(TM) SE Runtime Environment (build 1.6.0_17-b04)
Java HotSpot(TM) Client VM (build 14.3-b01, mixed mode, sharing)

H2 Version - 1.2.122

Do you know a workaround?
Close and open the connection to the database each time
RunScript.execute(Connection conn, Reader reader) is called.

How important/urgent is the problem for you?
Low priority
In your view, is this a defect or a feature request?
Defect
Please provide any additional information below.

Original issue reported on code.google.com by phewl...@oats.co.uk on 17 Dec 2009 at 11:38

[GoogleCodeExporter]

What is the result with the newest versin, 1.2.125?

Original comment by thomas.t...@gmail.com on 17 Dec 2009 at 12:00

[GoogleCodeExporter]

According to my test case, the database grows up to 4 MB, but then stays this 
size
(with 1.2.125). That's the behavior I expected, therefore I set the bug to 
'works for
me'. My test case is:

package db;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import org.h2.tools.DeleteDbFiles;

public class TestSimple {
    public static void main(String... args) throws Exception {
        Class.forName("org.h2.Driver");
        DeleteDbFiles.execute("data", "test", false);
        Connection conn = DriverManager.getConnection(
                "jdbc:h2:data/test");
        for (int i = 0; i < 100000; i++) {
            Connection conn1 = DriverManager.getConnection(
                    "jdbc:h2:data/test");
            Statement stat1 = conn1.createStatement();
            stat1.execute(
                    "create table test(id int primary key, name varchar)");
            stat1.execute(
                    "drop table test");
            conn1.close();
            if (i % 1000 == 0) {
                System.out.println(i + " " +
                        new File("data/test.h2.db").length());
            }
        }
        conn.close();
    }
}

Original comment by thomas.t...@gmail.com on 21 Dec 2009 at 11:17

• Changed state: WorksForMe