Pull Request for Issue # .

Summary of Changes

With PR #28495 , an additional step for of tables for action logs and the privacy suite has been added to the utf8mb4 conversion, because it had been forgotten to add these tables to the conversion when they were added to the core, and the logic of the conversion was extended to fix incomplete conversions resulting from that. This PR has been merged and released with 3.9.17.

Unfortunately I have made a mistake in that PR.

If a 3.9.17 or later is migrated from a database server without utf8mb4 support to a server with utf8mb4 support, or if the database server is updated so that it supports utf8mb4 in opposite to before the update, the conversion has to run again completely for all steps. But because I've made a mistake in the logic to check conversion status, it runs only the 2nd step, so that only the tables for action logs and the privacy are converted to utf8mb4, but all other core tables remain utf8.

This Pull Request (PR) here corrects this and extends again the logic to check conversion status to fix incomplete conversions on 3.9.17 or later resulting from the mistake.

The possible values of the converted column of table #__utf8_conversion and their meanings currently are:

• 0 = Conversion has never run
• 1 = Conversion to utf8 has been done, but tables for action logs and the privacy suite might be unconverted (*)
• 2 = Conversion to utf8mb4 has been done, but tables for action logs and the privacy suite might be unconverted (*)
• 3 = Conversion to utf8 has been done, including the tables for action logs and the privacy suite (new since 3.9.17)
• 4 = Conversion to utf8mb4 has been done, including the tables for action logs and the privacy suite (new since 3.9.17, and here we have the mistake)

This PR adds a new value 5, so that values 4 and 5 have following meaning now:

• 4 = Conversion of the tables for action logs and the privacy suite to utf8mb4 has been done, but the other tables might be unconverted (*)
• 5 = Conversion to utf8mb4 has been done (new with this PR)

(*) "might be" because this depends on update history and at which version the conversion has been made.

The reason why my mistake in PR #28495 was not found by the testers is because I have forgotten that scenario "update with utf8mb4 conversion from a version which includes the patch of that PR to a later version which also includes that patch" in my testing instructions. So it is my fault that it was not found, and the testers can't be blamed.

Testing Instructions

Testing this PR requires to test several different update scenarios, so the testing instructions appear a bit long ;-) Please don't be discouraged. With help of the detailed step by step instructions and the installation and update packages which I've prepared, the test should not be too time consuming.

Preconditions

1. This PR is only relevant for MySQL and MariaDB databases but not for PostgreSQL.

2. Because most testers don't have versions of MySQL older than 5.5.3 available which don't support utf8mb4 for testing a real database migration or server update scenario with utf8mb4 conversion, I have prepared patched installation packages of the diverse Joomla versions to be used during tests with a patched MySQLi database driver so that missing utf8mb4 support of the database server is simulated. Links to these packages will be provided in the testing instructions below.
   If using these packages, the MySQLi driver has to be used, not the MySQL (PDO).

3. The utf8mb4 conversion currently nowhere leaves any information which of the 3 conversion sql scripts actually has been run and which has been skipped.
   Adding that is out of the skope of this PR.
   To be able to verify during the tests which script has been run, I have prepared patched update packages, extended by some error_log calls to log into the PHP error log file. Links to these packages will be provided in the testing instructions below.
   To be able to see these logs, you have to switch on error loging into a log file in your PHP settings in a php.ini or .user.ini file:
   log_errors = On;
error_log = "/full-path-to-log-folder/php-errors.log";
   Alternatively, if PHP errors are logged already into your webserver's log file, just watch that log file during the tests.

Test 1: New installation with this PR

This test shall make sure that the logic to not require any utf8(mb4) conversion after a new installation is not broken with this PR.

Step 1: Make a new installation of current staging plus the patch of this PR applied. If you don't have a development enviroment, you can use the installation package created for this PR: https://ci.joomla.org/artifacts/joomla/joomla-cms/staging/29117/downloads/32173/Joomla_3.9.19-dev+pr.29117-Development-Full_Package.zip.

Step 2: Login to the backend and go to "Extensions - Manage - Database".

Result: 1 database problem found. Database update version (3.9.19-dev) does not match CMS version (3.9.19-dev+pr.29117). This is normal when using a package automatically built for a PR. If using a development environment with current staging + this PR applied, this error is not shown.

Step 3: Using a tool like e.g. PhpMyAdmin, check collations of all core database tables.

Result: All tables have collations starting with utf8mb4_.

