SHUTDOWN DEFRAG is producing a trace that the database is closing

[Brett Ryan]

I'm trying to shutdown defrag a database followed by a backup. My tests are when ran in isolation there is no problem, the problem seems to only occur when ran from a windows scheduled task for some reason.

When ran manually I am building a DB that is 4GB in size, after a defrag this is reduced to < 800MB.

I'm using h2 1.4.191.

[Thomas Mueller]

Hi,

It looks like the store is closed while doing compaction. So it is probably closed twice (maybe concurrently). I'm not sure what could have caused this. I'm afraid we would need a reproducible test case.

What might help is adding ";TRACE_LEVEL_FILE=3" to the database URL (everywhere), so all JDBC method calls are logged.

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Brett Ryan]

That will produce one hell of a file.

What I'm doing is taking a snapshot of a portion of another DB in the UK as a h2 database (well several) then sending that file to Australia to be used as a read only reference DB. The solution is sound it's the program that builds the DB that causes the problem. I've found that compacting (shutdown defrag) will go from 4GB to <800MB.

Just checked and the built DB has 12,771,399 records which will only grow over time. It presently takes 20 minutes to create this file.

Is there a way to turn tracing on at a certain point in time?

The basic approach I take is of the following form where jt is an instance of spring's JdbcTemplate:

        jt.execute((Connection con) -> {

            RunScript.execute(con, new InputStreamReader(AppRunner.class.getResourceAsStream(s)));

            return null;

        });

        syncTables();

        // Would like to turn tracing on here.

        jt.execute("analyze");

        jt.execute("shutdown defrag");

        Backup.execute("local_db.zip", ".", "local_db", false);

You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/TAlNnoWW0kI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

[Thomas Mueller]

Hi,

Yes, you could use (probably just after "analyze"):

        jt.execute("set trace_level_file 3");

So far, I could not reproduce the problem, my test case is:

    DeleteDbFiles.execute("~/temp", "test", false);
    String url = "jdbc:h2:~/temp/test";
    Connection conn = DriverManager.getConnection(url);
    Statement stat = conn.createStatement();
    stat.execute("create table if not exists test(id int, data clob)");
    for (int i = 0; i < 10; i++) {
        stat.execute("insert into test values(1, space(10000))");
        stat.execute("delete from test");
    }
    stat.execute("set trace_level_file 3");
    stat.execute("analyze");
    stat.execute("shutdown defrag");
    Backup.execute("~/temp/test.zip", "~/temp", "test", false);
    conn.close();

Regards,
Thomas

[Brett Ryan]

Ta Thomas. Yeah it's a tricky one. As mentioned it only seems to crop up when run within a windows scheduled task, never happened before that.

I should note I'm actually creating two h2 db's from two different sources, both run in their own Callable in an ExecutionService. The other smaller db is built with 3.2 million records and doesn't exhibit the same symptom.

Interestingly I can watch the other db's tempFile being built which throws the trace when it gets to almost its full size.

I shall introduce the trace to see what's going on.