Invalid default value for 'created', but using CURRENT_TIMESTAMP for DATETIME

Question

I'm using the Sequel Pro app to work with my db.

Using Mysql 5.7.

I have the following table structure. When I attempt to reorder the 'created' table column, I get an error from mysql "Invalid default value for 'created'.

I have no rows in the materials table when attempting the reorder.

From everything I've read, CURRENT_TIMESTAMP is the correct default value.

CREATE TABLE `materials` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

My goal is to have the created column be automatically filled when the row is added. modified will automatically update to the current time when changed.

What am I missing?

Here is a post related to your question. [http://stackoverflow.com/questions/4897002/mysql-current-timestamp-on-create-and-on-update](http://stackoverflow.com/questions/4897002/mysql-current-timestamp-on-create-and-on-update) — Srini, Aug 31 '16 at 04:17
@Srini The post you linked to doesn't answer my issue. In fact, in a comment as recent as February of this year, it should be valid. http://stackoverflow.com/a/35426865/68788 — Geuis, Aug 31 '16 at 05:16

score 3 · Answer 1 · edited May 23 '17 at 12:16

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.

refer :Invalid default value for 'dateAdded'

Invalid default value for 'created', but using CURRENT_TIMESTAMP for DATETIME

1 Answers1