Incorrect datetime value: 'DEFAULT' using mysql in node.js

Question

Using mysql 5.7 and node 12.

The table schema is like this

  name  varchar(255)   not null  primary key,
  createdAt  timestamp    default CURRENT_TIMESTAMP not null,

Now if i do

mysql> insert into myTable (name, createdAt) values ('name1', 'DEFAULT');

it works, but if i do

mysql> insert into myTable (name, createdAt) values ('name1', null);

it will say "cannot be null".

So i try to set the value to be DEFAULT if needed, go to my node code,

const addEntry (inputObj) => {
    const sqlCmd = `insert into myTable (name, createdAt)
           values ( ? ? )`;
    const param = ['name1', inputObj.createdAt || 'DEFAULT'];
    mysql.query(sqlCmd, param);
}

when i test it, don't define the createdAt property, inputObj = { 'name': 'name1' };

it errors

Error:  ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: 'DEFAULT' for column 'createdAt' at row 1

How to fix this please ?

When is the insert happening? During user's input? Or are you performing insert from another table? — FanoFN
– FanoFN, Commented Sep 13, 2019 at 1:04
i think i'll just build the sqlCmd and param based on if inputObj.createdAt is null or not. — user3552178
– user3552178, Commented Sep 13, 2019 at 3:23

GMB · Accepted Answer · 2019-09-12 22:34:37Z

1

You have defined a column that has both a NOT NULL constraint and a DEFAULT constraint.

You cannot assign a NULL value to that column because of the NOT NULL constraint, as you noticed: NULL is still considered a value.

If you want to benefit the DEFAULT constraint, you can either not assign a value to the column, or use expresssion DEFAULT (as a litteral, ie unquoted, else MySQL considers it as a string, which, as you would expect, cannot be assigned to a timestamp column):

insert into myTable (name) values ('name1');
insert into myTable (name, createdAt) values ('name1', DEFAULT);

answered Sep 12, 2019 at 22:34

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

user3552178 Over a year ago

Thx for the explanation, my real Q is: createdAt could be null, or could be a valid date from input, what's the best concise way to write in node ? don't want to use if/else.

GMB Over a year ago

@user3552178: I think you need some kind of if/else, one way or another. Why don’t you want to use if/else?

Collectives™ on Stack Overflow

Incorrect datetime value: 'DEFAULT' using mysql in node.js

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related