ProcessWire
ProcessWire copied to clipboard
ryancramerdesign
Change invalid date format (0000-00-00 00:00:00) to current timestamp
Is there any reason why the exported sql contains 0000-00-00 00:00:00 instead of timestamp? This will give error to some server that has strict permission on date format.
Reference: http://stackoverflow.com/questions/9192027/invalid-default-value-for-create-date-timestamp-field
I've not heard of this particular error occurring for anyone else before, but looks like we should stop using that date as our default for unknown date. We can't use a unix timestamp here as MySQL requires it to be in that Y-m-d format. We probably need to switch to a null default for these dates. I will investigate further. Thanks.
How about just use CURRENT_TIMESTAMP? Since it's make sense that some module and pages are installed on the date of installation :)
Additional information is this happen on mac os with mysql that is not installed using homebrew.
Yeah I think you are right CURRENT_TIMESTAMP is probably the way to go here. I like to have the option of having an "unknown" value for dates/times, but since the situations where we use 0000-00-00 don't currently allow for NULL, it would be more straightforward just to go for the current timestamp. I think we'll just apply this to new installations rather than try to fix something that isn't broken on existing installs.
This will occured on new or old if it's consist of 0000-00-00. On my case, the installation proceed even if the database fail to insert module and page. I fix it by modifying the my.cnf but I think it's also good to fix this problem :)
The problem is you can have only one CURRENT_TIMESTAMP per table. On our pages table, we're already using it for the 'modified' date, so can't apply CURRENT_TIMESTAMP to our default for created date–MySQL won't let us. We'd either have to go null, or choose some arbitrary date/time to make the default, and then let the installer just specify NOW() for all the dates. I'll update our installer SQL files to do this, but again for existing installs I think we've got to leave those alone. I think the only time the issue would turn up is at install time anyway. Luckily it doesn't appear we've got any dependencies on a 0000-00-00 date, so seems like a safe bet.
It's also occured after the installation but that is a good starting point :)
It's interesting that MySQL let you proceed with it anyway. Did the installation succeed despite the error? If so maybe it was more of a warning than a fatal error? Did you continue to see any errors/warnings after installation, like on the front-end or back-end of the site?
I think it's different. It's interesting that processwire allow me to proceed even that database export has some error. Maybe you're right that it's just some warning. Yup it still occured. I will try to submit a PR on this.I will try to inspect further. Will get back to you.
Thanks Christian, I've actually already fixed this locally so don't worry about a PR. I just need to do some more testing with it before I commit to GitHub, but so far so good!
On Fri, Dec 18, 2015 at 6:38 AM, Christian Esperar <[email protected]
wrote:
I think it's different. It's interesting that processwire allow me to proceed even that database export has some error. Maybe you're right that it's just some warning. Yup it still occured. I will try to submit a PR on this.I will try to inspect further. Will get back to you.
— Reply to this email directly or view it on GitHub https://github.com/ryancramerdesign/ProcessWire/issues/1559#issuecomment-165755357 .
We are having this same issue when using the Site Profile Exporter. The latest version still produces created dates in the 0000-00-00 00:00:00 format in the install.sql file. Doing a search & replace with CURRENT_TIMESTAMP fixes it.
