Pull Request for Issue # .

Summary of Changes

This Pull Request (PR) updates the utf8mb4 conversion by tables which have been added to staging for action logs and the privacy suite but have been forgotten to add to the utf8mb4 conversion SQL scripts.

In addition it fixes a database error shown after a conversion which requires an additional use of the "Fix" button. The reason for this error is the same: It was forgotten to update one of the utf8mb4 conversion SQL scripts to changes in database structure.

The utf8mb4 conversion is needed for the migration or update of an old MySQL database (but with still supported version) which doesn't support utf8mb4 character set to a newer version which supports that. It runs on a Joomla update, if necessary, or when you go to the database checker page (Extensions -> Manage -> Database) and see a message about missing utf8 conversion and use the "Fix" button after having migrated or updated your database server.

Because there might be already installations out in the wild where the conversion has been done after action logs and the privacy suite have been added, we might have databases which are only partly converted, i.e. these new tables have still utf8 (without mb4) character sets.

Therefore this PR adds a 2nd conversion step, which runs the conversion for these tables only and not for all again in such a case.

Testing Instructions

This PR is only relevant for MySQL databases, not for MariaDB or PostgreSQL databases.

But MariaDB can be used for testing, too. In this case you have to patch the MySQLi database driver to simulate an old MySQL version which doesn't support utf8mb4 like described below in the testing instructions.

Please potential testers don't be discouraged by the long testing insctructions. They are detailled and step by step but at the end you will see testing did not require much time and was not much work.

Requirements

