What is the reason why MySQL is telling me "Invalid default value for 'postdate'"?

Question

I have 2 Windows servers running MySQL, one of them running version 5.1 and the other running version 5.7. I am trying trying to copy a database from the MySQL 5.7 over to the 5.1 on the other server and believe it's the difference in versions (new syntax in 5.7?) that is causing this error, but I could be wrong.

After Exporting through phpMyAdmin the database I in the 5.7 version and trying to Import in the 5.1 version I'm getting the error

MySQL said: Documentation #1067 - Invalid default value for 'postdate'

on the command

CREATE TABLE IF NOT EXISTS `jobs` (

 `id` mediumint( 9 ) NOT NULL ,
 `title` varchar( 200 ) DEFAULT NULL ,
 `descr` varchar( 5000 ) DEFAULT NULL ,
 `postdate` datetime DEFAULT CURRENT_TIMESTAMP 
) ENGINE = InnoDB AUTO_INCREMENT =5 DEFAULT CHARSET = utf8mb4;

Any idea why?

@Mihai Hmmm that's what the program `mysqldump` generated, though. Does `mysqldump` make errors sometimes? — , Jul 27 '15 at 16:52
5.1 cant have default on datetime column,change it to timestamp\ — Mihai, Jul 27 '15 at 16:54
you can check this question [invalid-default-value-for-dateadded](http://stackoverflow.com/questions/9005766/invalid-default-value-for-dateadded) — ar em, Jul 28 '15 at 05:27

score 1 · Accepted Answer · edited May 23 '17 at 11:43

In 5.1, the default value has to be a constant value (e.g. NULL is acceptable) except for the timestamp type where current_timestamp is allowed. I.e., for a date or datetime you cannot use current_date, now or current_timestamp.

So you either stick to the datetime type for your postdate column and you have to give up current_timestamp as a default value (you can maybe set up a trigger for the purpose, see examples here), or - depending on your requirements - consider using timestamp (which has a different range of values).

The corresponding section of the manual says:

With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.

What is the reason why MySQL is telling me "Invalid default value for 'postdate'"?

1 Answers1