Liquibase - Add defaultValueComputed as CURRENT_TIMESTAMP to timestamp column

Question

I am using liquibase 3.5.3 to run liquibase update command on MySql 5.5. I have below changeSet to create a table which has a column as Created_Time that should have a default value as CURRENT_TIMESTAMP.

<changeSet author="authorName" id="AutoGeneratedId">
    <createTable tableName="aTable">
        <column autoIncrement="true" name="Id" type="INT">
            <constraints primaryKey="true"/>
        </column>
        <column name="Code" type="VARCHAR(45)"/>
        <column defaultValueComputed="CURRENT_TIMESTAMP" name="Created_Time" type="TIMESTAMP(19)"/>
    </createTable>
</changeSet>

While firing liquibase command it throws an exception as Unexpected error running Liquibase: Invalid default value for 'Created_Time' [Failed SQL: CREATE TABLE aTable (Id INT AUTO_INCREMENT NOT NULL, Code VARCHAR(45) NULL, Created_Time TIMESTAMP(19) DEFAULT NOW() NULL, CONSTRAINT PK_ATABLE PRIMARY KEY (Id))]

Liquibase is converting CURRENT_TIMESTAMP into NOW() that might be causing this issue.

Can some please provide me any solution or alternative to this issue?

Answer 1

Add type as 'TIMESTAMP' as following

<column defaultValueComputed="CURRENT_TIMESTAMP" name="Created_Time" type="TIMESTAMP"/>

Answer 2

This is a weird scenario causes by how liquibase handles DateTime defaultValueComputed or defaultValueDate.

In short anything that starts with current_timestamp or the default function on your target engine to get the current timestamp will replace *the whole string* by only the default function call ignoring anything else you put.

We ended up with something like this:

            <column name="created_at" type="datetime"
                defaultValueComputed="NOW()">
                <constraints nullable="false" />
            </column>
            <column name="updated_at" type="datetime"
                defaultValueComputed="NOW() ON UPDATE NOW()">
                <constraints nullable="false" />
            </column>

LiquibaseDataType helper function