Live MySQL db takes the value "CURRENT_TIMESTAMP" as the datetime value - local is not

Question

I took a project that a few years ago and noted that CURRENT_TIMESTAMP is sent with a lot of php calls to update the datetime field in a lot of rows. This works great in a live environment, however, this is not the case on my local installation.

Both Live DB and my local version of the WAMP64 download work on MySQL5.7.19.

The PHP script executing the request, which includes the insert CURRENT_TIMESTAMP, will return with the following error:

Invalid default value for 'last_update' timestamp 

Again, on a real server, this works without problems. Both use MySQLi to execute these insert queries.

Is there something I am missing here? Is there some server-side configuration parameter that allows CURRENT_TIME to be inserted into the timestamp field?

Answer 1

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

DATETIME values ​​must contain either a null default value or a default value in general - the default values ​​must be a constant value, not the result of an expression.

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

You can get around this by setting the post-insert trigger in the table to populate the "now" value for any new entries.

Answer 2

The specific column should not be present in the insert statement because the error message is indicated by Invalid default value . Take a look: Invalid default value for creation timed label field 'create_date' (and read useful comments here) .; -)

Answer 3

The database may have lost the default value for the date. You can try one of the following:

 ALTER TABLE mydb.mytable MODIFY myfield datetime DEFAULT CURRENT_TIMESTAMP; 

Or

 ALTER TABLE mydb.mytable MODIFY myfield datetime DEFAULT NOW(); 

Answer 4

• If you insert the last_update value manually from php code, then make mysql registered as var-char, since you are passing the date, is not recognized by the database as the date and this error occurs.

• Or you can simply set the default value as CURRENT_TIMESTAMP and set the attribute, as when updating, insert CURRENT_TIMESTAMP.

So, when ever any updated update will automatically update CURRENT_TIMESTAMP.