Test 2: Reproduce the error on 3.9.17 or later introduced by PR #28495

This test results in an incomplete utf8mb4 conversion on a 3.9.17 or later, which will be then used in the next test "Test 3" to test that an update to a later version which includes the correction from this PR will fix it. Because 3.9.17 was only available for short time, this test uses 3.9.18 to cover the most likely szenario.

Step 1: Make a new installation of Joomla 3.9.18 on an old database server which does not support utf8mb4 in case if you can do later a migration or an update, otherwise use a database server which supports utf8mb4 and use following installation package patched to simulate missing utf8mb4 support: https://test5.richard-fath.de/Joomla_3.9.18-Stable-Full_Package_no-utf8mb4.zip.

Step 2: Using a tool like e.g. PhpMyAdmin, check collations of all core database tables.

Result: All tables have collations starting with utf8_.

Step 3: Login to backend and do an update to the latest 3.9 nightly build with the Joomla Update Component. You can find the links to the nightlies custom update URL or update packages downloads here: https://developer.joomla.org/nightly-builds.html. When using the zip package, use https://developer.joomla.org/nightlies/Joomla_3.9.19-dev-Development-Update_Package.zip and not the other, smaller packages.

Step 4: After the update has finished, go to "Extensions - Manage - Database".

Result: There are no database errors shown.

Step 5: Using a tool like e.g. PhpMyAdmin, check collations of all core database tables.

Result: All tables with names starting with #__action_log or #__privacy_ have collations starting with utf8mb4_, all other tables still have collations starting with utf8_.

Test 3: Fix the error from "Test 2" by updating to a future version which includes this PR

This test has to be executed immediately after the previous test "Test 2". It shall show that updating to a later version with this PR included will fix the partly converted database caused by my previous mistake.

Step 1: Update the Joomla 3.9.18 with partly converted database from the previous test "Test 2" to current staging plus this PR applied, using the "Upload & Update" tab of the "Joomla Update" component and following patched update package:
https://test5.richard-fath.de/Joomla_3.9.19-dev+pr.29117-Development-Update_Package_with-debug-log.zip.
This patched package contains additonal debug output with error_log to be checked later during this test.

Step 2: After the update has finished, go to "Extensions - Manage - Database".

Result: 1 database problem found. Database update version (3.9.19-dev) does not match CMS version (3.9.19-dev+pr.29117). This is normal when using a package automatically built for a PR.

Step 3: Using a tool like e.g. PhpMyAdmin, check collations of all core database tables.

Result: All tables have collations starting with utf8mb4_.

Step 4: Check your PHP error log or your webserver log for logs like DEBUG: Running utf8mb4-conversion-....sql..

Result: There are only following 2 logs.
DEBUG: Running utf8mb4-conversion-01.sql.
DEBUG: Running utf8mb4-conversion-02.sql.
There is no log DEBUG: Running utf8mb4-conversion-03.sql..

Test 4: Update a partly converted 3.9.16 to a future version which includes this PR

This test shall make sure that the correction from PR #28495 for a partly converted database due to the forgotten tables for action logs and privacy suite still works.

Step 1: Make a new installation of Joomla 3.8.15 on an old database server which does not support utf8mb4 in case if you can do later a migration or an update, otherwise use a database server which supports utf8mb4 and use following installation package patched to simulate missing utf8mb4 support: https://test5.richard-fath.de/Joomla_3.9.15-Stable-Full_Package_no-utf8mb4.zip.

Step 2: Using a tool like e.g. PhpMyAdmin, check collations of all core database tables.

Result: All tables have collations starting with utf8_.

Step 3: Login to backend and do an update to 3.9.16, using the "Upload & Update" tab of the "Joomla Update" component and following package: https://github.com/joomla/joomla-cms/releases/download/3.9.16/Joomla_3.9.16-Stable-Update_Package.zip.
This update will run the utf8mb4 conversion with the error later fixed by PR #28495 .

Step 4: After the update has finished, go to "Extensions - Manage - Database".

Result: In most cases only database errors about int or tinyint columns are shown, as described in and solved by PR #28501 . In some case there is also an error Table '#_ucm_content' has the wrong type or attributes for column 'core_title' with type varchar(400). (From file 3.7.0-2017-01-08.sql.). In this case use the "Fix" button to fix this error. The other errors about int or tinyint columns will remain.

Step 5: Using a tool like e.g. PhpMyAdmin, check collations of all core database tables.

