Upgrading MySQL from 5.7 to 8.0 makes breaking changes to timestamp behaviour

[denny]

Specifically, the way setting of defaults works, and the way the column behaves once you've set your default. It doesn't appear to be possible to set a DEFAULT of current_timestamp and then assign null to it in your insert to get the row creation date - you either have to not set the column at all (and it will set the default), or assign the current time explicitly. The same applies to ON_UPDATE default values ... and now we get to the problem.

Because ShinyCMS has a whole bunch of columns with default behaviour for insert but not update, these are breaking changes. The tests have only picked it up in Blog and Newsletter, but I'll be very surprised if it's not endemic, I've used a similar default timestamp pattern in most models.

When fixing this, I think the best solution would be one that ends us up with two timestamps on every table, one for insert time and one for last update time, that set themselves automatically whenever possible (MySQL didn't support this behaviour when I started ShinyCMS, then it briefly did but I didn't get around to implementing it, and now I'm not sure if it does - I think it will work differently, at least). If the current timestamp value is used as content in the code (e.g. 'posted' in BlogPost), that should be split out into a third column, so we're not overloading the purpose of the created/updated metadata columns any more.

Any solution needs to continue working on MySQL 5.7 as well.