Liquibase & MySQL 5.7 timestamp column modification problem

Question

I have a column called updated_time it has a timestamp data type assigned:

`updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

The problem is that currently, this column supports a second precision. I need it to be able to store a millisecond precision.

So, I have prepared a following Liquibase changeset:

{
  "databaseChangeLog": [
    {
      "changeSet": {
        "id": "1",
        "changes": [
          {
            "modifyDataType": {
              "columnName": "updated_time",
              "newDataType": "timestamp(3)",
              "tableName": "mytable"
            },
            "addDefaultValue": {
              "columnDataType": "timestamp(3)",
              "columnName": "updated_time",
              "defaultValueComputed": "current_timestamp(3)",
              "tableName": "mytable"
            },
            "addNotNullConstraint": {
              "columnDataType": "timestamp(3)",
              "columnName": "updated_time",
              "tableName": "mytable"
            }
          }
        ]
      }
    }
  ]
}

Unfortunately, this changeset always fails.

Here is the reason:

2019-11-08 00:57:28.624  WARN [] 99326 --- [           main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [org/springframework/boot/autoconfigure/liquibase/LiquibaseAutoConfiguration$LiquibaseConfiguration.class]: Invocation of init method failed; nested exception is liquibase.exception.MigrationFailedException: Migration failed for change set ...
     Reason: liquibase.exception.DatabaseException: Invalid default value for 'updated_time' [Failed SQL: (1067) ALTER TABLE maas.mytable MODIFY updated_time timestamp(3)]

I saw some posts regarding the fact that there were some changes to the timestamp data type in version 5.7. That, by default, I cannot assign a non-zero (null) value to the timestamp column. And, in order, to get it fixed, I'd have to make some changes to the server config itself. Let's imagine that I have a limited access to the server and I can't simply go and modify the config, is there an elegant way of working around this issue in Liquibase?

Have you tried using `datetime(3)` instead of `timestamp(3)`? — htshame, Nov 08 '19 at 08:15
No, I haven't. Timestamp fits better based on this thread: https://stackoverflow.com/questions/409286/should-i-use-the-datetime-or-timestamp-data-type-in-mysql — Ihor M., Nov 08 '19 at 15:14
according to that thread - yes. But what about your own needs? — htshame, Nov 08 '19 at 15:42
to be honest with you, I need that column to store epoch value. So, I think `bigint` is the best fit. But I do not know how to modify the type of the column and convert data from `timestamp` to `bigint` in one changeset. If there is a way to do it, I would go with that approach. Otherwise, I'd keep it a `timestamp`. — Ihor M., Nov 08 '19 at 16:08

htshame · Accepted Answer · 2019-11-10T06:20:00.947

If you want to convert timestamp to bigint, then you can do it the following way:

create a new column, e.g. bigint_date;
populate it with bigint values from your current timestamp_date column;
drop your timestamp_date column;

Liquibase changeSets may look like this:

<changeSet id="foo1" author="bar">
    <preConditions onFail="MARK_RAN">
        <not>
            <columnExists tableName="your_table" columnName="bigint_date"/>
        </not>
    </preConditions>
    <comment>Add new column</comment>
    <addColumn tableName="your_table">
        <column name="bigint_date" type="bigint(13)"/>
    </addColumn>
</changeSet>
<changeSet id="foo2" author="bar">
    <preConditions onFail="MARK_RAN">
        <columnExists tableName="your_table" columnName="bigint_date"/>
        <columnExists tableName="your_table" columnName="timestamp_date"/>
    </preConditions>
    <comment>Populate it with bigint values from your current "timestamp_date" column</comment>
    <update tableName="your_table">
        <column name="bigint_date" valueComputed="SELECT UNIX_TIMESTAMP(timestamp_date) FROM your_table"/>
    </update>
</changeSet>
<changeSet id="foo3" author="bar">
    <preConditions onFail="MARK_RAN">
        <columnExists tableName="your_table" columnName="timestamp_date"/>
    </preConditions>
    <comment>Drop your "timestamp_date" column</comment>
    <dropColumn tableName="your_table" columnName="timestamp_date"/>
</changeSet>
<changeSet id="foo4" author="bar">
    <preConditions onFail="MARK_RAN">
        <columnExists tableName="your_table" columnName="bigint_date"/>
    </preConditions>
    <comment>Update NULL values</comment>
    <update tableName="your_table">
        <column name="bigint_date" valueComputed="UNIX_TIMESTAMP()"/>
        <where>bigint_date IS NULL</where>
    </update>
</changeSet>

Yeah, I think you are right, I am tailoring that changeset, but in json. I'll report back soon. — Ihor M., Nov 08 '19 at 18:57
It works fine, the only thing is left: how do I set the default value to my new bigint column to current timestamp? Is there a way? — Ihor M., Nov 09 '19 at 16:49
adding something like `defaultValueComputed=“UNIX_TIMESTAMP()”` to the `` tag of the `foo1` changeSet should do it — htshame, Nov 09 '19 at 19:27
That function is allowed on selects, I tried to set it as default, no-go — Ihor M., Nov 10 '19 at 02:12
Oh, right. My bad. I've updated my answer with `foo4` changeSet. It'll populate all NULL values (if there're any) of `bigint_date` with current timestamp. I guess you'll have to populate `bigint_date` with values in your code for future records. — htshame, Nov 10 '19 at 06:22
All of my values are non-nulls already. I just wanted to add a default value that would set it to the current epoch if the app hasn't provided it. I read that some people are implementing a trigger for it, but it seems too much, way too involved. I'll just make sure that the application code always sets it. Thanks for your help! — Ihor M., Nov 10 '19 at 13:59

Liquibase & MySQL 5.7 timestamp column modification problem

1 Answers1