IncrementBy does not work for MySQL in Liquibase 4.17

pschobers · October 21, 2022, 9:00am

Hi. We just migrated to Liquibase 4.17 (for the parallel updating).
When we try to run a script with an addAutoIncrement on a MySQL table, we get the following error:

 2022-10-21 09:45:36,293 ERROR - [petere]: Validation Failed:
     2 changes have validation failures
          incrementBy is not allowed on mysql, wokpdatabase-201907042004-COM1854.xml::COM-1854-001::rudie
          incrementBy is not allowed on mysql, wokpdatabase-201907042004-COM1854.xml::COM-1854-002::rudie
 [http-thread-pool::https-listener(5)] nl.tent.database.command.LiquibaseCommand.execute(39)
liquibase.exception.ValidationFailedException: Validation Failed:

I read somewhere that this issue was fixed in version 4.11 (Fixed autoIncrement incrementBy/startWith support in MySQL, H2, HSQLDB, and MariaDB by tozogabee · Pull Request #3026 · liquibase/liquibase · GitHub).
Is there a fix or a workaround?

tabbyfoo · October 27, 2022, 7:44pm

Mysql and MariaDB don’t support incrementBy in a way how other platforms do. They have auto_increment_offset var set globally or per session, but not for particular table.

github.com/liquibase/liquibase

addAutoIncrement isn't working properly for HyperSQL, Mysql, MariaDB and H2

opened 05:48PM - 20 May 22 UTC

closed 02:39PM - 20 Jul 22 UTC

KushnirykOleh

DBMySQL DBH2 DBMariaDB DBHSQLDB ver4.10.0 sprint2022-29

## Environment **Liquibase Version**: 4.10 **Liquibase Integration & Versi…on**: - **Liquibase Extension(s) & Version**: - **Database Vendor & Version**: Mysql, MariaDB, HyperSQL, H2 **Operating System Type & Version**: windows 10 ## Description Mysql and MariaDB don't support `incrementBy` in a way how other platforms do. They have `auto_increment_offset` var set globally or per session, but not for particular table. For H2 and HyperSQL databases generated queries are not correct ## Steps To Reproduce create xml changeLog file with next changesets ``` <changeSet id="1" author="as"> <createTable tableName="autoincrement_test"> <column name="intColumn" type="int"> <constraints primaryKey="true" nullable="false"/> </column> <column name="dateColumn" type="date"/> </createTable> <rollback> <dropTable tableName="autoincrement_test"/> </rollback> </changeSet> <changeSet id="2" author="as"> <addAutoIncrement columnDataType="int" columnName="intColumn" generationType="ALWAYS" incrementBy="2" startWith="100" tableName="autoincrement_test"/> <rollback/> </changeSet> ``` run `liquibase updateSql` against mentioned DBs observe generated SQL ## Actual Behavior Generated SQL for : 1) For Mysql and MariaDB, nothing about `incrementBy` may be confusing if user rely on `incrementBy` but didn't check generated SQL and expect it to be working ``` CREATE TABLE lbcat.autoincrement_test (intColumn INT NOT NULL, dateColumn date NULL, CONSTRAINT PK_AUTOINCREMENT_TEST PRIMARY KEY (intColumn)) ALTER TABLE lbcat.autoincrement_test MODIFY intColumn INT AUTO_INCREMENT ALTER TABLE lbcat.autoincrement_test AUTO_INCREMENT=100 ``` 2) For H2 ``` CREATE TABLE PUBLIC.autoincrement_test (intColumn INT NOT NULL, dateColumn date, CONSTRAINT PK_AUTOINCREMENT_TEST PRIMARY KEY (intColumn)) ALTER TABLE PUBLIC.autoincrement_test ALTER COLUMN intColumn int GENERATED BY DEFAULT AS IDENTITY (100, 2) ``` failing with `Syntax error in SQL statement "ALTER TABLE PUBLIC.autoincrement_test ALTER COLUMN intColumn int GENERATED BY DEFAULT AS IDENTITY ([*]100, 2)"; expected "START, RESTART, INCREMENT, MINVALUE, MAXVALUE, CYCLE, NO, EXHAUSTED, CACHE, NOMINVALUE, NOMAXVALUE, NOCYCLE, NOCACHE, )";` 3) For HyperSQL ``` ALTER TABLE PUBLIC.autoincrement_test ALTER COLUMN intColumn int GENERATED BY DEFAULT AS IDENTITY (START WITH 100, INCREMENT BY 2) ``` failing with `Reason: liquibase.exception.DatabaseException: unexpected token: , required: ) [Failed SQL: (-5581) ALTER TABLE PUBLIC.autoincrement_test ALTER COLUMN intColumn int GENERATED BY DEFAULT AS IDENTITY (START WITH 100, INCREMENT BY 2)]` ## Expected/Desired Behavior 1) If liquibase can't do anything about `incrementBy` for Mysql and MariaDB indicate that in https://docs.liquibase.com/change-types/add-auto-increment.html as not supported and throw validation error for this attribute in changeset. Fix if there is a way. 2) Fix query for H2 3) Fix query for HyperSQL to remove comma, query like `ALTER TABLE PUBLIC.autoincrement_test ALTER COLUMN intColumn int GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 2)` works fine ## Additional Context discovered in test-harness

pschobers · November 22, 2022, 11:31am

We’ve solved the problem by replacing the <addAutoIncrement> by sql-statements:
<addAutoIncrement tableName=“Actief” columnName=“id” columnDataType=“BIGINT” incrementBy=“1” startWith=“1”/>

    <sql>ALTER TABLE Actief MODIFY id BIGINT AUTO_INCREMENT</sql>
    <sql>ALTER TABLE Actief AUTO_INCREMENT=1</sql>

Topic		Replies	Views
addAutoIncrement does not work on Liquibase 3.5.3 for Postgres Liquibase Development	0	427	December 27, 2017
SMALLINT and MEDIUMINT with autoincrement in the changelog fails on update General Discussion	1	450	June 18, 2013
Support AutoIncrement in creating objects Liquibase Development	2	973	March 16, 2015
addAutoIncrement doesn't work for PostgreSQL General Discussion	2	1657	May 16, 2010
2.0.0 / 2.0 RC7 � generateChangeLog miss AutoIncrement General Discussion	2	312	January 5, 2011

IncrementBy does not work for MySQL in Liquibase 4.17

Related Topics