Play Framework: Error getting sequence nextval using H2 in-memory database

Question

As the title suggests, I get an error running Play 2.0.1 Tests using a FakeApplication w/ H2 in memory.

I set up a basic unit test:

public class ModelTest {
    @Test
    public void checkThatIndustriesExist() {
        running(fakeApplication(inMemoryDatabase()), new Runnable() {
            public void run() {
                Industry industry = new Industry();
                industry.name = "Some name";
                industry.shortname = "some-name";
                industry.save();
                assertThat(Industry.find.all()).hasSize(1);
            }
        });
    }

Which yields the following exception:

[info] test.ModelTest
[error] Test test.ModelTest.checkThatIndustriesExist failed: Error getting sequence nextval
[error]     at com.avaje.ebean.config.dbplatform.SequenceIdGenerator.getMoreIds(SequenceIdGenerator.java:213)
[error]     at com.avaje.ebean.config.dbplatform.SequenceIdGenerator.loadMoreIds(SequenceIdGenerator.java:163)
[error]     at com.avaje.ebean.config.dbplatform.SequenceIdGenerator.nextId(SequenceIdGenerator.java:118)
[error]     at com.avaje.ebeaninternal.server.deploy.BeanDescriptor.nextId(BeanDescriptor.java:1218)
[error]     at com.avaje.ebeaninternal.server.persist.DefaultPersister.setIdGenValue(DefaultPersister.java:1304)
[error]     at com.avaje.ebeaninternal.server.persist.DefaultPersister.insert(DefaultPersister.java:403)
[error]     at com.avaje.ebeaninternal.server.persist.DefaultPersister.saveEnhanced(DefaultPersister.java:345)
[error]     at com.avaje.ebeaninternal.server.persist.DefaultPersister.saveRecurse(DefaultPersister.java:315)
[error]     at com.avaje.ebeaninternal.server.persist.DefaultPersister.save(DefaultPersister.java:282)
[error]     at com.avaje.ebeaninternal.server.core.DefaultServer.save(DefaultServer.java:1577)
[error]     at com.avaje.ebeaninternal.server.core.DefaultServer.save(DefaultServer.java:1567)
[error]     at com.avaje.ebean.Ebean.save(Ebean.java:538)
[error]     at play.db.ebean.Model.save(Model.java:76)
[error]     at test.ModelTest$1.run(ModelTest.java:24)
[error]     at play.test.Helpers.running(Helpers.java:277)
[error]     at test.ModelTest.checkThatIndustriesExist(ModelTest.java:21)
[error]     ...
[error] Caused by: org.h2.jdbc.JdbcSQLException: Syntax Fehler in SQL Befehl "SELECT INDUSTRY_SEQ.NEXTVAL UNION[*] SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL "; erwartet "identifier"
[error] Syntax error in SQL statement "SELECT INDUSTRY_SEQ.NEXTVAL UNION[*] SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL "; expected "identifier"; SQL statement:
[error] select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval [42001-158]
[error]     at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
[error]     at org.h2.message.DbException.get(DbException.java:169)
[error]     at org.h2.message.DbException.getSyntaxError(DbException.java:194)
[error]     at org.h2.command.Parser.readColumnIdentifier(Parser.java:2777)
[error]     at org.h2.command.Parser.readTermObjectDot(Parser.java:2336)
[error]     at org.h2.command.Parser.readTerm(Parser.java:2453)
[error]     at org.h2.command.Parser.readFactor(Parser.java:2035)
[error]     at org.h2.command.Parser.readSum(Parser.java:2022)
[error]     at org.h2.command.Parser.readConcat(Parser.java:1995)
[error]     at org.h2.command.Parser.readCondition(Parser.java:1860)
[error]     at org.h2.command.Parser.readAnd(Parser.java:1841)
[error]     at org.h2.command.Parser.readExpression(Parser.java:1833)
[error]     at org.h2.command.Parser.parseSelectSimpleSelectPart(Parser.java:1746)
[error]     at org.h2.command.Parser.parseSelectSimple(Parser.java:1778)
[error]     at org.h2.command.Parser.parseSelectSub(Parser.java:1673)
[error]     at org.h2.command.Parser.parseSelectUnion(Parser.java:1518)
[error]     at org.h2.command.Parser.parseSelect(Parser.java:1506)
[error]     at org.h2.command.Parser.parsePrepared(Parser.java:405)
[error]     at org.h2.command.Parser.parse(Parser.java:279)
[error]     at org.h2.command.Parser.parse(Parser.java:251)
[error]     at org.h2.command.Parser.prepareCommand(Parser.java:217)
[error]     at org.h2.engine.Session.prepareLocal(Session.java:415)
[error]     at org.h2.engine.Session.prepareCommand(Session.java:364)
[error]     at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1119)
[error]     at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:71)
[error]     at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:267)
[error]     at com.jolbox.bonecp.ConnectionHandle.prepareStatement(ConnectionHandle.java:820)
[error]     at com.avaje.ebean.config.dbplatform.SequenceIdGenerator.getMoreIds(SequenceIdGenerator.java:193)
[error]     ... 80 more

