Pull Request for Issue #30546 .

Related to Pull Request #31450 .

Summary of Changes

Beginning with version 4, Joomla will stop to use the old fashioned pseudo null dates and support real null values for datetime columns in core database tables.

But some datetime columns shall not allow null values. These are mainly the created and modified dates of core content and the registration date and last visit date of users.

For the created time and the registration date we assumed that valid values are set in database, and for modified dates and last visit time we decided to do it like files systems do and set the modified and last visit dates equal to the created and registration dates. (You may like it or not, that's another discussion).

But on installations with a long update history it can happen that come old core content or some old users don't have a valid created or registration date, i.e. they have an old pseudo null date, i.e. our assumption was wrong.

If such an installation is updated to Joomla 4 and uses a MySQL or MariaDB database, you might get an SQL error or not when editing some content or user with such invalid created or registration dates and trying to save, depending on your database server settings. See issue #30546 for details.

In addition, with any kind of database it may happen that such content or users are sorted wrong when ordering by the created or the registration date, and so you may not find it when sorting by that date. That's why this PR changes stuff for PostgreSQL, too.

This pull request (PR) fixes the issue by updating the critical values to a fix default value 1980-01-01 00:00:00 when updating to Joomla 4.

Therefore it

• adds the necessary update SQL statements to the existing update SQL scripts for updating pre-4 versions so the later update SQL statements for the modified times or last visit times don't fail, and
• adds a new update SQL script for each kind of database which fixes the wrong datetimes on installations already updated to previous Joomla 4 Beta versions.

The PR handles only those database columns where due to the software history of the core the problem really can happen. These are (table.colum):

• #__banners.created
• #__categories.created_time
• #__contact_details.created
• #__content.created
• #__newsfeeds.created
• #__redirect_links.created_date
• #__users.registerDate

The PR does not handle all possible datetime columns where bad null values could make a problem caused by come 3rd party extension not writing data in the right way.

In addition to this PR, another PR for 3.10-dev will be made, which informs the backend user in some way if critical datetime values have been found and helps to identify the affected tables and records, so the user can chose a different value than the default, e.g. the date when the site was created, and fix the bad created dates or registration dates before updating to Joomla 4. Currently I'm working on this with PR #31450 , but I might replace that PR by another, better one, instead of just rebase to 3.10-dev.

Testing Instructions

This pull request (PR) should be tested with different database types (MySQL or MariaDB and PostgreSQL).

Testers please report back which kind of database you have used for testing. If you have both types, test with both. Thanks in advance.

Preparation: Create a Joomla 3.10 installation which contains bad created and registration dates

1. It needs a Joomla 3.10 installation (current 3.10-dev branch or last nightly build) which contains at least one example for each kind of database content handled with this PR.
   The easiest way to get that is to install the 3.10 and at the end of the installation's "Finalisation" step chose to install testing sample data, then after installation enable the "System - Redirect" plugin and create a few redirect links and then create a few users.

2. Set some bad created or registration date for each kind of content by running the following sequence of SQL commands in an SQL client like e.g. phpMyAdmin or phpPgAdmin, depending on your database type.
   But before executing the SQL, replace the #__ by your actual table prefix and the values for id by appropriate values which exist in your database.
   The values in the examples below are suitable for testing sample data except of the user id, this has to be adjusted to the id of the user created in the preparation step.

• For MySQL and MariaDB databases

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
UPDATE `#__banners` SET `created` = '0000-00-00 00:00:00' WHERE `id` = 2;
UPDATE `#__categories` SET `created_time` = '0000-00-00 00:00:00' WHERE `id` = 14;
UPDATE `#__contact_details` SET `created` = '0000-00-00 00:00:00' WHERE `id` = 1;
UPDATE `#__content` SET `created` = '0000-00-00 00:00:00' WHERE `id` = 1;
UPDATE `#__newsfeeds` SET `created` = '0000-00-00 00:00:00' WHERE `id` = 1;
UPDATE `#__redirect_links` SET `created_date` = '0000-00-00 00:00:00' WHERE `id` = 1;
UPDATE `#__users` SET `registerDate` = '0000-00-00 00:00:00' WHERE `id` = 355;

The SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; is needed in order not to get an SQL error when the NO_ZERO_DATE mode is set together with strict mode by default in an SQL session due to server settings.

• For PostgreSQL databases

UPDATE "#__banners" SET "created" = '1970-01-01 00:00:00' WHERE "id" = 2;
UPDATE "#__categories" SET "created_time" = '1970-01-01 00:00:00' WHERE "id" = 14;
UPDATE "#__contact_details" SET "created" = '1970-01-01 00:00:00' WHERE "id" = 1;
UPDATE "#__content" SET "created" = '1970-01-01 00:00:00' WHERE "id" = 1;
UPDATE "#__newsfeeds" SET "created" = '1970-01-01 00:00:00' WHERE "id" = 1;
UPDATE "#__redirect_links" SET "created_date" = '1970-01-01 00:00:00' WHERE "id" = 1;
UPDATE "#__users" SET "registerDate" = '1970-01-01 00:00:00' WHERE "id" = 355;

3. Verify the bad created or registration dates in database by running the following SQL command(s) in an SQL client like e.g. phpMyAdmin or phpPgAdmin, depending on your database type.

• For MySQL and MariaDB databases

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SELECT 'Articles' AS `content_type`, `id` FROM `#__content` WHERE `created` = '0000-00-00 00:00:00'
UNION ALL
SELECT 'Banners' AS `content_type`, `id` FROM `#__banners` WHERE `created` = '0000-00-00 00:00:00'
UNION ALL
SELECT 'Categories' AS `content_type`, `id` FROM `#__categories` WHERE `created_time` = '0000-00-00 00:00:00'
UNION ALL
SELECT 'Contacts' AS `content_type`, `id` FROM `#__contact_details` WHERE `created` = '0000-00-00 00:00:00'
UNION ALL
SELECT 'News Feeds' AS `content_type`, `id` FROM `#__newsfeeds` WHERE `created` = '0000-00-00 00:00:00'
UNION ALL
SELECT 'Redirect Links' AS `content_type`, `id` FROM `#__redirect_links` WHERE `created_date` = '0000-00-00 00:00:00'
UNION ALL
SELECT 'Users' AS `content_type`, `id` FROM `#__users` WHERE `registerDate` = '0000-00-00 00:00:00'
ORDER BY `content_type`, `id`;

• For PostgreSQL databases

SELECT 'Articles' AS "content_type", "id" FROM "#__content" WHERE "created" = '1970-01-01 00:00:00'
UNION ALL
SELECT 'Banners' AS "content_type", "id" FROM "#__banners" WHERE "created" = '1970-01-01 00:00:00'
UNION ALL
SELECT 'Categories' AS "content_type", "id" FROM "#__categories" WHERE "created_time" = '1970-01-01 00:00:00'
UNION ALL
SELECT 'Contacts' AS "content_type", "id" FROM "#__contact_details" WHERE "created" = '1970-01-01 00:00:00'
UNION ALL
SELECT 'News Feeds' AS "content_type", "id" FROM "#__newsfeeds" WHERE "created" = '1970-01-01 00:00:00'
UNION ALL
SELECT 'Redirect Links' AS "content_type", "id" FROM "#__redirect_links" WHERE "created_date" = '1970-01-01 00:00:00'
UNION ALL
SELECT 'Users' AS "content_type", "id" FROM "#__users" WHERE "registerDate" = '1970-01-01 00:00:00'
ORDER BY "content_type", "id";

Result: The records which have been modified in step 2 are found.

4. Make a backup of the complete Joomla installation and the database so you can later restore this 3.10 with bad datetime values for executing test 3 after having updated it to 4.0 with test 2.

Test 1: Update a 3.10 with bad created and registration dates to latest 4.0 nightly build to reproduce the issue

1. Update the 3.10 installation which you have prepared as described above to latest 4.0 nightly build by using the following custom update URL: https://update.joomla.org/core/nightlies/next_major_list.xml.
   Alternatively, if you can't use "Live Update" for some reason, use "Upload & Update" instead with the update package downloaded from here: https://developer.joomla.org/nightlies/Joomla_4.0.0-beta6-dev-Development-Update_Package.zip
2. Verify if there are bad created or registration dates in database by running the SQL command(s) described in step 3 of the preparation step.
   Result: The records with bad creation or registration dates are still found.
3. Verify if there are bad modified dates in database by running the following SQL command(s) in an SQL client like e.g. phpMyAdmin or phpPgAdmin, depending on your database type.

• For MySQL and MariaDB databases

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SELECT 'Articles' AS `content_type`, `id` FROM `#__content` WHERE `modified` = '0000-00-00 00:00:00'
UNION ALL
SELECT 'Banners' AS `content_type`, `id` FROM `#__banners` WHERE `modified` = '0000-00-00 00:00:00'
UNION ALL
SELECT 'Categories' AS `content_type`, `id` FROM `#__categories` WHERE `modified_time` = '0000-00-00 00:00:00'
UNION ALL
SELECT 'Contacts' AS `content_type`, `id` FROM `#__contact_details` WHERE `modified` = '0000-00-00 00:00:00'
UNION ALL
SELECT 'News Feeds' AS `content_type`, `id` FROM `#__newsfeeds` WHERE `modified` = '0000-00-00 00:00:00'
UNION ALL
SELECT 'Redirect Links' AS `content_type`, `id` FROM `#__redirect_links` WHERE `modified_date` = '0000-00-00 00:00:00'
ORDER BY `content_type`, `id`;

• For PostgreSQL databases

SELECT 'Articles' AS "content_type", "id" FROM "#__content" WHERE "modified" = '1970-01-01 00:00:00'
UNION ALL
SELECT 'Banners' AS "content_type", "id" FROM "#__banners" WHERE "modified" = '1970-01-01 00:00:00'
UNION ALL
SELECT 'Categories' AS "content_type", "id" FROM "#__categories" WHERE "modified_time" = '1970-01-01 00:00:00'
UNION ALL
SELECT 'Contacts' AS "content_type", "id" FROM "#__contact_details" WHERE "modified" = '1970-01-01 00:00:00'
UNION ALL
SELECT 'News Feeds' AS "content_type", "id" FROM "#__newsfeeds" WHERE "modified" = '1970-01-01 00:00:00'
UNION ALL
SELECT 'Redirect Links' AS "content_type", "id" FROM "#__redirect_links" WHERE "modified_date" = '1970-01-01 00:00:00'
ORDER BY "content_type", "id";

Result: The same records as in the previous step 2 are found except of the user record.

Test 2: Update the 4.0 nightly from test 1 to a later 4.0 version which includes the fix from this PR

1. Update the 4.0 nightly from the previous test 1 to current 4.0-dev plus the fix from this PR by using the following custom update URL: https://ci.joomla.org/artifacts/joomla/joomla-cms/4.0-dev/31449/downloads/38633/pr_list.xml.
   Alternatively, if you can't use "Live Update" for some reason, use "Upload & Update" instead with the update package downloaded from here: https://ci.joomla.org/artifacts/joomla/joomla-cms/4.0-dev/31449/downloads/38633/Joomla_4.0.0-beta6-dev+pr.31449-Development-Update_Package.zip.
2. Verify if there are bad created or registration dates in database by running the SQL command(s) described in step 3 of the preparation step.
   Result: No records found.
3. Verify if there are bad modified dates in database by running the SQL command(s) described in step 3 of the previous test 1.
   Result: No records found.
4. Verify the registration dates of users and the created and modified dates of other tables handled by this PR.
   Result: Old pseudo null dates have been set to 1980-01-01 00:00:00 (UTC), other previously valid values have not been touched.

Test 3: Update a 3.10 with bad created and registration dates to a 4.0 version which includes the fix from this PR

1. Update the 3.10 installation which you have prepared as described in the preparation step (use the backup) to to current 4.0-dev plus the fix from this PR by using the following custom update URL: https://ci.joomla.org/artifacts/joomla/joomla-cms/4.0-dev/31449/downloads/38633/pr_list.xml.
   Alternatively, if you can't use "Live Update" for some reason, use "Upload & Update" instead with the update package downloaded from here: https://ci.joomla.org/artifacts/joomla/joomla-cms/4.0-dev/31449/downloads/38633/Joomla_4.0.0-beta6-dev+pr.31449-Development-Update_Package.zip.
2. Verify if there are bad created or registration dates in database by running the SQL command(s) described in step 3 of the preparation step.
   Result: No records found.
3. Verify if there are bad modified dates in database by running the SQL command(s) described in step 3 of test 1.
   Result: No records found.
4. Verify the registration dates of users and the created and modified dates of other tables handled by this PR.
   Result: Old pseudo null dates have been set to 1980-01-01 00:00:00 (UTC), other previously valid values have not been touched.

Actual result BEFORE applying this Pull Request

If a Joomla 3.10 installation contains old pseudo null dates for created and modified dates of the core database tables listed below and for the registration time of users, it will still have these values after the update to Joomla 4.

Expected result AFTER applying this Pull Request

If a Joomla 3.10 installation contains old pseudo null dates for created and modified dates of the core database tables listed below and for the registration time of users, these values are changed to 1980-01-01 00:00:00 (UTC) when updating to Joomla 4.

Documentation Changes Required

If the other PR for 3.10-dev will only implement a way to show that there is a problem with datetimes, e.g. a postinstall message or just a flag in the pre-update check, but will not provide display of all information necessary to identify where in particular the problems have been found, it will need some documentation telling the necessary steps (SQL statements), and the implementation of that 3.10-dev PR will have to link to that documentation.