H2, diffDatabaseToChangeLog and Hibernate throws JDBCException

General Discussion
1

Environment :

sun-jdk-1.5.0.22
liquibase 1.9.5
h2 1.2.132

Problem:

I get the following error if I want to create the new change sets based on hiberante.cfg.xml as described at http://www.liquibase.org/manual/hibernate via Ant task:

liquibase.exception.JDBCException: org.h2.jdbc.JdbcSQLException: Table "ACCOUNT" not found; SQL statement:
SELECT `id` FROM account WHERE 1 = 0 [42102-132]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:316)
	at org.h2.message.DbException.get(DbException.java:167)
	at org.h2.message.DbException.get(DbException.java:144)
	at org.h2.command.Parser.readTableOrView(Parser.java:4347)
	at org.h2.command.Parser.readTableFilter(Parser.java:1001)
	at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1529)
	at org.h2.command.Parser.parseSelectSimple(Parser.java:1626)
	at org.h2.command.Parser.parseSelectSub(Parser.java:1523)
	at org.h2.command.Parser.parseSelectUnion(Parser.java:1368)
	at org.h2.command.Parser.parseSelect(Parser.java:1356)
	at org.h2.command.Parser.parsePrepared(Parser.java:395)
	at org.h2.command.Parser.parse(Parser.java:278)
	at org.h2.command.Parser.parse(Parser.java:250)
	at org.h2.command.Parser.prepareCommand(Parser.java:222)
	at org.h2.engine.Session.prepareLocal(Session.java:420)
	at org.h2.server.TcpServerThread.process(TcpServerThread.java:224)
	at org.h2.server.TcpServerThread.run(TcpServerThread.java:135)
	at java.lang.Thread.run(Thread.java:595)

	at liquibase.ant.DiffDatabaseTask.execute(DiffDatabaseTask.java:97)
	at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)

If I use the same statement in the webconsole of h2 I get an error too.

SELECT * FROM account;

Table “ACCOUNT” not found; SQL statement:
SELECT * FROM account [42102-132] 42S02/42102

But If I use quotes around the name of the table it works:

SELECT * FROM “account”;

Is this a bug? Any workarounds known?

Regards
Oliver

2

Is it an issue with table capitalization?  I don’t remember if h2 is case sensitive or not.  If you do a table list, does account show up?  Is it the only table that is having troubles?

Nathan

3

No, I don’t think that the table capitalization is the problem. The problem are the quotes:

SELECT * FROM “account”;

If the quotes are missing: the exception will be thrown.

The database contains the table “account”.  It is the first table in alphabetical order. I don’t know if there are more problems. The process stops after this table.

Oliver

4

I checked out the trunk. Is the hibernate integration removed from the 2.x branch?

Oliver

5

I checked out the 1.9.5 version and changed two classes a little bit:


    Index: src/java/liquibase/database/H2Database.java

— src/java/liquibase/database/H2Database.java (revision 1374)
+++ src/java/liquibase/database/H2Database.java (working copy)
@@ -40,7 +40,7 @@

    @Override

    public String getObjectEscapeCharacter() {

-        return “`”;

+        return “”";

    }

    //    public void dropDatabaseObjects(String schema) throws JDBCException {

@@ -111,7 +111,7 @@
    public DataType getDateTimeType() {

        return DATETIME_TYPE;

    }

-   

    @Override

    public boolean isLocalDatabase() throws JDBCException {

    String url = getConnectionURL();

Index: src/java/liquibase/database/HsqlDatabase.java

— src/java/liquibase/database/HsqlDatabase.java (revision 1374)
+++ src/java/liquibase/database/HsqlDatabase.java (working copy)
@@ -176,9 +176,9 @@
    @Override

    public String escapeTableName(String schemaName, String tableName) {

        if (StringUtils.trimToNull(schemaName) == null || !supportsSchemas()) {

-            return escapeObjectName(tableName);

+            return “”" + tableName +""";

        } else {

-            return escapeObjectName(schemaName+"."+escapeObjectName(tableName));

+            return escapeObjectName(schemaName+"."" + tableName +""");

        }

    }

… and it works now.

But I only tested the DiffDatabaseToChangeLogTask-Class in my environment. Is it possible that H2 changed so much or is this a bug in LiquiBase?

Oliver

6

I did move the hiberate tool from the core codebase to an extension.  The code for the hibernate support is now at http://liquibase.jira.com/source/browse/CONTRIB/trunk/hibernate.  I need to do more testing on importing the extension still, then will document how to use it.

I’m wondering if it is a problem with the hibernate diff tool using the escape function twice.  If I create a table in the changelog file regularly, I can select out of it without quotes.  What does your generated changelog create table tag look like?

Nathan

7

This is a part of the changelog.xml:

    <?xml version="1.0" encoding="UTF-8"?> http://www.liquibase.org/xml/ns/dbchangelog/1.9" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9 http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">                                     ...
8

What does the account createTable tag look like in your generated changelog?

I upgraded h2 that the integration tests use, and it runs fine using " as the escape char.  I’d like to not remove the escape char because that way you don’t have to worry as much about reserved words. 

Is account a reserved word in h2?  Do you have a similar problem with other tables?

Nathan

9

just a short note what we found. The initial creation of the tables was done with hibernate/teneo. And Teneo used the double quotes to create the tables.

CREATE TABLE ABC ();

vs.

CREATE TABLE “abc” ();

It looks as if we now must use the quotation marks always. When we find a solution, I rise again.

Oliver

10

Glad you found the problem. 

Nathan