Validation error when creating auto increment column on Mysql

[jfneis]

Search first

• I searched and no similar issues were found

Description

Tried to create an auto increment column with a unique constraint in a mysql table, but Liquibase Validation doesn't allow it with the message "Cannot add a non-primary key identity column".

Despite the validation, coded on this line, mysql actually supports an auto increment column not being a PK, as long as it is a key itself (unique in this case).

Steps To Reproduce

Try to create a an auto increment column with a unique constraint, and it will fail:

        <addColumn tableName="campanha">
            <column autoIncrement="true" name="cd_campanha" type="INT(10)">
                <constraints unique="true" uniqueConstraintName="cd_campanha_UK" nullable="false" />
            </column>
        </addColumn>

Nonetheless, if we run an equivalent SQL command it works:

        <sql>
            alter table campanha
                add cd_campanha int not null auto_increment,
                add constraint cd_campanha_UK unique (cd_campanha)
        </sql>

Expected/Desired Behavior

Expected behavior is that Liquibase Validation allows to create an auto increment column in mysql, even if it's not a primary key, as long as it has a unique constraint associated with it.

Liquibase Version

4.9.1

Database Vendor & Version

mysql 8.2.-0

Liquibase Integration

spring boot

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

Ubuntu

Additional Context

No response

Are you willing to submit a PR?

• I'm willing to submit a PR (Thank you!)

[tati-qalified]

Hi @jfneis, thank you for reporting this issue.
Have you tried using the latest Liquibase version? Is the problem still replicable with it?

Let me know.
Thank you,
Tatiana

[jfneis]

Hi @tati-qalified!

Yes, running validate using Liquibase 4.27.0 via command line the error persists, thus showing that it's not a Spring integration problem.

[2024-04-30 15:06:40] INFO [liquibase.ui] ERROR: Exception Primary Source:  4.27.0
[2024-04-30 15:06:40] INFO [liquibase.command] Command execution complete
[2024-04-30 15:06:40] SEVERE [liquibase.integration] Validation Failed:
     1 changes have validation failures
          Cannot add a non-primary key identity column, changelogs/add_auto_increment_non_pk_column.xml::202404291200-1::jfneis
liquibase.exception.CommandExecutionException: liquibase.exception.ValidationFailedException: Validation Failed:
     1 changes have validation failures
          Cannot add a non-primary key identity column, changelogs/add_auto_increment_non_pk_column.xml::202404291200-1::jfneis

[jfneis]

Complementing the possible solution: I found that Mysql allows an auto increment column to be added as a non primary key as long as the new column is a "key" itself. So it will work if:

• The column has a unique constraint associated to it
• The column has a index associated to it
• The column has a FK associated to it (referencing another table)

As an example, all 3 commands below would successfully create an auto increment column in a table that already has a PK:

-- works
        alter table teste
                add cd_campanha int not null auto_increment,
                add constraint cd_campanha_UK unique (cd_campanha)

-- works
alter table teste
                add cd_campanha int not null auto_increment,
                add index (cd_campanha)

-- works
alter table teste
                add cd_campanha int not null auto_increment,
                add foreign key fk (cd_campanha) references teste2 (id)

[tati-qalified]

@jfneis I figured out what the problem is - the autoIncrement="true" attribute is being processed before the unique constraint is added, so the error is technically correct at that point. If the autoIncrement is added after setting the constraint, then the changeset will run as expected:

  <changeSet id="1" author="1">
    <addColumn tableName="campanha">
      <column name="cd_campanha" type="INT(10)">
        <constraints unique="true" uniqueConstraintName="cd_campanha_UK" nullable="false" />
      </column>
    </addColumn>
  </changeSet>

  <changeSet id="2" author="1">
    <addAutoIncrement tableName="campanha" columnName="cd_campanha" columnDataType="INT(10)"/>
  </changeSet>

It's a simple workaround, though it might get tedious if you need many columns with this configuration.

I see that you're willing to submit a PR - our development team is available to provide guidance if needed, so feel free to submit it and ask any questions you may have.

Thank you,
Tatiana

[jfneis]

@tati-qalified thanks for pointing out this behavior.

The given examples work as there is no validation about column keys when adding an auto increment separately. Actually even not adding the unique constraint, liquibase validates the changeSets and tries to apply them to the database, thus failing with

liquibase.exception.DatabaseException: Incorrect table definition; there can be only one auto column and it must be defined as a key [Failed SQL: (1075) ALTER TABLE teste.teste MODIFY cd_campanha INT AUTO_INCREMENT]
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:473)

I'm OK with this exception as liquibase doesn't check the current database characteristics before applying the change sets. Basically, it can't check if the column is not a key in this situation, opposed to when it's adding a new column.

All said, the problem remains in the add column statement in a non primary key auto increment column, as liquibase incorrectly doesn't allow a changeSet generated by diffChangeLog to be applied. I will open a PR for this!

Thanks for your help!