CODEWITHSUNDEEP
mysqltimestampliquibaseliquibase-hibernateliquibase-sql
luso
luso

Reputation: 3000

Force liquibase to current_timestamp instead of now()

Using liquibase-core:3.6.3 with MySQL.

For the first time I have to use a timestamp(3) for a column instead on default timestamp. As my timestamp columns are not nullable, if no DEFAULT value is set, the current_timestamp is added (either directly in MySQL or with liquibase).

Not I face the problem that liquibase generated SQL doesn't use CURRENT_TIMESTAMP but NOW() as the default function to be called. This makes the timestamp(3) impossible.

Is there a way to force liquibase to use CURRENT_TIMESTAMP in the output rather than NOW()?

- changeSet:
      id: xxx
      author: xxx
      changes:
        - createTable:
            tableName: table_name
            columns:
              - column:
                  name: id
                  type: int unsigned
                  autoIncrement: false
                  constraints:
                    primaryKey: true
              - column:
                  name: updated
                  type: timestamp
                  defaultValueComputed: current_timestamp
                  constraints:
                    nullable: false

OUTPUT: CREATE TABLE db_name.table_name(id INT unsigned NOT NULL, updated timestamp DEFAULT NOW() NOT NULL, CONSTRAINT PK_RULE_STATE PRIMARY KEY (id));

This works fine. However, changing timestamp to timestamp(3)

OUTPUT: CREATE TABLE db_name.table_name(id INT unsigned NOT NULL, updated timestamp(3) DEFAULT NOW() NOT NULL, CONSTRAINT PK_RULE_STATE PRIMARY KEY (id)); where NOW() is not a valid value.

Same output using defaultValueDate

If I use defaultValue: current_timestamp() liquibase is smart enough to detect it as a function at it does not use the literal string, however, this is still an invalud value for timestamp(3). At the end, using

- changeSet:
          id: xxx
          author: xxx
          changes:
            - createTable:
                tableName: table_name
                columns:
                  - column:
                      name: id
                      type: int unsigned
                      autoIncrement: false
                      constraints:
                        primaryKey: true
                  - column:
                      name: updated
                      type: timestamp(3)
                      defaultValue: current_timestamp(3)
                      constraints:
                        nullable: false

produces the output:

CREATE TABLE db_name.table_name(id INT unsigned NOT NULL, updated timestamp(3) DEFAULT DEFAULT 'current_timestamp(3)' NOT NULL, CONSTRAINT PK_RULE_STATE PRIMARY KEY (id));

My last chance is NOT SETTING a default value at all. This works fine except for the fact that, as this guy said long ago, MySQL adds the ON UPDATE CURRENT_TIMESTAMP(3) and I can't get to avoid it.

So, is there a way to force to current_timestamp(3) or as a backup plan, to prevent MySQL to generate the ON UPDATE part?

Upvotes: 3

Views: 5105

Answers (3)

Roberto Fernández Iglesias
Roberto Fernández Iglesias

Reputation: 61

As described in this GitHub issue you can use value NOW(3) for defaultValueComputed like in the following example:

<column defaultValueComputed="NOW(3)" name="DateTime" type="TIMESTAMP(3)">
    <constraints nullable="false" />
</column>

Then, result is the following:

mysql> SHOW COLUMNS FROM myTable;
+-----------+---------------+------+-----+----------------------+-------------------+
| Field     | Type          | Null | Key | Default              | Extra             |
+-----------+---------------+------+-----+----------------------+-------------------+
| DateTime  | timestamp(3)  | NO   |     | CURRENT_TIMESTAMP(3) | DEFAULT_GENERATED |
+-----------+---------------+------+-----+----------------------+-------------------+

Upvotes: 1

GuyM
GuyM

Reputation: 11

Instead of custom liquibase classes, you can use the modifySql command with the replace option to replace the auto-generated NOW() with CURRENT_TIMESTAMP(3) (or whatever you need).

From the docs:

...there are times when the generated SQL needs to be slightly different for your particular needs

See here for more details: https://docs.liquibase.com/workflows/liquibase-community/modify-sql.html

Upvotes: 1

sudo
sudo

Reputation: 775

If you look at the Database configuration in the liquibase repositoy you see that indeed NOW() is set as the currentDateTimeFuntion for MySQL.

public MySQLDatabase() {
    super.setCurrentDateTimeFunction("NOW()");
    ...
}

So one way to force current_timestamp(3) is to use a custom liquibase.database.Database implementation based on the MySQLDatabase:

public class CustomMySQLDatabase extends MySQLDatabase {

    public CustomMySQLDatabase() {
        super();
        super.setCurrentDateTimeFunction("CURRENT_TIMESTAMP(3)");
    }
    ...

Afterwards you can use the CustomMySQLDatabase as --databaseClass Parameter when calling liquibase.

Upvotes: 2

Related Questions