Pull Request for Issue #.

Summary of Changes

Fix utf8mb4 conversion for Joomla 4.

In Joomla 4, the version requirements make sure that one can only do a new installation of J4 if the MySQL or MariaDB database supports utf8mb4.

For updated installations we could assume they have already been converted to utf8mb4, but it can happen that the site admin has forgotten to use "Fix" button in "Extensions -> Manage -> Database" after having migrated or updated his Joomla 3.10 database to a new database server, and so we still have to do the conversion when updating to J4 in that case.

Therefore we can't remove the utf8mb4 conversion completely from J4.

But we can do following:

• Don't create table #__utf8_conversion anymore at new installation and don't initialize it anymore in the installation's database model.
• Drop table #__utf8_conversion if the utf8mb4 conversion has been successfully done.
• Adapt the database schema check to the above changes.
• Remove all SQL query downgrades from utf8mb4 to utf8.
• Remove all index modifications and column enlargements from the conversion sql because we can be sure those have been performed on J3 before.
• Move the remaining conversion sql for core tables to one sql script because we can be sure that any partly converted database has been fully converted or not converted on J3 before, so we don't need the 2 steps for partly converted databases like we needed in 3.9.17 and later.

In addition to what we can do, we have to adapt the utf8mb4 conversion to changes in database schema in J4, otherwise it will fail. These changes are:

• Table #__core_log_searches has been removed in J4 for new installations and so has to be removed from the conversion of core tables. But it still may exist on updated installations. Therefore a new step for optional conversions has to be added, which allows the table not to exist.
• The #_finder_... tables of Smart Search (com_finder) are all restructured with the update SQL script 4.0.0-2018-07-29.sql at update and so will already have utf8mb4 character set, so they have to be removed from the conversion.

This Pull Request (PR) does all of the above.

Testing Instructions

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 a patched installation package of Joomla 3.10-dev to be used during tests with a patched MySQLi database driver so that missing utf8mb4 support of the database server is simulated. Links to this package will be provided in the testing instructions below.
   If using this package, the MySQLi driver has to be used, not the MySQL (PDO).

Test 1: New installation

This test shall show that new installation is not broken by this PR.

Step 1: Apply the patch of this PR to a clean 4.0-dev staging branch or a 4.0-beta1-dev nightly build.

Step 2: Make a new installation into an empty database.

Step 3: Log in to backend and go to "System - Information - Database".

Result: No database error is shown.

Step 4: Check with a tool like e.g. PhpMyAdmin in your database if a table named #__utf8_conversion exists (replace #__ by your database prefix).

Result: The table doesn't exist.

Step 5: Check with the same tool the collation of all core tables.

Result: All database tables have collation utf8mb4_unicode_ci.

Test 2: Update a J4 with this PR applied to a later version

This test has to be executed immediately after the previous test "Test 1".

It shall make sure that this PR doesn't break updating a J4 to a later J4 version.

Step 1: On the J4 from the previous test "Test 1", login to backend and do an update to the update package of this PR with the Joomla Update Component. You can find the links to the custom update URL or update packages downloads for this PR here: https://ci.joomla.org/artifacts/joomla/joomla-cms/4.0-dev/28515/downloads/32191/.

Step 2: After the update has finished, go to "System - Information - Database".

Result: Only one database error is shown about wrong version. This is normal for update packages build for PRs and so can be ignored.

Step 3: Check with a tool like e.g. PhpMyAdmin the collation of all core tables.

Result: All database tables still have collation utf8mb4_unicode_ci.

Test 3: Update with utf8mb4 conversion from 3.10 to 4.0-dev without this PR applied

This test shall show that an update from 3.10 with utf8mb4 conversion is broken without this PR.

Step 1: Make a new installation of Joomla 3.10-dev or recent 3.10 nightly 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.10.0-dev-Development-Full_Package_2020-05-17_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 4.0 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.

Step 4: After the update has finished, go to "System - Information - Database".

Result: There are no database errors shown.

Step 5: Check with a tool like e.g. PhpMyAdmin in your database if a table named #__utf8_conversion exists (replace #__ by your database prefix).

Result: The table exists.

Step 6: Check with the same tool the collation of all core tables.

Result: Some of the database tables have collation utf8mb4_unicode_ci, some still have collations starting with utf8_.

The tables with collation utf8mb4_unicode_ci are

• those which are converted in a 2nd step jn J3, which are the action logs and privacy tables
• all tables of com_finder because they are recreated in J4 with an update SQL script
• all tables for extensions newly added in J4, which are com_csp, mail templates, template overrides, webauthn and workflows.

Test 4: Update with utf8mb4 conversion from 3.10 to 4.0-dev with this PR applied

