h2 truncate may fail

[ben-manes]

Between tests I clean-up by deleting the data in the test case's tables. If I use a truncate this may fail due to improperly rendering the schema name. If I change to a delete it works perfectly. Oddly its the second truncate that fails, as the first executes fine. This is with jOOQ 3.0.0 and H2 1.3.172.

  @Inject DSLContext db;

  @AfterMethod
  public void afterMethod() {
    db.truncate(FILE_TAG).execute();
    db.truncate(FILE_METADATA).execute();
    db.truncate(FILE_ASSOCIATION).execute();
  }

org.jooq.exception.DataAccessException: SQL [truncate table file_metadata]; Cannot truncate """file_vault"".FILE_METADATA"; SQL statement:
truncate table file_metadata [90106-167]
    at org.jooq.impl.Utils.translate(Utils.java:1021)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:462)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:287)
    at com.addepar.domain.service.filevault.FileVaultServiceTest.afterMethod(FileVaultServiceTest.java:45)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:84)
    at org.testng.internal.Invoker.invokeConfigurationMethod(Invoker.java:564)
    at org.testng.internal.Invoker.invokeConfigurations(Invoker.java:213)
    at org.testng.internal.Invoker.invokeMethod(Invoker.java:786)
    at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:901)
    at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1231)
    at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:127)
    at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:111)
    at org.testng.TestRunner.privateRun(TestRunner.java:767)
    at org.testng.TestRunner.run(TestRunner.java:617)
    at org.testng.SuiteRunner.runTest(SuiteRunner.java:334)
    at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:329)
    at org.testng.SuiteRunner.privateRun(SuiteRunner.java:291)
    at org.testng.SuiteRunner.run(SuiteRunner.java:240)
    at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
    at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86)
    at org.testng.TestNG.runSuitesSequentially(TestNG.java:1224)
    at org.testng.TestNG.runSuitesLocally(TestNG.java:1149)
    at org.testng.TestNG.run(TestNG.java:1057)
    at org.testng.remote.RemoteTestNG.run(RemoteTestNG.java:111)
    at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:204)
    at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:175)
Caused by: org.h2.jdbc.JdbcSQLException: Cannot truncate """file_vault"".FILE_METADATA"; SQL statement:
truncate table file_metadata [90106-167]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:169)
    at org.h2.message.DbException.get(DbException.java:146)
    at org.h2.command.ddl.TruncateTable.update(TruncateTable.java:35)
    at org.h2.command.CommandContainer.update(CommandContainer.java:75)
    at org.h2.command.Command.executeUpdate(Command.java:230)
    at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:194)
    at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:189)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:337)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:283)
    ... 27 more

[lukaseder]

Is it really because of the rendered SQL? I have observed H2 adding funny quotes to the schema names in error messages before, but the SQL statement referred from the stack trace reads truncate table file_metadata (no rendered schema here).

To get the real rendered SQL statements, could you turn on DEBUG logging for jOOQ? Do your statements work when executed directly via JDBC?

[ben-manes]

jOOQ appears to be doing the right thing. Here's the gist.

Oddly, if I run the sql query directly it fails too, e.g.

db.execute("truncate table file_metadata");

If it helps, here's how I create the jdbc connection,

private static final String URL = "jdbc:h2:mem:test_%1$d;DB_CLOSE_DELAY=-1;"
    + "INIT=CREATE SCHEMA IF NOT EXISTS \"%2$s\"\\;SET SCHEMA \"%2$s\"";

@Provides @Singleton @TargetDataSource
DataSource providesDataSource(DelegatingDataSource delegater) {
  JdbcDataSource dataSource = new JdbcDataSource();
  dataSource.setURL(String.format(URL, counter.incrementAndGet(), config.getString("schema")));
  delegater.setTargetDataSource(dataSource);
  return delegater;
}

[lukaseder]

Do your tables have foreign key references? Then, TRUNCATE usually doesn't work, in most databases. See the H2 manual for details:
http://www.h2database.com/html/grammar.html#truncate_table

I guess you could still use it if you manually deactivated constraint checking before truncating.

[ben-manes]

Thanks! I do have FKs and was deleting in the correct order (since I haven't added ON DELETE CASCADE yet). I wish the error was more descriptive to make up for my naivety. =)

[lukaseder]

Thought so :-)

Maybe, you could drop a line on the H2 user group to request a better error message for that case...