Create table with autoIncrement fail for hsqldb

General Discussion
1

Hi,

Using Liquibase 2.0 rc2 and hsqldb-2.0.0-rc8 I get the following error:

liquibase.exception.MigrationFailedException: Migration failed for change set db/company.xml::company::bob:
    Reason: liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE MYSCHEMA.company (id INT GENERATED BY DEFAULT AS IDENTITY IDENTITY  NOT NULL, version INT, name VARCHAR(50) NOT NULL, CONSTRAINT PK_COMPANY PRIMARY KEY (id)):
          Caused By: Error executing SQL CREATE TABLE MYSCHEMA.company (id INT GENERATED BY DEFAULT AS IDENTITY IDENTITY  NOT NULL, version INT, name VARCHAR(50) NOT NULL, CONSTRAINT PK_COMPANY PRIMARY KEY (id)):
          Caused By: unexpected token: IDENTITY:
          Caused By: unexpected token: IDENTITY
for the following xml:

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
  xmlns=“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”>

   
       
           
               
           
           
           
               
           
         

       
   

It works ok for Derby and H2.

regards,
Liquiuser

2

It words for me under hsql 1.8.  It may be a bug or SQL change in hsqldb 2.0.  Do you know if they purposely changed the SQL and that is how it will be in 2.0 final? Or if it it something that will change before then?  Do you know what the SQL should be for hsql 2.0?

Nathan

3

Hi Nathan,

Note the double words “IDENTITY IDENTITY” in the sql statement above.

If I remove one of them the sql goes ok in a console.

thanks,
Liquiuser

4

Hi Nathan,

I tried with the hsqldb 1.8 version and it works ok for me too.
I tried with the very latest hsqldb rc 2.0 from: http://hsqldb.org/support/ and I get the same error message.

regards,
Liquiuser

5

I took the create table “generated by identity as identity identity” sql from http://www.hsqldb.org/doc/guide/ch09.html#create_table-section which seems to suggest having identity twice.  Searching for that string on google, however, only brings up the liquibase code which makes me think that we don’t need that second identity. 

The hsql tests still seem to run correctly against 1.8, so I’ll remove the second identity keyword. 

Nathan

6

Hi Nathan,

Can I get a build in which the double identity is removed for hsqldb 2.0?

The bamboo artifact via de website points to the same liquibase 2.0 RC which I have already?
And I cannot find a source download anywhere to build my own?

thanks,
Liquiuser

7

Hi Nathan,

After some svn download problems which caused DatabaseException to be missing, I ran “mvn -Dmaven.test.skip=true install” but I also had to comment all the dependencies to “test-jar” in all the pom’s.

But it works and also with hsqldb 2.0.

thanks,
Liquiuser

8

The build has been failing lately due to failing tests. They pass on my machine, so I still need to figure out why.

You can download the source via svn checkout http://liquibase.jira.com/svn/CORE/trunk

I’ll hopefully get the build working in the next day or two.

Nathan

9

Hello, I’ve just downloaded liquibase-2.0.1 and get the same error. The XML for my changeLogFile is:


       

           

               

           

…

       

   


But when I try to execute the update on H2 database, I see this SQL sentence:


CREATE

    TABLE ANN_ANNOUNCEMENT_CAS

    (

        id INT identity GENERATED BY DEFAULT AS IDENTITY IDENTITY NOT NULL,

…

    )



IDENTITY keyword is twice in the sentence…