Pull Request for Issue # .

Summary of Changes

This Pull Request (PR) adapts the utf8mb4 conversion to changes in J4 which have been forgotten to do in the conversion sql scripts.

• Add missing tables for com_csp, mail templates, webauthn and workflows (J4).
• Change tables for com_finder to new structure and change them to unicode collation (J4, see PR #28455 ).

It does not add the missing tables for action logs and the privacy suite to the utf8mb4 conversion script. This will be done with PR #28495 in staging and later be merged up into 4.0-dev with the regular upmerge. @wilsonge Whenever this is done, ping me, because then I have to do a small change in a new PR for 4.0-dev or (if still open then) in PR #28515 .

Testing Instructions

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

Code review should be sufficient:

• Check that each of the two sections "Step 2.2: Convert all tables to utf8mb4 chracter set ..." and "Step 2.4: Set default character set and collation for all tables" in file administrator/components/com_admin/sql/others/mysql/utf8mb4-conversion-02.sql handles all tables created in joomla.sql, i.e. all core tables, in alphabetical order, except of the tables for action logs and com_privacy, for which a PR is made in staging, see summary of changes above.
• Check for every table added by this PR to the sections mentioned above that it doesn't contain any varchar columns which is longer than 191 characters in any key or index, so that it doesn't need any special index handling in the other parts of the utf8mb4 conversion. I.e. the changes in this PR are enough, indexes of tables newly added in J4 already are limited to the first 191 characters and so are already utf8mb4 compatible.
• Check for every table added by this PR to the sections mentioned above that it doesn't contain any columns with binary collation (utf8mb4_bin or utf8_bin, depending on the utf8mb4 support of the database), which would also require additional special treatment at other places.

For a real test it would either need a version of MySQL older than 5.5.3, or it would need to temporarily patch the utf8mb4 support detection in the database driver and make an installation so that utf8 (without mb4) is used, and then revert those driver patches and use the database fixer to do the utf8mb4 conversion.

The utf8mb4 detection of the MySQLi driver can be patched by patching the private function serverClaimsUtf8mb4Support() in that driver so that it always returns false. You can find this function in file libraries/vendor/joomla/database/src/Mysqli/MysqliDriver.php beginning at line 1075.

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.

Expected result

Utf8mb4 conversion handles all core tables except of the tables for action logs and com_privacy, for which a PR is made in staging, see summary of changes above.

Actual result

Utf8mb4 conversion doesn't handle all core tables. Tables for action logs, the privacy suite, com_csp, mail templates, webauthn and workflows are missing.

Additional information

The utf8mb4 conversion is needed for the migration or update of an old 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.

Theoretically it should not be necessary on J4 to have the utf8mb4 conversion, because the databases which fultill the minimum requirements for J4 all support utf8mb4, and updating an older database to such a version has to happen before updating to J4. But in practice we can't make sure that this is fulfilled. The site admin may just have not checked "Extensions -> Manage -> Database" after having updated the database version and before updating to J4, and so not have seen the message about missing utf8 cconversion and so not have used the "Fix" button to run the conversion. So we keep the conversion in J4 and run them like before at the end of an update if necessary, then we can be really sure that AFTER the update to J4 we really are on utf8mb4.

Documentation Changes Required

None.