My model looks like this:

@Entity
@Table(name = "industry")
public class Industry extends Model {
    @Id public Long id;
    public String name;
    public String shortname;

    // called in the view to trigger lazy-loading
    public String getName() {
        return name;
    }

    public static Finder<Long, Industry> find = new Finder<Long, Industry>(Long.class, Industry.class);
}

... and finally the relevant part from my initial evolution:

create table industry (
    id        bigint not null,
    name      varchar(255),
    shortname varchar(255),
    constraint pk_industry primary key (id)
}

create sequence industry_seq start with 1000;

Everything works fine running on my PostgreSQL DB, and from my point of view the code is not any different from the Play2.0 Computer Database Sample.

I am happy for any help - thanks!

Regards, Alex

Cuthbert

Answer 1

Solved the problem, here's how:

• before calling inMemoryDatabase() I provided my own config
• the name of the database was 'test' and not 'default' as is used by calling inMemoryDatabase()
• since I did not keep evolutions in conf/evolutions/test but only in conf/evolutions/default the schema hasn't been created
• I had to clean the app, as the 'default' config wasn't applied after the change to inMemoryDatabase()

Cuthbert

I will; need to wait another 23 hours until I'm allowed to do so. – Cuthbert

Answer 2

Just using your code and a new Play 2.0.1 project I could not reproduce. Here is my code and config with imports. Since the classes are not the problem my guess would be that you switched off evolutions with evolutionplugin=disabled and your database evolution does never get applied, hence the "identifier expected" in your exception.

Edit disabling database evolutions, as stated above, yields the same exception that you encountered, see at the bottom.

Edit-2 the author of this blogpost describes how to apply the evolutions by hand. Doesn't look too sexy, but I you should only need to implement that base-class once per project. If someone knows something more elegant within the framework, do tell.

The Ebean Evolution (automatically generated):

create table industry (   
  id              bigint not null,  
  name            varchar(255),   shortname                
  varchar(255),   constraint pk_industry primary key (id)) ;
  create sequence industry_seq;
;

My models.Industry

package models;

import play.db.ebean.Model;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "industry")
public class Industry extends Model {
    @Id
    public Long id;
    public String name;
    public String shortname;

    // called in the view to trigger lazy-loading
    public String getName() {
        return name;
    }

    public static Finder<Long, Industry> find 
           = new Model.Finder<Long, Industry>(Long.class, Industry.class);
}

My Unit-Test

package models;

import org.junit.Test;

import java.util.List;
import java.util.Map;

import static org.fest.assertions.Assertions.assertThat;
import static play.test.Helpers.fakeApplication;
import static play.test.Helpers.inMemoryDatabase;
import static play.test.Helpers.running;

public class ModelTest {

    @Test
    public void checkThatIndustriesExist() {
        running(fakeApplication(inMemoryDatabase()), new Runnable() {
            public void run() {
                Industry industry = new Industry();
                industry.name = "Some name";
                industry.shortname = "some-name";
                industry.save();
                assertThat(Industry.find.all()).hasSize(1);
            }
        });
    }

}

My appication.conf

application.secret="8aXG0?h`kxccxs6JM?WdB@<v`kwouvQr2<y5Y>9jk3XU1yHV`Yr>18xRKHv8PTdv"
application.langs="en"
ebean.default="models.*"
logger.root=ERROR
logger.play=INFO
logger.application=DEBUG

Test output:

[stackoverflow-10869508] $ test
[info] Updating {file:/Users/martin/workspace/stackoverflow-10869508/}stackoverflow-10869508...
[info] Done updating.                                                                  
[info] Compiling 4 Scala sources and 3 Java sources to /Users/martin/workspace/stackoverflow-10869508/target/scala-2.9.1/classes...
[info] Compiling 1 Java source to /Users/martin/workspace/stackoverflow-10869508/target/scala-2.9.1/test-classes...
[info] models.ModelTest
[info] + models.ModelTest.checkThatIndustriesExist
[info] 
[info] 
[info] Total for test models.ModelTest
[info] Finished in 2.289 seconds
[info] 1 tests, 0 failures, 0 errors
[info] Passed: : Total 1, Failed 0, Errors 0, Passed 1, Skipped 0

Test output disabling evolutions:

[stackoverflow-10869508] $ test
[info] Compiling 1 Java source to /Users/martin/workspace/stackoverflow-10869508/target/scala-2.9.1/test-classes...
[info] models.ModelTest
[error] Test models.ModelTest.checkThatIndustriesExist failed: Error getting sequence nextval
[error]     at com.avaje.ebean.config.dbplatform.SequenceIdGenerator.getMoreIds(SequenceIdGenerator.java:213)
[error]     at com.avaje.ebean.config.dbplatform.SequenceIdGenerator.loadMoreIds(SequenceIdGenerator.java:163)
[error]     at com.avaje.ebean.config.dbplatform.SequenceIdGenerator.nextId(SequenceIdGenerator.java:118)
[error]     at com.avaje.ebeaninternal.server.deploy.BeanDescriptor.nextId(BeanDescriptor.java:1218)
[error]     at com.avaje.ebeaninternal.server.persist.DefaultPersister.setIdGenValue(DefaultPersister.java:1304)
[error]     at com.avaje.ebeaninternal.server.persist.DefaultPersister.insert(DefaultPersister.java:403)
[error]     at com.avaje.ebeaninternal.server.persist.DefaultPersister.saveEnhanced(DefaultPersister.java:345)
[error]     at com.avaje.ebeaninternal.server.persist.DefaultPersister.saveRecurse(DefaultPersister.java:315)
[error]     at com.avaje.ebeaninternal.server.persist.DefaultPersister.save(DefaultPersister.java:282)
[error]     at com.avaje.ebeaninternal.server.core.DefaultServer.save(DefaultServer.java:1577)
[error]     at com.avaje.ebeaninternal.server.core.DefaultServer.save(DefaultServer.java:1567)
[error]     at com.avaje.ebean.Ebean.save(Ebean.java:538)
[error]     at play.db.ebean.Model.save(Model.java:76)
[error]     at models.ModelTest$1.run(ModelTest.java:22)
[error]     at play.test.Helpers.running(Helpers.java:277)
[error]     at models.ModelTest.checkThatIndustriesExist(ModelTest.java:17)
[error]     ...
[error] Caused by: org.h2.jdbc.JdbcSQLException: Syntax Fehler in SQL Befehl "SELECT INDUSTRY_SEQ.NEXTVAL UNION[*] SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL "; erwartet "identifier"
[error] Syntax error in SQL statement "SELECT INDUSTRY_SEQ.NEXTVAL UNION[*] SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL UNION SELECT INDUSTRY_SEQ.NEXTVAL "; expected "identifier"; SQL statement:
[error] select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval union select industry_seq.nextval [42001-158]
[error]     at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
[error]     at org.h2.message.DbException.get(DbException.java:169)
[error]     at org.h2.message.DbException.getSyntaxError(DbException.java:194)
[error]     at org.h2.command.Parser.readColumnIdentifier(Parser.java:2777)
[error]     at org.h2.command.Parser.readTermObjectDot(Parser.java:2336)
[error]     at org.h2.command.Parser.readTerm(Parser.java:2453)
[error]     at org.h2.command.Parser.readFactor(Parser.java:2035)
[error]     at org.h2.command.Parser.readSum(Parser.java:2022)
[error]     at org.h2.command.Parser.readConcat(Parser.java:1995)
[error]     at org.h2.command.Parser.readCondition(Parser.java:1860)
[error]     at org.h2.command.Parser.readAnd(Parser.java:1841)
[error]     at org.h2.command.Parser.readExpression(Parser.java:1833)
[error]     at org.h2.command.Parser.parseSelectSimpleSelectPart(Parser.java:1746)
[error]     at org.h2.command.Parser.parseSelectSimple(Parser.java:1778)
[error]     at org.h2.command.Parser.parseSelectSub(Parser.java:1673)
[error]     at org.h2.command.Parser.parseSelectUnion(Parser.java:1518)
[error]     at org.h2.command.Parser.parseSelect(Parser.java:1506)
[error]     at org.h2.command.Parser.parsePrepared(Parser.java:405)
[error]     at org.h2.command.Parser.parse(Parser.java:279)
[error]     at org.h2.command.Parser.parse(Parser.java:251)
[error]     at org.h2.command.Parser.prepareCommand(Parser.java:217)
[error]     at org.h2.engine.Session.prepareLocal(Session.java:415)
[error]     at org.h2.engine.Session.prepareCommand(Session.java:364)
[error]     at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1119)
[error]     at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:71)
[error]     at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:267)
[error]     at com.jolbox.bonecp.ConnectionHandle.prepareStatement(ConnectionHandle.java:820)
[error]     at com.avaje.ebean.config.dbplatform.SequenceIdGenerator.getMoreIds(SequenceIdGenerator.java:193)
[error]     ... 80 more
[info] x models.ModelTest.checkThatIndustriesExist
[info] 
[info] 
[info] Total for test models.ModelTest
[info] Finished in 2.028 seconds
[info] 1 tests, 1 failures, 0 errors
[error] Failed: : Total 1, Failed 1, Errors 0, Passed 0, Skipped 0
[error] Failed tests:
[error]     models.ModelTest
[error] {file:/Users/martin/workspace/stackoverflow-10869508/}stackoverflow-10869508/test:test: Tests unsuccessful
[error] Total time: 3 s, completed 03.06.2012 18:34:39

Valvate

Thanks for testing code! Indeed I figured out that the evolutions haven't been applied (see my answer). – Cuthbert

Answer 3

My ids look like this, and I don't get that error...

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
public Long id;

Notarial

This fixed it for me, the key is to add the @GeneratedValue annotation – Mysia