Result: All tables have collations starting with "utf8mb4_", except of tables with names starting with #__action_log or #__privacy_ , which still have collations starting with "utf8_".

Step 6: Update the 3.9.16 with partly converted database to current staging plus this PR applied, using the "Upload & Update" tab of the "Joomla Update" component and following patched update package:
https://test5.richard-fath.de/Joomla_3.9.19-dev+pr.29117-Development-Update_Package_with-debug-log.zip.
This patched package contains additonal debug output with error_log to be checked later during this test.

Step 7: After the update has finished, go to "Extensions - Manage - Database".

Result: 1 database problem found. Database update version (3.9.19-dev) does not match CMS version (3.9.19-dev+pr.29117). This is normal when using a package automatically built for a PR.

Step 8: Using a tool like e.g. PhpMyAdmin, check collations of all core database tables.

Result: All tables have collations starting with utf8mb4_.

Step 9: Check your PHP error log or your webserver log for logs like DEBUG: Running utf8mb4-conversion-....sql..

Result: There is only one log DEBUG: Running utf8mb4-conversion-03.sql..
There are no logs DEBUG: Running utf8mb4-conversion-01.sql. or DEBUG: Running utf8mb4-conversion-02.sql..

Test 5: Update with utf8mb4 conversion on a Joomla version which contains the patch of this PR

This is the scenario which I had forgotten in my PR #28495 , which has introduced the error fixed by this PR here.

Step 1: Make a new installation of current staging or last 3.9 nightly build with this PR applied on an old database server which does not support utf8mb4 in case if you can do later a migration or an update, otherwise use a database server which supports utf8mb4 and use following installation package patched to simulate missing utf8mb4 support: https://test5.richard-fath.de/Joomla_3.9.19-dev+pr.29117-Development-Full_Package_no-utf8mb4.zip.

Step 2: Using a tool like e.g. PhpMyAdmin, check collations of all core database tables.

Result: All tables have collations starting with utf8_.

