Default of datetime in mariadb

Question

I set default of datetime in mariadb 5.5 such as

CREATE TABLE IF NOT EXISTS test (
    `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

But it displays this error

Error 1067: Invalid default value for 'create_time'

How can I set default current time value in mariadb but don't use TIMESTAMP type ?

possible duplicate of http://stackoverflow.com/questions/168736/how-do-you-set-a-default-value-for-a-mysql-datetime-column — Gryphius, Oct 18 '13 at 13:54
and since mariadb is a drop-in replacement for mysql the answer probably applies...? — Gryphius, Oct 18 '13 at 13:57
because can set default current_timestamp in mysql but can't in mariadb so that I ask this question — Toan Nguyen, Oct 18 '13 at 14:00
mariadb 5.5 is based on mysql 5.5 where this didn't work either. mysql only supports this from 5.6.5 onwards according to that answer — Gryphius, Oct 18 '13 at 14:02
Unfortunately, you have to use MariaDB 10 to get this functionality. It's frustrating, I know, but it still hasn't been (may never be?) "backported" to MariaDB 5.5. — Eric L., Nov 01 '13 at 14:54
MariaDB 10.0 released: https://downloads.mariadb.org/mariadb/10.0/ — , May 23 '14 at 15:15

score 3 · Answer 1 · answered Jun 21 '18 at 04:37

In 5.5 you cannot use CURRENT_TIMESTAMP as the default value for a DATETIME column. This was later introduced in 10.0.1 to make its use possible.

Your options are to either upgrade to a newer version or to emulate it using a trigger (as was explained by inanutshellus).

inanutshellus · Answer 2 · 2018-06-21T04:01:27.490

1

Specify it using a trigger:

create trigger bi_mytable
before insert on mytable 
for each row 
begin
    NEW.create_time = NOW();
end;

And, since it's a "create" value, make sure nobody updates it:

create trigger bu_mytable
before update on mytable 
for each row 
begin
    NEW.create_time = OLD.create_time;
end;

edited Jun 21 '18 at 04:01

answered Jun 21 '18 at 03:43

inanutshellus

2 Answers2