[4.0] Fix update to 4.0-beta1-dev failing with SQL error when updating from 3.9 or 3.10 with testing sample data

[richard67]

Pull Request for Issue #27032 (comment).

See also #27032 (comment).

Summary of Changes

On J 3.9 and 3.10, the column core_checked_out_time of database table #__ucm_content has data type varchar(255) and not datetime like it should be in MySQL databases, see here: https://github.com/joomla/joomla-cms/blob/staging/installation/sql/mysql/joomla.sql#L1883.

On PostgreSQL it already has the right data type timestamp without time zone, see here: https://github.com/joomla/joomla-cms/blob/staging/installation/sql/postgresql/joomla.sql#L1851.

When a current staging or 3.9.x or 3.10 is installed with English testing sampla date, records are added to table #__ucm_content with value '' (empty sting) for column core_checked_out_time on MySQL.

This makes the following SQL statement to convert the database column to datetime data type fail on certain MySQL databases (5.7 and later with default settings, e.g. some strict modes on): https://github.com/joomla/joomla-cms/blob/4.0-dev/administrator/components/com_admin/sql/updates/mysql/4.0.0-2018-08-01.sql#L6.

This PR here adds before the failing statement an update statement which changes an empty string to a sting literal for the (old) MySQL null date, so when converting the column to datetime the string will be converted correctly.

The old null date is then later converted correctly to a real null value here: https://github.com/joomla/joomla-cms/blob/4.0-dev/administrator/components/com_admin/sql/updates/mysql/4.0.0-2019-09-14.sql#L24-L26.

One may say maybe better fix the testing sample data installation, but there might be other sources where empty string values for the core_checked_out_time might come from, and these also would be fixed by this PR here, so it makes sense, and testing sample data might be fixed later independently of this PR.

Testing Instructions

1. Install staging or 3.9.13 or a current nightly build of 3.10 with English testing sample data.

2. In backend, go to Global Configuration, tab "Server", and set error reporting to "Maximum" or "Development".

3. Download the Joomla 4 nightly build update package "Joomla_4.0.0-beta1-dev-Development-Update_Package.zip" from here: https://developer.joomla.org/nightly-builds.html.

4. Update your installation to 4.0-beta1-dev using the Joomla Update Component's "Upload & Update" tab, uploading the zip package downloaded before in step 3.

5. At the end of the update, login to the backend.

Result: See section "Actual result" below.

6. Repeat steps 1 to 5, but in step 3 download following zip package instead of the regular nightly build: https://test5.richard-fath.de/Joomla_4.0.0-beta1-dev-Development-Update_Package_2019-12-08_pr-27228.zip, and in step 4 upload this package instead of the regular one.
   The zip package here is the update package of nightly build of last night plus the change from this PR here applied.

Result: See section "Expected result" below.

Expected result

Update finishs without SQL error.

There might be many untranslated language strings shown, but this is not related to this PR here, it happens also with the regular, i.e. unmodified update package from last nightly build.

Actual result

After the first login to backend after end of the update:

But there is no further hint in PHP error log or the MySQL log file about the source of this error.

There might be many untranslated language strings shown, but this is a separate issue not related to the issue handled by this PR here.

Documentation Changes Required

None.

[Quy]

I have tested this item ✅ successfully on 7be914e

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/27228.}

[alikon]

I have tested this item ✅ successfully on 803a791

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/27228.}

[alikon]

RTC

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/27228.}

[HLeithner]

Thanks

[richard67]

Thanks, too.