Step 3: Login to backend and do an update current staging plus this PR applied (i.e. the same version as already installed, but that doesn't matter), using the "Upload & Update" tab of the "Joomla Update" component and following patched update package:
https://test5.richard-fath.de/Joomla_3.9.19-dev+pr.29117-Development-Update_Package_with-debug-log.zip.
This patched package contains additonal debug output with error_log to be checked later during this test.

Step 4: After the update has finished, go to "Extensions - Manage - Database".

Result: 1 database problem found. Database update version (3.9.19-dev) does not match CMS version (3.9.19-dev+pr.29117). This is normal when using a package automatically built for a PR.

Step 5: Using a tool like e.g. PhpMyAdmin, check collations of all core database tables.

Result: All tables have collations starting with utf8mb4_.

Step 6: Check your PHP error log or your webserver log for logs like DEBUG: Running utf8mb4-conversion-....sql..

Result: There are three debug logs DEBUG: Running utf8mb4-conversion-01.sql., DEBUG: Running utf8mb4-conversion-02.sql. and DEBUG: Running utf8mb4-conversion-03.sql., i.e. all 3 conversion sql scripts have been run.

Test 6: Update without utf8mb4 conversion on a Joomla version which contains the patch of this PR

This test shall make sure that no utf8mb4 conversion script is run if we update a current staging with this PR applied which is already utf8mb4.

Step 1: Make a new installation of current staging or last 3.9 nightly build with this PR applied on a database server which does support utf8mb4, using the following installation package: https://test5.richard-fath.de/Joomla_3.9.19-dev+pr.29117-Development-Full_Package.zip.

Step 2: Using a tool like e.g. PhpMyAdmin, check collations of all core database tables.

Result: All tables have collations starting with utf8mb4_.

Step 3: Login to backend and do an update current staging plus this PR applied (i.e. the same version as already installed, but that doesn't matter), using the "Upload & Update" tab of the "Joomla Update" component and following patched update package:
https://test5.richard-fath.de/Joomla_3.9.19-dev+pr.29117-Development-Update_Package_with-debug-log.zip.
This patched package contains additonal debug output with error_log to be checked later during this test.

Step 4: After the update has finished, go to "Extensions - Manage - Database".

Result: 1 database problem found. Database update version (3.9.19-dev) does not match CMS version (3.9.19-dev+pr.29117). This is normal when using a package automatically built for a PR.

Step 5: Using a tool like e.g. PhpMyAdmin, check collations of all core database tables.

Result: All tables still have collations starting with utf8mb4_.

Step 6: Check your PHP error log or your webserver log for logs like DEBUG: Running utf8mb4-conversion-....sql..

Result: There is no such log, none of the conversion SQL scripts has been run.

Test 7: Update a not converted 3.9.18 to current staging plus this PR with utf8mb4 conversion

This test shall make sure that updating with utf8mb4 conversion is not broken by this PR.

Step 1: Make a new installation of Joomla 3.9.18 on an old database server which does not support utf8mb4 in case if you can do later a migration or an update, otherwise use a database server which supports utf8mb4 and use following installation package patched to simulate missing utf8mb4 support: https://test5.richard-fath.de/Joomla_3.9.18-Stable-Full_Package_no-utf8mb4.zip.

Step 2: Using a tool like e.g. PhpMyAdmin, check collations of all core database tables.

Result: All tables have collations starting with utf8_.

Step 3: Login to backend and do an update current staging plus this PR applied (i.e. the same version as already installed, but that doesn't matter), using the "Upload & Update" tab of the "Joomla Update" component and following patched update package:
https://test5.richard-fath.de/Joomla_3.9.19-dev+pr.29117-Development-Update_Package_with-debug-log.zip.
This patched package contains additonal debug output with error_log to be checked later during this test.

Step 4: After the update has finished, go to "Extensions - Manage - Database".

Result: 1 database problem found. Database update version (3.9.19-dev) does not match CMS version (3.9.19-dev+pr.29117). This is normal when using a package automatically built for a PR.

Step 5: Using a tool like e.g. PhpMyAdmin, check collations of all core database tables.

Result: All tables have collations starting with utf8mb4_.

Step 6: Check your PHP error log or your webserver log for logs like DEBUG: Running utf8mb4-conversion-....sql..

Result: There are three debug logs DEBUG: Running utf8mb4-conversion-01.sql., DEBUG: Running utf8mb4-conversion-02.sql. and DEBUG: Running utf8mb4-conversion-03.sql., i.e. all 3 conversion sql scripts have been run.

Test 8: Update a not converted 3.6.5 to current staging plus this PR with utf8mb4 conversion

This test shall make sure that updating with utf8mb4 conversion of the oldest version for which there is a direct update path to current 3.9 is not broken by this PR.

Step 1: Make a new installation of Joomla 3.6.5 on an old database server which does not support utf8mb4 in case if you can do later a migration or an update, otherwise use a database server which supports utf8mb4 and use following installation package patched to simulate missing utf8mb4 support: https://test5.richard-fath.de/Joomla_3.6.5-Stable-Full_Package_no-utf8mb4.zip.

Step 2: Execute steps 2 to end of the previous test "Test 7".

Result: See previous test "Test 7".

Test 9: Update without utf8mb4 conversion of any Joomla version 3.6.5 or later

This shall make sure that updating any Joomla version which can be updated to 3.9 without any utf8mbe conversion is not broken.

Step 1: Make a new installation of any Joomla version between 3.6.5 and current staging or nightly on a database server which does support utf8mb4.

Step 2: Using a tool like e.g. PhpMyAdmin, check collations of all core database tables.

Result: All tables have collations starting with utf8mb4_.

Step 3: Login to backend and do an update current staging plus this PR applied (i.e. the same version as already installed, but that doesn't matter), using the "Upload & Update" tab of the "Joomla Update" component and the update package build for this PR: https://ci.joomla.org/artifacts/joomla/joomla-cms/staging/29117/downloads/32173/Joomla_3.9.19-dev+pr.29117-Development-Update_Package.zip.

Step 4: After the update has finished, go to "Extensions - Manage - Database".

Result: 1 database problem found. Database update version (3.9.19-dev) does not match CMS version (3.9.19-dev+pr.29117). This is normal when using a package automatically built for a PR.

Step 5: Using a tool like e.g. PhpMyAdmin, check collations of all core database tables.

Result: All tables still have collations starting with utf8mb4_.

Expected result

No incomplete utf8mb4 conversion.

Actual result

After utf8mb4 conversion on a Joomla 3.9.17 or later, only tables with names starting with #__action_log or #__privacy_ have been converted to utf8mb4, all other tables still are utf8.

Documentation Changes Required

Maybe it needs to add something about known errors for 3.9.17 amd 3.9.18 telling that an utf8mb4 conversion would result in most of the core tables not being converted and how to fix that (update converted column of table #__utf8_conversion to value 1 and run the database fixer, or update to a later version which includes this PR).