What Liquibase XML element to use to reset AUTO INCREMENT counter for a column in MySQL?

Question

In MySQL you would use this kind of SQL to reset the AUTO INCREMENT counter for a table:

ALTER TABLE my_table AUTO_INCREMENT = 200;

What is the corresponding XML element to use in Liquibase?

I'm writing a changeSet that loads fresh data into an existing table, and I'd like the auto-increment primary key to start counting from 1 again. i.e.:

<changeSet id="reload-data" author="neek">
    <delete tableName="my_table"></delete>
    <!-- want to reset the auto_increment counter here -->
    <loadData tableName="my_table"
        file="data/fresh_data.csv" separator=","/>
    <rollback></rollback>
</changeSet>

This loads the data OK but the id column continues its value from the last known id, which is normal because I haven't reset the AUTO_INCREMENT for the table.

I can use a raw sql element, i.e.:

<sql>ALTER TABLE my_table AUTO_INCREMENT = 1</sql>

But I worry the syntax would be different for other databases and best handled by a specific Liquibase command.

Perhaps we could use addAutoIncrement https://docs.liquibase.com/change-types/add-auto-increment.html on the column, which already is auto increment, to reset it? Seems a bit heavy handed.

Answer 1

There's no Liquibase change for resetting autoIncrement.

As you mentioned, you can always use <sql> change.

If you have to use Liquibase changes (assuming you don't have any foreign keys related to that column, since you want to reset autoIncrement), as a not very good workaround you may try dropping the autoIncremented column, and then recreating it.

<!-- drop primary key -->
<changeSet id="foo1" author="bar">
    <dropColumn tableName="table_name" columnName="auto_incremented_column"/>
</changeSet>

<!-- recreate autoIncremented column -->
<changeSet id="foo2" author="bar">
    <addColumn tableName="table_name">
        <column name="auto_incremented_column" autoIncrement="true" type="bigserial">
            <constraints primaryKey="true"/>
        </column>
    </addColumn>
</changeSet>