CURRENT_TIMESTAMP with mariadb

General Discussion
1

Hello,


I’m trying to replicate the next sql:

  2. CREATE TABLE IF NOT EXISTS lang (
  3.     lang_id INT NOT NULL AUTO_INCREMENT,
  4.     country VARCHAR(2) NOT NULL,
  5.     language VARCHAR(2) NOT NULL,
  6.     description VARCHAR(45) NOT NULL,
  7.     created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  8.     PRIMARY KEY (lang_id)
  9. );

But when I run liquibase updateSQL i’m getting NOW() instead of CURRENT_TIMESTAMP. Is this the expected behavior?

  • liquibase updatesql
  • --  *********************************************************************
  • --  Update Database Script
  • --  *********************************************************************
  • --  Change Log: master.xml
  • --  Ran at: 7/16/14 12:19 AM
  • --  Against: root@localhost@jdbc:mysql://localhost:3306/xxxx
  • --  Liquibase version: 3.2.0
  • --  *********************************************************************

  • --  Lock Database
  • --  Changeset translate.xml::create table lang::fsousa
  • CREATE TABLE megan.lang (lang_id INT AUTO_INCREMENT NOT NULL, country CHAR(2) NOT NULL, language CHAR(2) NOT NULL, description VARCHAR(45) NOT NULL, created TIMESTAMP DEFAULT NOW() NOT NULL, CONSTRAINT PK_LANG PRIMARY KEY (lang_id));

  • INSERT INTO megan.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, LIQUIBASE) VALUES ('create table lang', 'fsousa', 'translate.xml', NOW(), 9, '7:14277505d1b016b84d087f4f542547a5', 'createTable', '', 'EXECUTED', '3.2.0');

  • --  Release Database Lock
  • Liquibase 'updatesql' Successful
    • 2

    There is no support for mariadb yet. I’m hoping to get it in with 3.3.0 but currently it falls back to “unknown database” which doesn’t always match with mariadb.

    Nathan

    3

    Actually, I may try to put some support into 3.2.1 which should hopefully come out today. I won’t do extensive testing, but it should work like mysql does which should work with mariadb.

    Nathan

    4

    With some testing, it appears that the maria support works just fine, they do a good job of pretending they are mysql. Even using a “jdbc:mariadb” URL with org.mariadb.jdbc.Driver.

    So the issue you are seeing is actually expected behavior. Liquibase uses CURRENT_TIMESTAMP as a placeholder for “The current timestamp function on this database” which on mariadb is NOW() so it helpfully converts it for you.

    Are you wanting to retain CURRENT_TIMESTAMP in your sql?

    Nathan

    5

    Hi,

    I didn’t even know there was a specific driver for mariadb. I was using the official mysql jdbc driver. I do not know if it make any difference.

    I’m moving my scripts from mysql to mariadb and I wan’t to retain what I have.

    I’m using mariadb 10.0.12 and the default value for TIMESTAMP is CURRENT_TIMESTAMP by default

    2. [fsousa@imac ~]$ mysql
    3. Welcome to the MariaDB monitor.  Commands end with ; or \g.
    4. Your MariaDB connection id is 17
    5. Server version: 10.0.12-MariaDB Homebrew
    7. Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
    9. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    11. MariaDB [(none)]> create database test;
    12. Query OK, 1 row affected (0.00 sec)
    14. MariaDB [(none)]> use test;
    15. Database changed
    16. MariaDB [test]> CREATE TABLE t (id INT, ts TIMESTAMP);
    17. Query OK, 0 rows affected (0.02 sec)
    19. MariaDB [test]> desc t
    20.     -> ;
    21. +-------+-----------+------+-----+-------------------+-----------------------------+
    22. | Field | Type      | Null | Key | Default           | Extra                       |
    23. +-------+-----------+------+-----+-------------------+-----------------------------+
    24. | id    | int(11)   | YES  |     | NULL              |                             |
    25. | ts    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
    26. +-------+-----------+------+-----+-------------------+-----------------------------+
    27. 2 rows in set (0.00 sec)
    29. MariaDB [test]>

    Taken from  https://mariadb.com/kb/en/mariadb/mariadb-documentation/data-types/date-and-time-data-types/timestamp/