Pull Request for Issue #24535 (part).

Summary of Changes

This PR fixes all datetime columns of com_fields tables 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, and the datetime (MySQL) or timestamp without time zone (PostgreSQL) columns will allow NULL values wherever useful/possible.

Following is changed:

• Table #__privacy_requests's columns requested_at and confirm_token_created_at will be treated in the same way as e.g. columns created and modified of the #__banners table in PR #26372 , i.e. they will get no default value.
• The same is done for table #__privacy_consents's column created.
• There are no columns made nullable.

The default value is only used when inserting new records without specifying values for that particular column. Not having a default will enforce to insert new records with values for these columns being provided and throw an SQL error if some of these values is not specified, i.e. such errors will not be hidden anymore.

Old data will be updated as little as possible. The requested_at and created columns will be not touched at all. We can assume that for core components there is no data with values '0000-00-00 00:00:00' on MySQL or '1970-01-01 00:00:00' on PostgreSQL, and data created by 3rd party components should not be modified. The confirm_token_created_at column will be set to the value of the requested_at column only has value '0000-00-00 00:00:00' on MySQL or '1970-01-01 00:00:00' on '1970-01-01 00:00:00'.

Testing Instructions

Testers please report back the database kind (MySQL or PostgreSQL) on which you have tested.

If you have both MySQL and PostgreSQL, please test on both if possible.

Test 1: 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 privacy consents and request.
5. After any action, check in your database the relevant datetime/timestamp without timezone columns.

Result: See section "Expected result" below.

Test 2: Update sql script

1. Install a clean clean 4.0-dev, login to backend, confirm the statistics dialog, go to global config and set error reporting to maximum or development in server settings.
2. Apply the changes from this PR e.g. manually or with patch tester.
3. Open PhpMyAdmin or PhpPgAdmin (depending on your database type), select your database and then go to the SQL command input.
4. On MySQL copy the first line of file installation/sql/mysql/joomla.sql into the SQL command window but don't execute the commands yet:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

This switches off strict mode to the SQL will run on MySQL 5.7 or later.

5. Copy the content of file administrator/components/com_admin/sql/updates/mysql/4.0.0-2019-09-26.sql or administrator/components/com_admin/sql/updates/postgresql/4.0.0-2019-09-26.sql (depending on your database type) into the SQL command window, in case of MySQL below the previously pasted commands, but don't execute the commands yet.
6. Replace #__ by your database prefix in the SQL statements pasted before in the SQL input window.
7. Put the cursor to the beginning of the 1st SQL statement in the SQL input window and now execute all SQL commands.
8. Play around with privacy consents and request.
9. After any action, check in your database the relevant datetime/timestamp without timezone columns.

Result: See section "Expected result" below.

Expected result

New Installation

The Privacy Component works as well as without this PR. In a MySQL database there are no columns of data type datetime having value '0000-00-00 00:00:00' in tables #__privacy_requests and #__privacy_consents, and there is no invalid default value anymore in MySQL >= 5.7 with strict mode on. On PostgreSQL there are no columns of data type timestamp without time zone having value '0000-00-00 00:00:00' in these tables.

Update sql script

The statements are processed without error. The expected result is the same as for a new installation.

Actual result

New Installation

On MySQL same as expected, but the default value of database columns of data type datetime having value '0000-00-00 00:00:00' in tables #__privacy_requests and #__privacy_consents 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 tables #__privacy_requests and #__privacy_consents.

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.