This test shall show that this PR fixes the error reproduced in the previous test "Test 3", and the in J4 unneccessary utf8mb4 conversion related code and database table are removed with the update if the conversion has been successful.

Step 1: Make a new installation of Joomla 3.10-dev or recent 3.10 nightly 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.10.0-dev-Development-Full_Package_2020-05-17_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 update package of this PR with the Joomla Update Component. You can find the links to the custom update URL or update packages downloads for this PR here: https://ci.joomla.org/artifacts/joomla/joomla-cms/4.0-dev/28515/downloads/32191/.

Step 4: After the update has finished, go to "System - Information - Database".

Result: Only one database error is shown about wrong version. This is normal for update packages build for PRs and so can be ignored.

Step 5: Check with a tool like e.g. PhpMyAdmin in your database if a table named #__utf8_conversion exists (replace #__ by your database prefix).

Result: The table doesn't exist.

Step 6: Check with the same tool the collation of all core tables.

Result: All database tables have collation utf8mb4_unicode_ci.

Test 5: Update without utf8mb4 conversion from 3.10 to 4.0-dev with this PR applied

This test shall show that an update from 3.10 without utf8mb4 conversion is not broken by this PR, and the in J4 unneccessary utf8mb4 conversion related code and database table are removed with the update.

Step 1: Make a new installation of Joomla 3.10-dev or recent 3.10 nightly on an database server which does support utf8mb4. You can find the latest noghtly build here: https://developer.joomla.org/nightly-builds.html.

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

Result: All tables have collations starting with utf8mb4_. Some have general and some have unicode collation.

Step 3: Login to backend and do an update to the update package of this PR with the Joomla Update Component. You can find the links to the custom update URL or update packages downloads for this PR here: https://ci.joomla.org/artifacts/joomla/joomla-cms/4.0-dev/28515/downloads/32191/.

Step 4: After the update has finished, go to "System - Information - Database".

Result: Only one database error is shown about wrong version. This is normal for update packages build for PRs and so can be ignored.

Step 5: Check with a tool like e.g. PhpMyAdmin in your database if a table named #__utf8_conversion exists (replace #__ by your database prefix).

Result: The table doesn't exist.

Step 6: Check with the same tool the collation of all core tables.

Result: All database tables have collation utf8mb4_unicode_ci.

Test 6: Update with utf8mb4 conversion from 3.10 without com_search to 4.0-dev with this PR applied

This test shall show that updating with utf8mb4 conversion also works if com_search has been removed before the update.

Instructions: Execute "Test 4" again, but before doing the update with step 3, do following:

1. In the 3.10, uninstall every extension related to com_search:

• Search component
• Search module
• All Plugins starting with "Search" (but not with "Smart Search").

2. Using a tool like e.g. PhpMyAdmin, delete table #__core_log_searches from database (replace #__ by your database prefix).

Result: Same as for "Test 4".

Last step: Check with a tool like e.g. PhpMyAdmin in your database if a table named #__core_log_searches exists (replace #__ by your database prefix).

Result: The table still doesn't exist.

Test 7: Update without utf8mb4 conversion from 3.10 without com_search to 4.0-dev with this PR applied

This test shall show that updating without utf8mb4 conversion also works if com_search has been removed before the update.

Instructions: Execute "Test 5" again, but before doing the update with step 3, do following:

1. In the 3.10, uninstall every extension related to com_search:

• Search component
• Search module
• All Plugins starting with "Search" (but not with "Smart Search").

2. Using a tool like e.g. PhpMyAdmin, delete table #__core_log_searches from database (replace #__ by your database prefix).

Result: Same as for "Test 5".

Last step: Check with a tool like e.g. PhpMyAdmin in your database if a table named #__core_log_searches exists (replace #__ by your database prefix).

Result: The table still doesn't exist.

Expected result

New installation

• No #__utf8_conversion table is created anymore.
• All database tables have collation utf8mb4_unicode_ci.
• No database error is shown in "System - Information - Database".

Update from 3.10 with or without utf8mb4 conversion

• All database tables have collation utf8mb4_unicode_ci.
• Only a database error related to the version is shown in "System - Information - Database", which is normal for using an update package built for a PR. No error about missing utf8mb4 conversion is shown.
• The #__utf8_conversion table doesn't exist anymore.

Actual result

New installation or update from 3.10 without utf8mb4 conversion

No issues, but there is a lot of useless stuff like the database table #__utf8_conversion or query downgrade which is not needed anymore on J4 where we are sure the database supports utf8mb4.

Update from 3.10 with utf8mb4 conversion

• Some of the database tables have collation utf8mb4_unicode_ci, some still have collations starting with utf8_.
• For the rest see new installation.

Documentation Changes Required

None.