Liquibase not working with mysql autoincrement

Frederic Close picture Frederic Close · Dec 9, 2013 · Viewed 10.5k times · Source

I try to create a new table via a liquibase changeset that looks like:

    <createTable tableName="mytable">
        <column name="id" type="number" autoIncrement="true">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="name" type="varchar(50)"/>
        <column name="description" type="varchar(255)"/>
        <column name="image_path" type="varchar(255)"/>
    </createTable>

this fails with following error:

liquibase.exception.DatabaseException: 
Error executing SQL CREATE TABLE 
kkm.mytable (id numeric AUTO_INCREMENT NOT NULL, name VARCHAR(50) NULL, description 
             VARCHAR(255) NULL, image_path VARCHAR(255) NULL, 
             CONSTRAINT PK_BOUFFE PRIMARY KEY (id)): 
Incorrect column specifier for column 'id'

if I set autoIncrement="false", this works perfectly.
Is this a known issue ?

EDIT:

this is working:

    <createTable tableName="mytable">
        <column name="id" type="number" autoIncrement="false">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="name" type="varchar(50)"/>
        <column name="description" type="varchar(255)"/>
        <column name="image_path" type="varchar(255)"/>
    </createTable>

    <addAutoIncrement
            columnDataType="int"
            columnName="id"
            incrementBy="1"
            startWith="1"
            tableName="mytable"/>

Answer

Waheed picture Waheed · Dec 10, 2013

Change type="number" to type="BIGINT".

i,e

 <createTable tableName="mytable">
        <column name="id" type="BIGINT" autoIncrement="true">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="name" type="varchar(50)"/>
        <column name="description" type="varchar(255)"/>
        <column name="image_path" type="varchar(255)"/>
    </createTable>

Hope it works..!!!!