MySQL 5.6 to 5.7

Peter Laws

Verified User
Joined
Sep 13, 2008
Messages
1,768
Location
London UK
`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

Verified User
Joined
May 31, 2008
Messages
1,052
Location
Bulgaria
try this:

Code:
`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.
 

Peter Laws

Verified User
Joined
Sep 13, 2008
Messages
1,768
Location
London UK
Didn't work... Syntax error.

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

wattie

Verified User
Joined
May 31, 2008
Messages
1,052
Location
Bulgaria
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.
 
Top