Tuesday, March 27, 2018

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

By Hường Hana 5:30 AM , Leave a Comment

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?

4 Answers

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

Answers 2

The specific column must be missing in the insert statement because the error message stated Invalid default value. Please take a look at: Invalid default value for 'create_date' timestamp field (and read the helpful comments here). ;-)

Answers 3

Perhaps database lost the default value for the date. You can try either of the following:

ALTER TABLE mydb.mytable MODIFY myfield datetime DEFAULT CURRENT_TIMESTAMP;

Or 

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

Answers 4

  1. If you are inserting the last_update value manually from php code then make the mysql filed as var-char as you passing the date is not recognize by database as date and this error is occurring.

  2. Or you can just set the default value as CURRENT_TIMESTAMP and set attribute as on update insert CURRENT_TIMESTAMP. enter image description here

So that when ever any update on filed it will update the CURRENT_TIMESTAMP automatically.

If You Enjoyed This, Take 5 Seconds To Share It

0 comments:

Post a Comment