live MySQL db accepts 'CURRENT_TIMESTAMP" as datetime value - local does not

Question

I've picked up on a project that's a few years old, and noted CURRENT_TIMESTAMP is being sent with a lot of the php calls to update the datetime field in a lot of rows. This works perfectly on the live environment - however, on my local setup, it does not.

Both the Live DB, and my local version from the WAMP64 download are running on MySQL5.7.19.

A PHP script running a query that involves inserting CURRENT_TIMESTAMP will return back with the following error;

Invalid default value for 'last_update' timestamp

Again though, on the live server, this works without issue. Both are using MySQLi to carry out these insert queries.

Is there something I'm missing here? Some sort of server-side config setting that allows CURRENT_TIME to be inserted into the timestamp field?

Answer 1

The CURRENT_TIMESTAMP field automatically pick the current time of server.or will only accept the timestamp values.

So 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.