cacheSize of createSequence is ignored

[mickroll]

Environment

Liquibase Version: 4.4.3

Liquibase Integration & Version: quarkus 2.3.0.Final

Liquibase Extension(s) & Version: liquibase-core only

Database Vendor & Version: MariaDB 10.6.4

Operating System Type & Version: Windows 10

Description

The Attribute cacheSize of createSequence seems to be ignored.

Steps To Reproduce

Run the following changesets:

    <changeSet id="working" author="mickroll">
        <createSequence sequenceName="SEQ_WORKING"
                            startValue="10000000"
                            incrementBy="1" />
        <sql dbms="mysql,mariadb">ALTER SEQUENCE SEQ_WORKING CACHE=5;</sql>
    </changeSet>
    <changeSet id="attribute" author="mickroll">
        <createSequence sequenceName="SEQ_SET"
                            startValue="10000000"
                            incrementBy="1"
                            cacheSize="5" />
    </changeSet>
    <changeSet id="default" author="mickroll">
        <createSequence sequenceName="SEQ_DEFAULT"
                            startValue="10000000"
                            incrementBy="1" />
    </changeSet>

• changeset working shows the wanted end result
• changeset attribute shows usage of createSequence cacheSize (which will be ignored)
• changeset default shows the default value of cacheSize (as defined by the database, i guess)

Now run the following SQL on the database to extract the results (or view the created sequences in the db client of our choice):

SELECT (SELECT cache_size FROM SEQ_WORKING) AS "working",
       (SELECT cache_size FROM SEQ_SET) AS "set with attribute",
       (SELECT cache_size FROM SEQ_DEFAULT) AS "default";

Actual Behavior

• working = 5 (as expected)
• set with attribute = 1000 (bug)
• default = 1000 (shows default value)

Expected/Desired Behavior

• set with attribute = 5 (as defined by the attribute cacheSize of createSequence tag)

Screenshots (if appropriate)

Additional Context

Doesn't work, either: <alterSequence sequenceName="SEQ_SET" cacheSize="5"/>

┆Issue is synchronized with this Jira Story by Unito

[molivasdat]

HI @mickroll Thanks for creating this issue and notifying us about it. We will add this to the list of issues that we are processing. If you have a PR with a fix for this, that would be great.

[mickroll]

A colleague of mine (@tkalmar) just wrote that he may have located the bug:

if (database instanceof OracleDatabase || database instanceof Db2zDatabase || database instanceof PostgresDatabase) {

see https://github.com/liquibase/liquibase/blob/master/liquibase-core/src/main/java/liquibase/sqlgenerator/core/CreateSequenceGenerator.java#L95

-->MariaDB is ignored here

Btw. CYCLE is also ignored for MariaDB:
https://github.com/liquibase/liquibase/blob/master/liquibase-core/src/main/java/liquibase/sqlgenerator/core/CreateSequenceGenerator.java#L117
But it seems to be supported: https://mariadb.com/kb/en/create-sequence/

CREATE [OR REPLACE] [TEMPORARY] SEQUENCE [IF NOT EXISTS] sequence_name
[ INCREMENT [ BY | = ] increment ]
[ MINVALUE [=] minvalue | NO MINVALUE | NOMINVALUE ]
[ MAXVALUE [=] maxvalue | NO MAXVALUE | NOMAXVALUE ]
[ START [ WITH | = ] start ] 
[ CACHE [=] cache | NOCACHE ] [ CYCLE | NOCYCLE] 
[table_options]

Same goes for AlterSequence: https://github.com/liquibase/liquibase/blob/master/liquibase-core/src/main/java/liquibase/sqlgenerator/core/AlterSequenceGenerator.java#L75

[mickroll]

Having the first deeper look at this repository, I have a bit of a hard time reading and understanding this part of the code, but shouldn't polling boolean AbstractJdbcDatabase#supportsSequences() be the right way to go, instead of those instanceof-chains? Or having more sequence-related methods like boolean AbstractJdbcDatabase#supportsSequenceCache() and boolean AbstractJdbcDatabase#supportsSequenceCycle()

[nvoxland]

It may be better to have more function checks like that vs instanceof. The trouble is that there is sort of an unlimited variation in what features are supported and not supported across the databases and also we hit variations in the SQL to generate depending on the database as well.

We're looking at some refactoring to make that all work better, but it's a medium-term project. In the meantime, I opened a PR that handles it the current way.

[mickroll]

Thank you!