Possible bug with H2 and auto_increment when creating schema

[Joe Zulli]

Hi everyone,

I'm running into an issue using Squeryl to auto-generate the schema in an H2 database that I'm using for unit testing. It seems to be generating create table statements that H2 doesn't like. I have a schema that looks like this:

object SampleSchema extends Schema {

  val categoryTable = table[Category]("category")

  on(categoryTable) { t =>

    declare {

      t.id is (autoIncremented)

    }

  } 

}

And a category case class that looks like this:

case class Category(id: Long, name: String) extends KeyedEntity[Long]

When I connect to an H2 database and create the schema with this command:

SampleSchema.create

It fails saying that there is an error in the SQL syntax. The SQL that Squeryl generates is:

create table category (

    name varchar(128) not null,

    id bigint primary key not null auto_increment

  );

.... and it fails with this error:

[error] org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "CREATE TABLE CATEGORY (

[error]     NAME VARCHAR(128) NOT NULL,

[error]     ID BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT[*]

[error]   ) "; expected "CHECK, REFERENCES, ,, )"; SQL statement:

I tried testing the generated CREATE TABLE statement out myself in my DBVisualizer pointing to an embedded H2 DB and it also fails. I find that I can make it work, by simply switching the order of "not null" and auto_increment so that the "not null" part is last. I guess H2 is extremely picky! I'm guessing this is a bug in Squeryl, but perhaps there is some config setting in h2 that I am not aware of? Any help would be appreciated.

Joe 

[David Whittaker]

Hi Joe,

That does seem like a problem.  Squeryl 0.9.5?  You might want to try removing the `t.id is (autoIncremented)` for now.  Using a Long field as a primary key should make it auto-incremented by default.  I have a feeling that the explicit declaration changes the order that the column attributes are output.

[Joe Zulli]

Hi David, 

Thanks for the response. You are right, the "t.id is (autoIncremented)" was unnecessary. Unfortunately, removing it did not change the order of the fields. I am using Squeryl 0.9.5. Should I try with the latest 0.9.6 build? 

Joe

[David Whittaker]

Hi Joe.

On Mon, Nov 12, 2012 at 3:52 PM, Joe Zulli <j...@savings.com> wrote:

Hi David, 

Thanks for the response. You are right, the "t.id is (autoIncremented)" was unnecessary. Unfortunately, removing it did not change the order of the fields. I am using Squeryl 0.9.5. Should I try with the latest 0.9.6 build? 

Huh, that's odd.  The whole test suite is run on H2, so if there is some issue with attribute ordering, I'm surprised it hasn't shown up before.  Would you mind posting a reproducible test case to Github?

[Joe Zulli]

Strange. Perhaps, I am using some different version of H2. It seems to work fine If I put "primary key" and "auto_increment" field adjacent to eachother. I also verified that it does not break MySQL but did not test other DBs. 

I will work on the test case. Thanks for the help.

Joe

[Joe Zulli]

Update: Upon digging deeper, it looks like Squeryl is perfectly fine. The issue was that my Specs2 test was not properly setting the adapter to the H2Adapter. After, I fixed that everything is good. 

Thanks for the help!

Joe 

[David Whittaker]

No problem.  Glad you've got it figured out.

[subbub...@gmail.com]

Hi,

I have the same problem as below. I need to create 10 digit increment number. Can you please help me.

Thanking you.