Liquibase H2 create sequence failed with bigint

user_11655178 0
Viewed 1507

Using liquibase 3.8.9, running following changeset success:

<changeSet author="quarkus" id="1-1">
        <createSequence cacheSize="1" cycle="false" incrementBy="1" maxValue="9223372036854775807" minValue="1" schemaName="myschema" sequenceName="mytable_id_seq" startValue="1"/>
</changeSet>

and adding data type bigint specification fail:

<changeSet author="quarkus" id="1-1">
    <createSequence cacheSize="1" cycle="false" dataType="bigint" incrementBy="1" maxValue="9223372036854775807" minValue="1" schemaName="myschema" sequenceName="mytable_id_seq" startValue="1"/>
</changeSet>

Stack trace

ERROR: Failed to start application
java.lang.IllegalStateException: liquibase.exception.MigrationFailedException: Migration failed for change set db-test/changelog-0.xml::1-1::quarkus:
     Reason: liquibase.exception.DatabaseException: Syntax error in SQL statement "CREATE SEQUENCE MYSCHEMA.MYTABLE_ID_SEQ AS[*] BIGINT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807"; SQL statement:
CREATE SEQUENCE myschema.mytable_id_seq AS bigint START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 [42000-200] [Failed SQL: (42000) CREATE SEQUENCE myschema.mytable_id_seq AS bigint START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807]

Is there a limitation with bigint data type?

Thanks

liquibase h2
1 Answers

As your exception says, there's an error in SQL syntax.

[42000-200] [Failed SQL: (42000) 
CREATE SEQUENCE myschema.mytable_id_seq AS bigint START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807

I believe it's because of the reserved word bigint. Perhaps it's a bug in Liquibase's mechanism of creation of SQL queries. I tried executing your changeSet on my local environment, and got the same error.

So I've removed dataType="bigint" from the changeSet and it worked.

<changeSet author="quarkus" id="1-1">
    <createSequence cacheSize="1" cycle="false" incrementBy="1"
                    maxValue="9223372036854775807" minValue="1" schemaName="public"
                    sequenceName="mytable_id_seq" startValue="1"/>
</changeSet>

After some Liquibase source code debugging, I've found the SQL query that's being generated for this changeSet:

CREATE SEQUENCE MYSCHEMA.MYTABLE_ID_SEQ 
START WITH 1 
INCREMENT BY 1 
MINVALUE 1 
MAXVALUE 9223372036854775807

And as was mentioned by @a_horse_with_no_name in the comments, the dataType is bigint by default.

user_60129390
Related