You need to have either a MySQL database server with a version older than 5.5.3 (which doesn't support utf8mb4 yet) and another MySQL database server with a newer version or a MariaDB, or if you have only a newer MySQL version than 5.5.3 or only have MariaDB you have to patch the utf8mb4 support detection in the database driver. The testing instructions will tell you when to do that.

For patching the MySQLi driver, edit file libraries/joomla/database/driver/mysqli.php and make function serverClaimsUtf8mb4Support() always return false by adding the code line marked with the green frame in following image:

For the MySQL (PDO) driver it would be more complicated. I recommend to test with the MySQLi driver, that's sufficient.

You should not test with MySQL 8.0.19 or later because you will run into another issue, see PR #28370 , which has nothing to do with this PR here but might be confusing when checking the database checker for errors.

Finally you should have error logging switched on in your PHP settings (log_errors = On; in php.ini or .user.ini) so that errors are logged into a log file (either webserver's log file or PHP error log).

Test 1: Reproduce issue = starting condition for Tests 2 and 3

Scenario: Reproduce the issue which results in a partly unconverted database if the database has been converted from utf8 to utf8mb4 with Joomla 3.9.16 after a migration or server update.

1.1 Prepare the installation of a 3.9.16 by unpacking the full package into an empty folder of your webserver.

1.2 If you don't have a MySQL database server with a version older than 5.5.3, you have to patch now the database driver as descibed above with the requirements. Before, make a backup of the file to be patched so you can later revert that patch.

1.3 Do the installation. If you not patched the database driver because you have an old database server, use a database on that server for the installation.

1.4 Check in your database e.g. with phpMyadmin collations of all Joomla core database tables.
Result: All tables have collations starting with "utf8_".

1.5 Now either revert the patch of the database driver made in step 1.2 or move the database to a new MySQL database server which supports utf8mb4 (5.5.3 or later).

1.6 Export your database (structure and content) e.g. with phpMyAdmin into an SQL file so you can later use it in Test 3, and backup your Joomla folder for the same purpose. Name the backups so that you can distinguish it from the backup made at the end of this step, e.g. something with "unconverted".

1.7 Login to backend and go to "Extensions -> Manage -> Database".

Result: The database check shows one error about missing UTF-8 Multibyte (utf8mb4) conversion.

1.8 Use the "Fix" button.

Result: The database check shows one error for column core_title of table #__ucm_content, see 1st screenshot in section "Actual result" below.

1.9 Use the "Fix" button again.

Result: The database is up to date, no errors are shown.

1.10 Check e.g. with phpMyAdmin collations of all Joomla core database tables.

Result: All tables have collations starting with "utf8mb4_", except of tables for action logs and the privacy suite, which still have collations starting with "utf8_", see 2nd screenshot in section "Actual result" below.

1.11 Export your database (structure and content) e.g. with phpMyAdmin into an SQL file so you can later use it in Test 5, and backup your Joomla folder for the same purpose. Name the backups so that you can distinguish it from the backup made at in step 1.6, e.g. something with "partly-converted".

Test 2: Update partly converted database from Test 1 to patched update package

Scenario: Fix a partly unconverted database due to this issue with updating to a future Joomla 3.9 version which contains the patch of this PR using the Joomla Update component.

2.1 Update the installation from previous Test 1 to a nightly build of today + the patch of this PR applied + debug log added for this test, either using the "Upload&Update" tab or the "Live Update" tab of the Joomla Update component.

• When using "Upload&Update", donwload following patched update package: https://test5.richard-fath.de/Joomla_3.9.17-dev+pr.28495-Development-Update_Package_debug.zip.
• When using "Live Update", change the update channel to "Custom URL" and the minimum stability to "Development" in the Joomla Update component's options, and use following custom URL: https://test5.richard-fath.de/next_patch_list_pr-28495_debug.xml.

2.2 After the update has finished, check e.g. with phpMyAdmin collations of all Joomla core database tables.

Result: All tables have collations starting with "utf8mb4_", see screenshot in section "Expected result" below.

2.3 Go to "Extensions -> Manage -> Database".

Result: The database is up to date, no errors are shown.

2.4 Check in your PHP error logfile (or the webserver log file) for logs like the following:

DEBUG 1: Running utf8mb4-conversion-01.sql
DEBUG 2: Running utf8mb4-conversion-02.sql
DEBUG 3: Running utf8mb4-conversion-03.sql

Result: Only the DEBUG 3: Running utf8mb4-conversion-03.sql can be found, i.e. the other conversion files which have already run before in Test 1 have not been run again.

Test 3: Update unconverted database to patched update package

Scenario: Convert an unconverted database with updating to a future Joomla 3.9 version which contains the patch of this PR using the Joomla Update component.

3.1 Restore to the 3.9.16 with unconverted database using the backups made in step 1.6 of Test 1.

3.2 Repeat all steps of Test 2.

Results:

• All tables have collations starting with "utf8mb4_", see screenshot in section "Expected result" below.
• In "Extensions -> Manage -> Database", the database is up to date, no errors are shown.
• In PHP error log: All 3 DEBUG logs can be found, i.e. all 3 scripts have been run.

Test 4: Convert unconverted database with database fixer

Scenario: Completely convert an unconverted database on a future J 3.9 version which contains the patch of this PR using the "Fix" button in "Extensions -> Manage -> Database", like it would be after a migration or an update of the database server.

4.1 On a clean staging, apply the patch of this PR.

Then either add debug logs like shown in following two screenshots to file administrator/components/com_admin/script.php, or replace it with the patched version which you can download with following zip file and which contains the same debug logs: https://test5.richard-fath.de/script_pr-28495_debug.zip

If you don't have a Git clone, you can alternatively unpack the following patched installation package with debug log for testing into an empty folder on your webserver: https://test5.richard-fath.de/Joomla_3.9.17-dev-Development-Full_Package_2020-04-02_pr-28495_debug.zip

4.2 If you don't have a MySQL database server with a version older than 5.5.3, you have to patch now the database driver as descibed above with the requirements. Before, make a backup of the file to be patched so you can later revert that patch.

4.3 Do the installation. If you not patched the database driver because you have an old database server, use a database on that server for the installation.

4.4 Check in your database e.g. with phpMyadmin collations of all Joomla core database tables.
Result: All tables have collations starting with "utf8_".

4.5 Now either revert the patch of the database driver made in step 4.2 or move the database to a new MySQL database server which supports utf8mb4 (5.5.3 or later).

4.6 Login to backend and go to "Extensions -> Manage -> Database".

Result: The database check shows one error about missing UTF-8 Multibyte (utf8mb4) conversion.

4.7 Use the "Fix" button.

Result: The database is up to date, no errors are shown.

4.8 Check e.g. with phpMyAdmin collations of all Joomla core database tables.

Result: All tables have collations starting with "utf8mb4_", see screenshot in section "Expected result" below.

4.9 Check in your PHP error logfile (or the webserver log file) for logs like the following:

DEBUG 1: Running utf8mb4-conversion-01.sql
DEBUG 2: Running utf8mb4-conversion-02.sql
DEBUG 3: Running utf8mb4-conversion-03.sql

Result: All 3 DEBUG logs can be found, i.e. all 3 scripts have been run.

Test 5: Convert partly unconverted database with database fixer

Scenario: Fix a partly unconverted database using the "Fix" button in "Extensions -> Manage -> Database", like it would be after a migration or an update of the database server with Joomla 3.9.16 after having the patch of this PR applied in some other way instead of using Joomla Update.

5.1 Restore to the 3.9.16 with partly unconverted database using the backups made in step 1.11 of Test 1.

5.2 Apply the patch of this PR. Then add the debug logs or use the modified script.php as described in step 4.1 of Test 4.

5.3 Login to backend and go to "Extensions -> Manage -> Database".

Result: The database check shows one error about missing UTF-8 Multibyte (utf8mb4) conversion.

5.4 Use the "Fix" button.

Result: The database is up to date, no errors are shown.

5.5 Check e.g. with phpMyAdmin collations of all Joomla core database tables.

Result: All tables have collations starting with "utf8mb4_", see screenshot in section "Expected result" below.

5.6 Check in your PHP error logfile (or the webserver log file) for logs like the following:

DEBUG 1: Running utf8mb4-conversion-01.sql
DEBUG 2: Running utf8mb4-conversion-02.sql
DEBUG 3: Running utf8mb4-conversion-03.sql

Result: Only the DEBUG 3: Running utf8mb4-conversion-03.sql can be found, i.e. the other conversion files which have already run before in Test 1 have not been run again.

Expected result

Utf8mb4 conversion handles all core tables. After conversion with the database fixer or updating the CMS, all core tables show collations starting with "utf8mb4_" in phpMyAdmin.

The database error about column core_title of table #__ucm_content is not shown anymore after conversion with the database fixer.

Actual result

When converting a utf8 database to utf8mb4 with current staging with the "Fix" button of the database checker, like it is necessary after a transfer of the database from an old server which doesn't suppoort utf8mb4 to a new server which does support it, or after an update of the old server to a new version, the checker shows after conversion a database error for column core_title of table #__ucm_content.

It needs to use the "Fix" button a 2nd time to finally fix the database.

After that, or when the utf8mb4 conversion has been run with a Joomla update, the tables for action logs and the privacy suite still have collations starting with "utf8_" in database.

Documentation Changes Required

None.