MySQL 5.6 to 5.7

[LawsHosting]

`creation_date` datetime DEFAULT CURRENT_TIMESTAMP,
`modif_date` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

Ok, so I need to migrate a .sql file from 5.6 to 5.7 (different servers), but the above schema errors:

Invalid default value for 'creation_date'

Do I need to edit the SQL_MODE?

 

[wattie]

try this:

`creation_date` datetime(0) DEFAULT CURRENT_TIMESTAMP(0),
`modif_date` datetime(0) DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0),

This "0" is the microsecond precision (can be from 0 to 6).

Alternatively do not use datetime datatype but timestamp. It will work with it.

 

[LawsHosting]

Didn't work... Syntax error.

It's a huge file...... Believe it or not, it is a Wordpress plugin , so I have no control what clients install...

 

[wattie]

CURRENT_TIMESTAMP is synonym of NOW()

Since MySQL 5.6.5 DEFAULT NOW() should be accepted. There are however evidences that it is not the case - for example if you look at the bottom of https://bugs.mysql.com/bug.php?id=27645, you'll see that a person complained on 29 Dec 2017 that "It is still happening of MySQL 5.7.18".

I am not sure what to say more on that... Strange. Make sure you are with the latest up to date MySQL 5.7.