5

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?

ICE
  • 1,667
  • 2
  • 21
  • 43
Eoghan
  • 1,720
  • 2
  • 17
  • 35
  • Have you tried using using `SET SQL_MODE='ALLOW_INVALID_DATES';`? – Script47 Mar 20 '18 at 12:52
  • 1
    Maybe this can help https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html – jornare Mar 20 '18 at 12:57
  • It sounds like your local version of MySQL has [`explicit_defaults_for_timestamp`](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp) set to false? – eggyal Mar 21 '18 at 14:15
  • 2
    Please provide `SHOW CREATE TABLE`. Are both running 5.7.19? And did you upgrade versions recently? – Rick James Mar 21 '18 at 14:37
  • 1
    Can you show those queries please with emphasis on the values that you're trying to insert into the column that represents the current timestamp ? – user10089632 Mar 22 '18 at 07:06
  • Present your [MCVE]. There is no reason for `NOW()` to be rejected for a `DATETIME` or `TIMESTAMP` field (which is it?). The error likely comes from a _different_ field for which you did not provide a value, then the behaviour relies on [`explicit_defaults_for_timestamp`](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp). But we cannot answer until you show us the problematic SQL and schema. – Lightness Races in Orbit Mar 26 '18 at 23:53

4 Answers4

2

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.

Varun Malhotra
  • 1,202
  • 3
  • 15
  • 28
2

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();
Jax297
  • 617
  • 1
  • 6
  • 8
1

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). ;-)

MichaEL
  • 84
  • 4
0

  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.

Bibhudatta Sahoo
  • 4,808
  • 2
  • 27
  • 51