Liquibase向MySQL写入错误默认日期值的Bug反馈与咨询

Problem Scenario #

When working with Liquibase 4.0.0 to manage a MySQL 8.0.15 database, I ran into an error while executing a changelog that creates a table with a DATETIME(3) column using CURRENT_TIMESTAMP(3) as its default value.

The Changelog in Question #

<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.0.xsd">
  <changeSet id="10" author="vaa25">
    <createTable tableName="test" schemaName="liquibase">
      <column name="id" type="INT" autoIncrement="true">
        <constraints primaryKey="true"/>
      </column>
      <column name="created_at" type="DATETIME(3)" defaultValueDate="CURRENT_TIMESTAMP(3)">
        <constraints nullable="false"/>
      </column>
    </createTable>
  </changeSet>
</databaseChangeLog>

Error Encountered #

liquibase.exception.DatabaseException: Invalid default value for 'created_at' [Failed SQL: (1067) CREATE TABLE liquibase.test (id INT AUTO_INCREMENT NOT NULL, created_at datetime(3) DEFAULT NOW() NOT NULL, CONSTRAINT PK_TEST PRIMARY KEY (id))]

Root Cause Analysis #

After troubleshooting, I found this is likely a bug in Liquibase 4.0.0: the tool incorrectly replaces the specified CURRENT_TIMESTAMP(3) with NOW() when generating the SQL. MySQL 8.0.15 requires the time function's precision to match the column's precision (3 in this case), so using the non-precise NOW() triggers the 1067 invalid default value error.

Temporary Workaround #

To get around this issue, you can bypass Liquibase's automatic SQL conversion by using the <sql> tag to execute raw native SQL directly. Here's the adjusted changelog:

<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.0.xsd">
  <changeSet id="10" author="vaa25">
    <sql>
      CREATE TABLE liquibase.test (
        id INT AUTO_INCREMENT NOT NULL,
        created_at datetime(3) DEFAULT CURRENT_TIMESTAMP(3) NOT NULL,
        CONSTRAINT PK_TEST PRIMARY KEY (id)
      )
    </sql>
  </changeSet>
</databaseChangeLog>

This ensures the exact SQL you intend is run without Liquibase altering the time function's precision.

内容的提问来源于stack exchange，提问作者Alexander Vlasov