102

I got a stupid problem with SQL that I can't fix.

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

Error: 

(#1067)Invalid default value for 'dateAdded'

Can somebody help me?

MPelletier
  • 16,256
  • 15
  • 86
  • 137
Robin Van den Broeck
  • 1,620
  • 4
  • 15
  • 19

8 Answers8

149

CURRENT_TIMESTAMP is only acceptable on TIMESTAMP fields. DATETIME fields must be left either with a null default value, or no default value at all - default values must be a constant value, not the result of an expression.

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

You can work around this by setting a post-insert trigger on the table to fill in a "now" value on any new records.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • 71
    It looks like as of mysql 5.6.5, you can use CURRENT_TIMESTAMP with DATETIME fields. See http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html – Frank Schwieterman Jul 25 '13 at 00:39
  • 2
    Thanks Frank, found this problem when trying to import an exported db from the latest xampp to an earlier one. – The HCD Aug 08 '14 at 23:03
  • The HCD , Frank ... found same issue exporting from MariaDb 10.x to MariaDB 5.5 ... thanks – Aukhan Jul 01 '15 at 19:57
  • 1
    This error happens in mariadb 5.5 also. Upgrade to mariadb 10 and its solved. Thanks Frank – Samuel Tesler Sep 17 '15 at 00:48
  • MySQL query browser seems to have a bug setting the default value for TIMESTAMPS. Use this query instead `ALTER TABLE tablename MODIFY COLUMN timestamp_colname TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;` – Muleskinner Apr 29 '22 at 09:29
54

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

See MySQL docs.

Michał Perłakowski
  • 88,409
  • 26
  • 156
  • 177
David Soussan
  • 2,698
  • 1
  • 16
  • 19
  • 10
    are you sure ? I get the above error in version 5.7.x – Ramesh Pareek Jul 12 '17 at 15:23
  • MySQL query browser seems to have a bug setting the default value for TIMESTAMPS. Use this query instead `ALTER TABLE tablename MODIFY COLUMN timestamp_colname TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;` – Muleskinner Apr 29 '22 at 09:31
21

Also do note when specifying DATETIME as DATETIME(3) or like on MySQL 5.7.x, you also have to add the same value for CURRENT_TIMESTAMP(3). If not it will keep throwing 'Invalid default value'.

informatik01
  • 16,038
  • 10
  • 74
  • 104
Torsten Ojaperv
  • 1,014
  • 17
  • 24
2

I had the same issue, following fix solved my problem.

  • Select Type as 'TIMESTAMP'

  • DON'T ENTER ANYTHING IN LENGTH/VALUES FIELD. KEEP IT BLANK

  • Select CURRENT_TIMESTAMP as Default value.

I am using MySQL ver 5.5.56

Darshn
  • 1,512
  • 1
  • 23
  • 31
0

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

Abraham Tugalov
  • 1,902
  • 18
  • 25
0

mysql version 5.5 set datetime default value as CURRENT_TIMESTAMP will be report error you can update to version 5.6 , it set datetime default value as CURRENT_TIMESTAMP

Dave2034
  • 11
  • 1
0

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

Hamid ER-REMLI
  • 200
  • 1
  • 7
0

I solved mine by changing DATE to DATETIME

iSafa
  • 165
  • 1
  • 8