CODEWITHSUNDEEP
mysqlmariadb
Toan Nguyen
Toan Nguyen

Reputation: 1103

Default of datetime in mariadb

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 ?

Upvotes: 7

Views: 13456

Answers (2)

markusjm
markusjm

Reputation: 2562

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).

Upvotes: 3

inanutshellus
inanutshellus

Reputation: 10001

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;

Upvotes: 1

Related Questions