Invalid default value for 'dateAdded'

Question

I am having a stupid SQL issue that I cannot fix.

  ALTER TABLE `news` 
  ADD `dateAdded` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AUTO_INCREMENT,
  ADD PRIMARY KEY (`dateAdded`)

Mistake:

(#1067)Invalid default value for 'dateAdded' 

Can someone help me?

Answer 1

CURRENT_TIMESTAMP valid only in TIMESTAMP fields. DATETIME fields must be left with either a default value of zero or no default value - the default values ​​must be a constant value, not the result of an expression.

relevant documents: http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

You can get around this by setting the post-insert trigger in the table to populate the "now" value for any new entries.

Answer 2

CURRENT_TIMESTAMP is version specific and is now allowed for DATETIME columns since version 5.6.

See MySQL Docs .

Answer 3

Also note when specifying DATETIME as DATETIME(3) or as in MySQL 5.7.x, you must also add the same value for CURRENT_TIMESTAMP(3) . If not, it will continue to throw "Invalid default value."

Answer 4

I have mysql version 5.6.27 on my LEMP and CURRENT_TIMESTAMP, as the default works fine.

Answer 5

mysql version 5.5 sets the default datetime value, since CURRENT_TIMESTAMP will present a report error, you can upgrade to version 5.6, set the default datetime value as CURRENT_TIMESTAMP

Answer 6

Change the type from datetime to timestamp and it will work! I had the same problem for mysql 5.5.56-MariaDB - MariaDB Server Hope this can help ... sorry if deprived

Answer 7

I had the same problem, after fixing my problem was resolved.

• Select type as "TIMESTAMP"

• DO NOT GO IN SEVERAL FULL LENGTHS / VALUES. KEEP MAN

• Select CURRENT_TIMESTAMP as the default value.

I am using MySQL ver 5.5.56