In the installation application, InstallationModelDatabase::populateDatabase will convert automatically utf8mb4 to utf8 if utf8mb4 support is not present. See line 933.

Likewise for JInstaller::parseSQLFiles, see line 954 and 960.

The detection for utf8mb4 cannot be super precise. We take version's claims with a pinch of salt at JDatabaseDriverMysqli::serverClaimsUtf8mb4Support and JDatabaseDriverMysql::serverClaimsUtf8mb4Support. However, if you're using the PDO driver you do not have the required information, therefore JDatabaseDriverPdomysql::__construct can only hope for the best. However, in all of the above cases, if we end up trying to use utf8mb4 and it's rejected at server connection time (connect() method) we revert to plain old utf8.

Now, if a server doesn't barf at connection time but complains at table creation time there's nothing I can do about it except suggest an upgrade to a newer version of MySQL. For what is worth this is exactly what WordPress does regarding utf8mb4 support so we're not exactly the odd sheep in the herd.

Holy inconsistent Joomla! code, Batman! It looks like the Fix button goes through SQL file parsing of its own instead of using any of the other TWO places in Joomla! where we also parse and apply SQL update files. FFS... You can solve by changing JSchemaChangeitem line 243 from

$this->db->setQuery($this->updateQuery);

to

$query = $this->db->convertUtf8mb4QueryToUtf8($this->updateQuery);
$this->db->setQuery($query);

@zero-24 can you please add that to your PR?

@zero-24 can you please add that to your PR?

Thanks @nikosdion done with: ed0e3e7

@zjw can you please try the changes with this patch applyed? If you need help to apply the patch please have a look here: https://joom.la/patchtesting

@zero-24:

After merging jah-tz/joomla-cms@ed0e3e7 --

Extensions->Manage->Database shows:

1 Database Problem Found.

Table 'osvlt_menu' does not have column 'alias' with type 'varchar(191)'. (From file 3.5.0-2015-07-01.sql.)

This error illustrates that you omitted fixing the alias column in #__menu in installation/sql/mysql/joomla.sql, but that the other changes to joomla.sql are correct.

After tapping the Fix button, the error goes away. This illustrates that the patch to JSchemaChangeitem is working correctly.

Thanks i can fix #_menu tomorrow if i'm back to the computer.

I have encountered another issue.

To test:

Install Joomla 3.4.3 (which doesn't have the utf8mb4 patch from #7173) onto a system that doesn't support utf8mb4. (As was done in #8267. In my case it is a CentOS 6.7 system.)

Prepare a full update package for 3.5.0-dev (e.g., Joomla_3.5.0-dev-Development-Update_Package.tar.gz) as patched by this pull request.

Go to Extensions->Extension Manager->Install and choose one of the available methods to install the update (I chose 'Install from Folder').

Results:

Warning

Unknown character set: 'utf8mb4' SQL=-- Convert utf8_bin collated columns to utf8mb4_bin collation ALTER TABLE `osvlt_banners` MODIFY `alias` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';


Error

Error installing file

So the update didn't complete. Checking the status on the server, it doesn't look like any of the 3.5.0 schema updates were applied.

Looking at Extensions->Manage->Database, it shows:

11 Database Problems Found.

    Database schema version (3.4.0-2015-02-26) does not match CMS version (3.5.0-2015-10-30).
    Table 'osvlt_banners' does not have column 'alias' with type 'varchar(191)'. (From file 3.5.0-2015-07-01.sql.)
    Table 'osvlt_categories' does not have column 'alias' with type 'varchar(191)'. (From file 3.5.0-2015-07-01.sql.)
    Table 'osvlt_contact_details' does not have column 'alias' with type 'varchar(191)'. (From file 3.5.0-2015-07-01.sql.)
    Table 'osvlt_content' does not have column 'alias' with type 'varchar(191)'. (From file 3.5.0-2015-07-01.sql.)
    Table 'osvlt_menu' does not have column 'alias' with type 'varchar(191)'. (From file 3.5.0-2015-07-01.sql.)
    Table 'osvlt_newsfeeds' does not have column 'alias' with type 'varchar(191)'. (From file 3.5.0-2015-07-01.sql.)
    Table 'osvlt_tags' does not have column 'alias' with type 'varchar(191)'. (From file 3.5.0-2015-07-01.sql.)
    Table 'osvlt_ucm_content' does not have column 'core_alias' with type 'varchar(191)'. (From file 3.5.0-2015-07-01.sql.)
    Table 'osvlt_contentitem_tag_map' should not have index 'idx_tag'. (From file 3.5.0-2015-10-26.sql.)
    Table 'osvlt_contentitem_tag_map' should not have index 'idx_type'. (From file 3.5.0-2015-10-26.sql.)

Tapping the Fix button makes those 11 items go away. So the Fix button can fix the problems related to table structure. I suspect that any SQL updates unrelated to structure are not fixed by the button. For example, administrator/components/com_admin/sql/updates/mysql/3.5.0-2015-10-13.sql is supposed to add an extension ID 453, but I don't find that ID when looking under Extensions->Plugins.

I suspect the problem arose because I was using the 3.4.3 installer to install the 3.5.0-dev update. The 3.4.3 installer doesn't know anything about the special handling of utf8mb4 SQL files, so it fails when it tries to apply the 3.5.0-dev SQL update from administrator/components/com_admin/sql/updates/mysql/3.5.0-2015-07-01.sql. Conversely, the 3.5.0-dev installer wouldn't have this problem because libraries/cms/installer/installer.php has been patched to handle it.

Perhaps there's a way of modifying the 3.5.0-dev manifest script (administrator/components/com_admin/script.php) so it performs the utf8mb4 conversion if needed. Then the administrator/components/com_admin/sql/updates/mysql/3.5.0-2015-07-01.sql file could be moved somewhere else so it is not applied automatically as part of an update, but is available for use by administrator/components/com_installer/models/database.php (where it is hard-coded in).

@zero I can confirm @zjw 's latest issue. I tried to update a copy of my 3.4.5 production site to 3.5. Beta 1 using an update container patched by the (up to now) latest changes from this PR here, and it happens what zjw describes: The update first fails with the 11 database problems, then fix database works, the new plugins (stats, update notification) have to be installed with the discover method, and possibly other database changes not detected by the database check are not performed.
So as it looks to me updating a pre-3.5 to 3.5 Beta 1 does not work, because like zhw sais, the old installer is performing the installation.
I tried to solve this by patching files administrator/components/com_installer/models/database.php and libraries/cms/schema/changeitem.php of the existing 3.4.5 installation to the state of 3.5 beta 1 + this PR before running the update, but this did not help.

Yes we have still problems here but i'm unable to fix it. I will leave this PR here open for referece if somone is willing to take that over or is able to find the problem please let me know. I would call this issue a release blocker as it will break much sites on updating. Thanks

Ok i finaly broken this PR. So only the code will be there: https://github.com/jah-tz/joomla-cms/tree/c6cb680fbb0e55fab3b4d8e726c9db5dd634c1d7 and I need to close here. Thanks to anyone how is able to fix this issue. @zjw I'm going to reopen your issue now. Sorry for all the mess

Hmm, why not leave the conversion of the tables to be manually done by the site admins as described for example here https://mathiasbynens.be/notes/mysql-utf8mb4 and so get rid of the character set conversion statements in sql scipt 3.5.0-2015-07-01.sql, which fail on mysql versions lower than 5.5 (because utf8mb4 not supported)? The rest of the PR would be ok then I think.
I have meanwhile changed my database from mysql 5.1 to 5.5, but still have the one on 5.1. for further testing this if necessary.

@richard67 this is no option for me (personaly). We have a B/C promise and 3.5.0 or more the PR #7173 break the CMS on old mysql versions (that are supported) as you will get this error back on all joomla updates (as it allway replaces the update SQL file) and the most ouf our users are unable to change the mysql version as it is controled by the host.

OK I've merged the fix to the database update schema. We just need to work out the best fix for the article alias' now and we are good to go

See #8376 for part of the issue