New issue + pull request for it.

Summary of Changes

This PR is only relevant for mysql-related databases.

With Joomla! version 3.5.0 the database driver is able to "downgrade" SQL statements "CREATE TABLE" and "ALTER TABLE" from utf8mb4 to utf8 if necessary (utf8mb4 not supported by db server or db client api) by simple text replacement in the statement before execution.

This feature has been implemented in the database driver itself and in the same way also locally in script.php and installer.php in case if updating from a pre-3.5.0 and still connected with the old database driver.

The implementation has 2 serious bugs:

1. As soon as there is more than 1 space or a tab between the words "CREATE" or "ALTER" and "TABLE", which is still valid SQL, the statement will not be detected to be a "CREATE TABLE" or "ALTER TABLE" statement and so not be processed.

2. If a "CREATE TABLE" or "ALTER TABLE" has been detected, the procedure is to simply replace "utf8mb4" by "utf8" in the statement, regardless if it occurs withint quoted text or name quotes. This makes it impossible to use the pattern "utf8mb4" within table or column names, table or column comments or column default values.

This pull request (PR) corrects these 2 issues.

I noticed these issues when starting to work on the utf8mb4 things, but it took me a while to find a good solution, so this PR comes very likely too late for 3.5.0.

Sorry for that.

Testing Instructions

Overiew, pre-conditions

• All tests have to be performed on a mysql database server / client combination which does not support utf8mb4, so statement downgrade will take place.
• Hint on utf8mb4 support or not: utf8mb4 is not supported if either server version is lower than 5.5.3 or if msqlnd client api is used with version lower than 5.0.9 or if other mysql-related client is used with version lower than 5.5.3.

• Because statement downgrade is implemented at 3 places it needs at least tests 1, 3 and 4 to be executed for "paranoid" testers:

1. Test the database driver (test unpatched, i.e. without this PR, and patched with this PR)
2. Test the database schema manager (only test patched)
3. Test the installer library (only test patched)
4. Test Joomla! Update (script.php) (only test patched)
5. Test Joomla! new installation (only test patched)

Less "paranoid" testers can skip Tests 2, 3 and 4 by executing Test 1 to verify that the changes in libraries/joomla/database/driver.php are working, and then check that the changes done in administrator/components/com_admin/script.php and libraries/cms/installer/installer.php are 100% identical with those in driver.php.

Test 1: Database driver

Download following test sql script and place it in a folder accessible to your Joomla! used for the test, e.g. in the tmp folder:

http://test5.richard-fath.de/test_query_downgrade.sql

Then add following code snippet to the index.php of your frontend template, e.g. Protostar, below the opening of the body div, replacing "FULL_PATH_TO_THE_FOLDER_WITH_THE_TEST_SQL_SCRIPT" by the path where you stored the sql script:

<?php
$db = JFactory::getDbo();
$buffer = file_get_contents('/FULL_PATH_TO_THE_FOLDER_WITH_THE_TEST_SQL_SCRIPT/test_query_downgrade.sql');
$statements = $db->splitSql($buffer);
foreach ($statements as $statement)
{
echo $db->convertUtf8mb4QueryToUtf8($statement) . '<br />';
}
echo '<br />';
?>

This code will read in the sql statements from the test sql script and echo the (if necessary) downgraded statements to the top of your website's pages.

Open the test sql script in an editor or show it on the command line (e.g.in Linux: "more" or "less" command).

Go to your website and verify the statements with those in the test sql script.

The comments in the scql script above the statements tell what is expected to be the result.

Result:

Without this PR, the statements with more than 1 space between "CREATE" or "ALTER" and "TABLE" are not downgraded at all, and in the other statements, every occurrance of "utf8mb4" is replaced by "utf8".

With this PR, all statements are downgraded, but "utf8mb4" in tables or columns names or comments or in default value of text columns are not touched.

In both cases charsets and collations in SQL statements should have been downgraded to utf8.

Test 2: Database schema manager

Download following sql script and place it in the schema update folder "administrator/components/com_admin/sql/updates/mysql" of your Joomla! used for the test:

http://test5.richard-fath.de/3.5.1-2016-03-30.sql

Then login to backend and go to "Extensions -> Manage -> Database".

Result: The database schema manager shows open problems related to the script "3.5.1-2016-03-30.sql".

Use the "Fix" button to fix the problems.

Result:

Without this PR, the update will fail becuase of invalid character set utf8mb4. The reason is that there are 2 spaces between "ALTER" and "TABLE".

Use a text editor to fix that by removing 1 of the 2 spaces, and then reload the "Extensions -> Manage -> Database" page, and then use again the "Fix" button to fix the problems.

Result: Table 'j3ux0_test_schemaupd_utf8mb4' does not exist. (From file 3.5.1-2016-03-30.sql.)

Use again the "Fix" button to fix the problems.

Result: You will not get out of this mess.

With this PR, the 1st use of the "Fix" button will be successful.

Now check with phpMyAdmin the structure of table #__test_schemaupd_utf8mb4 (with this PR) or #__test_schemaupd_utf8 (without this PR) created with the schema update. (Replace #__ by your db prefix.)

Result:

Without this PR there is a new table #_test_schemaupd_utf8 with columns named like "utf8_test...".

Column comments and default values contain only "utf8", not "utf8mb4".

With this PR, a new table #__test_schemaupd_utf8mb4 with columns named like "utf8mb4_test..." is created, and column comments and default values contain "utf8mb4".

In both cases charsets and collation of the table and the 1 binary collated columns are utf8.

To clean up after this test, remove the test schema update sql script you added before, and delete the table created in this test with phpMyAdmin.

Test 3: Installer library

Install following package using "Extensions -> Manage -> Install", "Upload & Install Package":

http://test5.richard-fath.de/test_package_utf8mb4.zip

Open the package locally and open the installation sql script of it in an editor.

Now use phpMyAdmin to compare with what is in your database.

Result:

Without this PR there is a new table #__test_package_utf8 with columns named like "utf8_test...".

Column comments and default values contain only "utf8", not "utf8mb4".

With this PR, a new table #__test_package_utf8mb4 with columns named like "utf8mb4_test..." is created, and column comments and default values contain "utf8mb4".

In both cases charsets and collation of the table and the 1 binary collated columns are utf8.

To clean up after this test, just uninstall the test package.

Test 4: Updating to latest staging + this patch

Update a Joomla! pre-3.5.0 or a 3.5.0 using following custom URL for the Joomla! Update component:

http://test5.richard-fath.de/list_test3.xml.

Verify that the update works without any errors, and that there are no open database problems shown in "Extensions -> Manage -> Database".

Test 5: New installation of latest staging + this patch

Verify that a new installation using the following link as source package still works without any errors:

https://github.com/richard67/joomla-cms/archive/fix-utf8mb4-query-downgrade.zip