Unable to create or alter sequence "xxx_SEQ" because of invalid attributes (start value "0", min value "1", max value "9223372036854775807", increment "1"); SQL statement: CREATE SEQUENCE PUBLIC.xxx_SEQ START WITH 0 [90009-175]

[GoogleCodeExporter]

Please send a question to the H2 Google Group or StackOverflow first,
and only then, once you are completely sure it is an issue, submit it here.
The reason is that only very few people actively monitor the issue tracker.

Before submitting a bug, please also check the FAQ:
http://www.h2database.com/html/faq.html

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. The attached DB seems to have a sequence that was set to -1; the DB loads up 
fine when the app is using version 174, but breaks when using version 175.
2. The function attached in the bottom of this e-mail causes the issue to 
occur; the DB user name is sa and the password blank.  The default url is set 
to the following: "jdbc:h2:~/pontusvision;MVCC=TRUE"

What is the expected output? What do you see instead?

The expected output is for the DB to just open instead, I see the following:
[main] INFO org.springframework.jdbc.datasource.DriverManagerDataSource - 
Loaded JDBC driver: org.h2.Driver
org.h2.jdbc.JdbcSQLException: Unable to create or alter sequence 
"SCHEDULE_TABLE_SEQ" because of invalid attributes (start value "0", min value 
"1", max value "9223372036854775807", increment "1"); SQL statement:
CREATE SEQUENCE PUBLIC.SCHEDULE_TABLE_SEQ START WITH 0 [90009-175]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
    at org.h2.message.DbException.get(DbException.java:172)
    at org.h2.schema.Sequence.<init>(Sequence.java:77)
    at org.h2.command.ddl.CreateSequence.update(CreateSequence.java:66)
    at org.h2.engine.MetaRecord.execute(MetaRecord.java:58)
    at org.h2.engine.Database.open(Database.java:686)
    at org.h2.engine.Database.openDatabase(Database.java:244)
    at org.h2.engine.Database.<init>(Database.java:239)
    at org.h2.engine.Engine.openSession(Engine.java:56)
    at org.h2.engine.Engine.openSession(Engine.java:160)
    at org.h2.engine.Engine.createSessionAndValidate(Engine.java:139)
    at org.h2.engine.Engine.createSession(Engine.java:122)
    at org.h2.engine.Engine.createSession(Engine.java:28)
    at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:323)
    at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:105)
    at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:90)
    at org.h2.Driver.connect(Driver.java:73)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:187)
    at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:173)
    at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:164)
    at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:149)
    at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:119)
    at com.pontusnetworks.utils.MigrateDb.getLocalReferenceDb(MigrateDb.java:112)

What version of the product are you using? On what operating system, file
system, and virtual machine?
175
OS is Windows 7

Do you know a workaround?
Drop to 174; unfortunately, I can't issue a SQL statement to fix the min value 
of the seq.

What is your use case, meaning why do you need this feature?
This was working previously, and suddenly broke.  It's preventing me from 
migrating to version 175

How important/urgent is the problem for you?
Very important, as if it stays broken, I'll be stuck in this version of h2 
forever.  I also have a lot of legacy customers, making it impossible to simply 
fix the DB.

Please provide any additional information below.

Here's the body of the method that's breaking:

import liquibase.Liquibase;
import liquibase.database.Database;
import liquibase.database.core.H2Database;
import liquibase.database.jvm.JdbcConnection;
import liquibase.diff.DiffResult;
import liquibase.diff.compare.CompareControl;
import liquibase.diff.output.DiffOutputControl;
import liquibase.diff.output.changelog.DiffToChangeLog;
import liquibase.exception.DatabaseException;
import liquibase.exception.LiquibaseException;
import liquibase.resource.FileSystemResourceAccessor;
import liquibase.resource.ResourceAccessor;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

  public static Database getLocalReferenceDb() throws SQLException, DatabaseException
  {
    DriverManagerDataSource referenceDataSource = new DriverManagerDataSource();
    referenceDataSource.setUrl(referenceDbUrl);
    referenceDataSource.setUsername(referenceDbUsr);
    referenceDataSource.setPassword(referenceDbPwd);
    referenceDataSource.setDriverClassName(referenceDbCls);
    Connection referenceDbConnection = referenceDataSource.getConnection();
    JdbcConnection referenceDbJdbcConn = new JdbcConnection(referenceDbConnection);
    Database referenceDatabase = new H2Database();
    referenceDatabase.setConnection(referenceDbJdbcConn);
    referenceDatabase.setAutoCommit(true);

    return referenceDatabase;

  }

Original issue reported on code.google.com by odranoe...@gmail.com on 10 Feb 2014 at 2:34

Attachments:

• pontusvision.h2.db.zip

[GoogleCodeExporter]

This bug has been fixed in SVN.

Original comment by noelgrandin on 11 Feb 2014 at 7:09

• Changed state: Fixed

[GoogleCodeExporter]

Hi, This is still an issue for me in the latest release 1.3.176.  It's still 
broken:

org.h2.jdbc.JdbcSQLException: Unable to create or alter sequence 
"DEPLOYMENT_ITEMS_TABLE_SEQ" because of invalid attributes (start value "1", 
min value "1", max value "9223372036854775807", increment "1"); SQL statement:
alter sequence  deployment_items_table_seq RESTART WITH (select max (id) from 
deployment_items_table ) + 1; [90009-176]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)
    at org.h2.message.DbException.get(DbException.java:178)
    at org.h2.schema.Sequence.modify(Sequence.java:120)
    at org.h2.command.dml.AlterSequence.update(AlterSequence.java:101)
    at org.h2.command.CommandContainer.update(CommandContainer.java:79)
    at org.h2.command.Command.executeUpdate(Command.java:254)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:186)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:160)
    at com.pontusnetworks.db.JdbcDeploymentItemsDAO.<init>(JdbcDeploymentItemsDAO.java:115)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)

Original comment by odranoe...@gmail.com on 17 Sep 2014 at 9:39

[GoogleCodeExporter]

You can use version 1.4.184 and add the option "MV_STORE=false" to your DB URL 
to get this fix

Original comment by noelgrandin on 6 Jan 2015 at 7:52