Invalid default value for 'dateAdded'
MysqlSqlMysql Problem Overview
I got a stupid problem with SQL that I can't fix.
ALTER TABLE ALTER TABLE news
ADD dateAdded DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AUTO_INCREMENT ,
ADD PRIMARY KEY ( dateAdded )
ADD ALTER TABLE ALTER TABLE news
ADD dateAdded DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AUTO_INCREMENT ,
ADD PRIMARY KEY ( dateAdded )
ADD dateAdded DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AUTO_INCREMENT ,
ADD PRIMARY KEY ( dateAdded )
DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AUTO_INCREMENT ,
ADD PRIMARY KEY ( ALTER TABLE ALTER TABLE news
ADD dateAdded DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AUTO_INCREMENT ,
ADD PRIMARY KEY ( dateAdded )
ADD ALTER TABLE ALTER TABLE news
ADD dateAdded DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AUTO_INCREMENT ,
ADD PRIMARY KEY ( dateAdded )
ADD dateAdded DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AUTO_INCREMENT ,
ADD PRIMARY KEY ( dateAdded )
DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AUTO_INCREMENT ,
ADD PRIMARY KEY ( dateAdded )
)
Error:
(#1067)Invalid default value for 'dateAdded'
Can somebody help me?
Mysql Solutions
Solution 1 - Mysql
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.
Solution 2 - Mysql
CURRENT_TIMESTAMP is version specific and is now allowed for DATETIME columns as of version 5.6.
See MySQL docs.
Solution 3 - Mysql
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'.
Solution 4 - Mysql
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
Solution 5 - Mysql
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
Solution 6 - Mysql
I have mysql version 5.6.27 on my LEMP and CURRENT_TIMESTAMP as default value works fine.
Solution 7 - Mysql
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
Solution 8 - Mysql
I solved mine by changing DATE to DATETIME