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
– htshame, Commented Nov 8, 2019 at 8:15
No, I haven't. Timestamp fits better based on this thread: stackoverflow.com/questions/409286/… — Ihor M.
– Ihor M., Commented Nov 8, 2019 at 15:14
according to that thread - yes. But what about your own needs? — htshame
– htshame, Commented Nov 8, 2019 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.
– Ihor M., Commented Nov 8, 2019 at 16:08

htshame · Accepted Answer · 2019-11-10 06:20:00Z

1

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>

edited Nov 10, 2019 at 6:20

answered Nov 8, 2019 at 18:54

htshame

7,4655 gold badges43 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ihor M. Over a year ago

Yeah, I think you are right, I am tailoring that changeset, but in json. I'll report back soon.

Ihor M. Over a year ago

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?

htshame Over a year ago

adding something like defaultValueComputed=“UNIX_TIMESTAMP()” to the <column> tag of the foo1 changeSet should do it

Ihor M. Over a year ago

That function is allowed on selects, I tried to set it as default, no-go

htshame Over a year ago

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.

|

Collectives™ on Stack Overflow

Liquibase & MySQL 5.7 timestamp column modification problem

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related