Pull Request for Issue # .

Summary of Changes

This PR fixes all datetime columns of the com_menus component's database table #__menu so there will not be any Invalid value '0000-00-00 00:00:00' for datetime error anymore on MySQL 5.7 or later when strict mode is enabled.

In opposite to PR #26372 , this PR here does not depend on PR #26295 , because this one here does not have anything to change in table #__ucm_content.

Because columns publish_up and publish_down have been added in 4.0, the sql update scripts 4.0.0-2019-03-30.sql which were used for this purpose can also be used for doing the schema changes of this PR.

In addition, the update scripts 4.0.0-2019-09-13.sql are changed because they insert a record into the menu table.

The joomla.sql scripts is changed so the core menu records are inserted excplicitely with null values for the datetime columns, like we do it elsewhere.

Finally, PHP code is changed so that the table supports null values.

Note on updating old 4.0 sql update scripts

Since we are not in Beta phase yet and so don't have to support updates from 4.0-Alpha-x to 4.0-Alpha-y or between nightly builds, we can change the existing 4.0 update scripts (but of course not pre-4.0 scripts). Later when in beta this will not be allowed anymore because we have to support updating from Beta-x to Beta-y (with y > x of course), so now is a good time to fix them.

Furthermore it makes sense to keep the schema updates for the datetime columns of each table together in one script, because on MySQL it might be necessary to combine all changes for a particular table into 1 single ALTER TABLE statement in order to run properly in strict mode (i.e. strict tables enabled). Currently the db checker/fixer can't understand such statements, but it might be necessary to teach that tool soon to do that.

That's why this PR updates the existing sql update script and doesn't create a new one.

Testing Instructions

New Installation

1. Apply the patch on a clean 4.0-dev branch using git or merging manually, or apply it on an installation of current 4.0-dev using patchtester.
2. If used patchtester on an existing installation in step 1, delete file configuration.php and delete all Joomla database tables in PhpMyAdmin or PhpPgAdmin (depending on your database type).
3. Do a new installation, login to backend, confirm the statistics dialog, go to global config and set error reporting to maximum or development in server settings.
4. Play around with menus.
5. After any action, check in your database the relevant datetime/timestamp without timezone columns.

Update from 3.9.12 or staging

1. On a clean staging or 3.9.12, set error reporting to maximum or development in global config server section.
2. Update to 4.0.0-alpha12-dev nightly build plus this PR applied, using the "Upload & Update" tab of the Joomla Update component and the update zip package which you have downloaded from following link before: https://test5.richard-fath.de/Joomla_4.0.0-alpha12-dev-Development-Update_Package_2019-09-29_pr-26435.zip.
3. Watch the PHP error log and the log file of your database server during the update and after.
4. After update, play around with menus.
5. After each activity, check in your database the relevant datetime/timestamp without timezone columns.

Expected result

New Installation

Menus work as well as without this PR. In a MySQL database there are no columns publish_up or publish_down or checked_out_time having value '0000-00-00 00:00:00' in table #__menu, and there is no invalid default value anymore in MySQL >= 5.7 with strict mode on.

Update from 3.9.12 or staging

Update works, no errors in SQL or PHP about workflows. Rest see above for new installation.

Actual result

New Installation

On MySQL same as expected, but the default value of database columns publish_up or publish_down or checked_out_time of table #__menu is invalid in MySQL >= 5.7 with strict mode on, and there might be values '0000-00-00 00:00:00'. On postgreSQL same as expected, but there might be values '1970-01-01 00:00:00' in the datetime columns of the menu table.

Update from 3.9.12 or staging

See new installation.

Documentation Changes Required

Maybe core developer docs and extension developer docs should be updated to encourage them not to use '0000-00-00 00:00:00' on MySQL anymore but use real NULL and not abuse '1970-01-01 00:00:00' on PostgreSQL as a speudo null date anymore and use real NULL values also there.