I have tested this item successfully on 3beecb4

Works like a charm!
Updated from 3.4.5 (copy of live site) to 3.5.0 beta 1 with a patched update container on a MySQL with
dbversion: 5.1.73-log
dbcollation: utf8_general_ci
dbconnectioncollation: utf8_general_ci
where it did not work before.

P.S.: When I tested, there were no database problems detected on the database tab after the update had finished, and all 3 new plugins were installed (which also was not the case before and shows that now everything completed).

P.P.S: Just to be on the safer side I just tested if something is broken for MySQL databases which DO support itf8mb4 (MySQL 5.5.46 with collations utf8mb4_general_ci in my case), and all is fine, also there no problems and nothing detected by the database tab.

P.P.P.S: Now I am confused: When I did my test before for the MySQL 5.5. database which supports utf8mb4, I did install the update package from a folder. There were no problems shown on the database tab, but now I noticed why: It seems that the collations where not changed for utf8mb4 in the datavase (I checked the alias column of the menu table, which still was utf8_bin).

But when I install from the same starting point using the Upload package way, then I get problems shown in the database tab, which then can be fixed with the Fix button, then one last problem is shown, which also can be fixed with the button, and then database tab shows all OK. In the database the collation changes have been applied (menu table, alias column has utf8mb4_bin).

So either I made a big mistake when testing or there is a difference between "Upload&Install" and "Install from Folder" methods regarding executing the SQL statements from the SQL files.

@roland-d Do you have any idea? Should I alter my test result? or would it be another issue if I am right and there is this difference?

For ref this download here contains the current fixes by @roland-d So if you have a install with old mysql pleaase try this.

edit: updated the URL.

@zero-24 @roland-d I just have checked the container and found a mismatch between paths in script.php and the container.

In administrator/components/com_admin/script.php line 1593:

$fileName = JPATH_ADMINISTRATOR . "/components/com_admin/sql/ut8mb4/$format/3.5.0-2015-07-01.sql";

In file administrator/components/com_installer/models/database.php, line 273:

$fileName = JPATH_ADMINISTRATOR . "/components/com_admin/sql/utf8mb4/$serverType/3.5.0-2015-07-01.sql";

And in the zip container you linked above: administrator\components\com_admin\sql\uft8mb4

3 places, 3 variants.

I suggest to use the one from administrator/components/com_installer/models/database.php ;-)

@roland-d Sent a PR to your branch with correction of script.php as mentioned in my comment above.

@zero-24 Please pack the container again with the correct folder utf8mb4 as soon as Roland has merged my PR into his repository, see my comment above.

tested this item successfully. No more Message of Failure about UTF8MB4.

I have tested this item unsuccessfully on 3beecb4

I have to alter my test result so this does not get RTC until the wrong path in script.php is corrected (see my comments and PR before).

Thanks i will check it soon

Thanks for testing guys, I have updated the PR with the correct path and removed the pdomysql check because the UTF8MB4 doesn't apply there.

@richard67 I have done the install from folder test and the upload test and all the results are good. What you are seeing I think is cached data, at least that is what happened to me. I do believe the current fixes are good.

@roland-d Am still testing, too. I do not think all is cached data. But the current fixes are good anyway.

@roland-d Just as a reminder: I do both tests for MySQL with and without support for utf8mb4, and with the first one I had the problems. I know this PR here deals with the second one, but it should not break things for the first one, that's why I test both.

@richard67 Correct, I have tested both as well. Will re-run the tests again later. Now I am dealing with the differences in the tables after an update compared to a clean install.

I had also found that 3beecb4 "worked", but only because the typo resulted in it doing nothing. I fix the typo, and I get errors on updating from 3.4.5:

Message

Database query failed (error # 1115): Unknown character set: 'utf8mb4' SQL=-- Convert all tables to UTF-8 Multibyte (utf8mb4) ALTER TABLE `osvlt_assets` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Database query failed (error # 1115): Unknown character set: 'utf8mb4' SQL=ALTER TABLE `osvlt_associations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Database query failed (error # 1115): Unknown character set: 'utf8mb4' SQL=ALTER TABLE `osvlt_banners` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Database query failed (error # 1115): Unknown character set: 'utf8mb4' SQL=ALTER TABLE `osvlt_banner_clients` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
...

Reviewing again the config that I'm testing with -- a CentOS 6 system with:

• PHP 5.4.16
• mysqlnd 5.0.10 - 20111026 - $Id: e707c415db32080b3752b232487a435ee0372157 $, as returned by mysqli_get_client_info().
• mysql-server-5.1.73

I find that support for utf8mb4 was added to mysqlnd here: php/php-src@277c7d4, and the version at that time was mysqlnd 5.0.7-dev. The tags on that commit show that it was first included in php 5.4.0. By the time that php 5.4.0 was released, mysqlnd was bumped up to msyqlnd 5.0.10. I would think that should be considered the first stable version released with utf8mb4 support.

The release notes for mysql 5.5.3 confirm that it was the first release supporting utf8mb4.

So in my case, I'm using a mysql client that supports utf8mb4, but a server that does not. The Joomla code as patched here is only looking at my client version with mysqli_get_client_info() and incorrectly concludes that utf8mb4 is supported. The code should also be checking the server version with version_compare($db->getVersion(), '5.5.3', '>='). For me, getVersion() returns 5.1.73.

This server version check should also be added to serverClaimsUtf8mb4Support() in libraries/joomla/database/driver/mysql.php and in libraries/joomla/database/driver/mysqli.php.

As for pdomysql, you should take a look at __construct() in libraries/joomla/database/driver/pdomysql.php. It tests for utf8mb4 support by attempting to connect.

Here the results of my recent 4 tests, with and without support for utf8mb4 and with upload&install package and install from folder.

Test 1 - MySQL 5.5.46 (supports utf8mb4) - Upload & Install package

Step 1. Instead of installation finished: Internal Server Error

Step 2. Database tab: 4 problems found

Database schema version (3.4.0-2015-02-26) does not match CMS version (3.5.0-2015-11-05).
Database update version (3.4.5) does not match CMS version (3.5.0-beta).
Table 'xxxxx_contentitem_tag_map' should not have index 'idx_tag'. (From file 3.5.0-2015-10-26.sql.)
Table 'xxxxx_contentitem_tag_map' should not have index 'idx_type'. (From file 3.5.0-2015-10-26.sql.)

Other information:

Database schema version (in #__schemas): 3.4.0-2015-02-26.
Update version (in #__extensions): 3.4.5.
Database driver: mysqli.
79 database changes were checked successfully.
148 database changes did not alter table structure and were skipped.

Step 3. Fix => 1 problem found

Table 'xxxxx_user_profiles' does not have column 'profile_value' with type 'TEXT'. (From file 3.3.4-2014-08-03.sql.)

Other information:

Database schema version (in #__schemas): 3.5.0-2015-11-05.
Update version (in #__extensions): 3.5.0-beta.
Database driver: mysqli.
80 database changes were checked successfully.
148 database changes did not alter table structure and were skipped.

Step 4. Fix => Database table structure is up to date.

Other information:

Database schema version (in #__schemas): 3.5.0-2015-11-05.
Update version (in #__extensions): 3.5.0-beta.
Database driver: mysqli.
81 database changes were checked successfully.
148 database changes did not alter table structure and were skipped.

Step 5. Check plugins: 3 new plugins from 3.5 (editor-button menu, statistics and update notification) NOT installed.

Step 6. Check collation in database (menu table, alias column): utf8mb4_bin, lenght 191 => OK.

Test 2 - MySQL 5.5.46 (supports utf8mb4) - Install from folder

Step 1. Message "Installation of the file was successful."

Step 2. Database tab: 1 problem found

Table 'xxxxx_user_profiles' does not have column 'profile_value' with type 'TEXT'. (From file 3.3.4-2014-08-03.sql.)

Other information:

Database schema version (in #__schemas): 3.5.0-2015-11-05.
Update version (in #__extensions): 3.5.0-beta.
Database driver: mysqli.
80 database changes were checked successfully.
148 database changes did not alter table structure and were skipped.

Step 3. Fix => Database table structure is up to date.

Other information:

Database schema version (in #__schemas): 3.5.0-2015-11-05.
Update version (in #__extensions): 3.5.0-beta.
Database driver: mysqli.
81 database changes were checked successfully.
148 database changes did not alter table structure and were skipped.

Step 4. Check plugins: 3 new plugins from 3.5 (editor-button menu, statistics and update notification) installed => OK.

Step 5. Check collation in database (menu table, alias column): utf8mb4_bin, lenght 191 => OK.

Test 3 - MySQL 5.1.73 (does NOT support utf8mb4) - Upload & Install package

Step 1. Like step 1 of test 1

Step 2. Like step 2 of test 1

Step 3. Fix => Database table structure is up to date, i.e. the 1 problem for table xxxxx_user_profiles which required a second fix in test 1 did not appear here.

Other information:

Database schema version (in #__schemas): 3.5.0-2015-11-05.
Update version (in #__extensions): 3.5.0-beta.
Database driver: mysqli.
81 database changes were checked successfully.
148 database changes did not alter table structure and were skipped.

Step 5. Check plugins: 3 new plugins from 3.5 (editor-button menu, statistics and update notification) NOT installed.

Test 4 - MySQL 5.1.73 (does NOT support utf8mb4) - Install from folder

Step 1. Messages

Database query failed (error # 1115): Unknown character set: 'utf8mb4' SQL=-- Convert all tables to UTF-8 Multibyte (utf8mb4) ALTER TABLE xxxxx_assets CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Database query failed (error # 1115): Unknown character set: 'utf8mb4' SQL=ALTER TABLE xxxxx_associations CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Database query failed (error # 1115): Unknown character set: 'utf8mb4' SQL=ALTER TABLE xxxxx_banners CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

... (lots of similar messages for other tables and columns)

Database query failed (error # 1115): Unknown character set: 'utf8mb4' SQL=ALTER TABLE xxxxx_ucm_content MODIFY core_alias varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';

Installation of the file was successful.

Step 2. Database tab: Database table structure is up to date.

Other information:

Database schema version (in #__schemas): 3.5.0-2015-11-05.
Update version (in #__extensions): 3.5.0-beta.
Database driver: mysqli.
81 database changes were checked successfully.
148 database changes did not alter table structure and were skipped.

Step 3. Check plugins: 3 new plugins from 3.5 (editor-button menu, statistics and update notification) installed => OK.

@richard67 @zjw thanks guys for the valuable feedback, I am certain we can fix the issues we are facing here :)

Ok, I have updated the SQL file to update the tables to match a clean J 3.5.0 version. That should be all I think.

As for the version check, I have consulted with a MySQL expert and he said it is version 5.0.9, he also agreed we should check for the server version as you pointed out @zjw

I have added the server check to the script.php file already, to make sure it is OK, if it is I can add it to the other files as well.

As for the PDO driver, the constructor just assumes it supports UTF8, in the connect call it tries to make sure. This would require us to build a complete PDO driver into the script file since we can't load the existing PDO driver. It may be something we can pick up in a later release when the PDO driver is already updated and we can use it as-is.

@richard67 Can you check the server log for what the actual error is on the Internal Server Error?

@roland-d Well, I can try to find something in the server log tomorrow, also will continue with testing then.

The internal server error somehow occured also in past with Upload&Install for packages when those had other errors during installation (like here with the database stuff) and so might be a Joomla! issue independent from the utf8mb4 works.

What I could see up to now is that the commit from a few hours ago with which you corrected the path has made the database problem "Table 'xxxxx_user_profiles' does not have column 'profile_value' with type 'TEXT'. (From file 3.3.4-2014-08-03.sql.)" which I had in the tests with the MySQL 5.5 database with utfmb8 support before, disappear, i.e. this seems to be solved.

Everything else was like with the test before.

I'll test again tomorrow with the last 2 (and maybe further) commits.

There is a new download that contains the last fixes by @roland-d see: this download

@zero-24 And again your container has the SQL script in a folder named uftmb8, not in utf8mb4 like it should be, i.e. f and t exchanged in your folder's name!!!

Did you not read what I wrote above? With your container, the script will not be found at all, and so no errors will occur, and so people will test this PR false with success, like @franz-wohlkoenig maybe did before.

Please pack the container in the right way. On @roland-d 's repository, the folder where the conversion SQL script is located has the right name (utf8mb4).

@roland-d Can you maybe tell @zero-24 what's wrong in his container (see my comment above)? It seems he takes not notice of @ mentions for him here.

Sorry @richard67 I take notice of mentions but i have forgotten to check it yesterday night ;) Issue with the update package is finaly fixed now ;)

@franz-wohlkoenig can you try to test it again with the last package from here ?

@zero-24 Upgrade works, got Message:

Database query failed (error # 1091): Can't DROP 'idx_tag'; check that column/key exists SQL=-- Drop obsolete indexes ALTER TABLE x_contentitem_tag_map DROP INDEX idx_tag;

Database query failed (error # 1091): Can't DROP 'idx_type'; check that column/key exists SQL=ALTER TABLE x_contentitem_tag_map DROP INDEX idx_type;

Installation of the file was successful."

Thanks

@franz-wohlkoenig Did you also check at the end that following new plugins have been installed: "Button - Module", "System - Joomla! Update Notification" and "System - Joomla! Statistics"?

@richard67 No, today have only tested about UTF8MB4. I will look about those 3 Plug-Ins. Are there more i shall look also?

@richard67 All 3 Plug-Ins are installed.

@franz-wohlkoenig No, just look on the updated Joomla! from your previous test if the plugins are there in the plugin list in backend. If they are not installed, note this here and do a "Discover" for extensions which have not been installed but unpacked. In my tests it has happened that depending on the method how the update package for 3.5.0. beta-1 is installed, the plugin installation had not happened, and in some case they even had not been unpacked.

@roland-d As far as my tests with the latest container linked above show up to now, Upload&Install behaves the same as Install from URL, while Install from folder behaves differently regarding what happens when an error occurs in an SQL statement of any update scripts.

The internal server error I could not find in the log files, but I could find a 404 for the URL of the install view in backend. Either the "view=install" parameter of this URL is not right, of there is something with caching of the install view in backend (Cached version might not work with 3.5.0. beta 1, I have to test later with any caching switched off). These issues have nothing to do with the utf8mb4 stuff I think.

What remains is that when I use one of the installation methods "Upload&Install" or "Install from URL" (link to the meanwhile correct package provided by zero-24), I have the internal server error, and then have these database problems to fix:

Database schema version (3.4.0-2015-02-26) does not match CMS version (3.5.0-2015-11-05).
Database update version (3.4.5) does not match CMS version (3.5.0-beta).
Table 'xxxxx_contentitem_tag_map' should not have index 'idx_tag'. (From file 3.5.0-2015-10-26.sql.)
Table 'xxxxx_contentitem_tag_map' should not have index 'idx_type'. (From file 3.5.0-2015-10-26.sql.)

But when installing from folder, I have the normal screen to be shown at the end of a package installation, but with following message:

Database query failed (error # 1091): Can't DROP 'idx_tag'; check that column/key exists SQL=-- Drop obsolete indexes ALTER TABLE `xxxxx_contentitem_tag_map` DROP INDEX `idx_tag`;
Database query failed (error # 1091): Can't DROP 'idx_type'; check that column/key exists SQL=ALTER TABLE `xxxxx_contentitem_tag_map` DROP INDEX `idx_type`;
Installation of the file was successful.

This is the case for both databases with and without utf8mb4 support.

What is also true for both: When installed from folder, the 3 new plugins have been installed correctly at the end.

When installed with "Upload&Install" or "Install from URL" it differs between the databases:

Database without support for utf8mb4: Plugins not installed, discover finds nothing.
Database with support for utf8mb4: Plugins not installed, discover finds them, install button works then.

So it seems there is a slight difference bewteen the installation methods.

@franz-wohlkoenig It seems in your case all went right then, except of the 2 messages which do not any harm. Which database server type and version do you have, just to have it written here somewhere?

@richard67 I always delete updated Joomla and restore (in this case) 3.4.5 to get no worry about different update-Files. So if i get you right i should install Joomla_3.5.0-beta-Beta-Update_Package.zip and look for these 3 Plug-Ins?

@roland-d P.S.: Another reason for the internal server error in my case could be that I have password-protected my test installations on subdomains of my domain with http authentification (or however it is called) and I force https for backend (having a valid certificate for my domain).

@richard67 MySQLi 5.1.73-1, PHP 5.4.28, Apache/2.2.16 (Debian)

@franz-wohlkoenig Yes, but you should install the update package provided by zero-24 above and now here again: http://www.jah-tz.de/downloads/download.php?file=Joomla_3.5.0-beta-Beta-Update_Package_utf8mb4_fix2.zip&folder=media/joomla/

You can use "Install from URL" with this URL.

ok, will update 3.4.5 with Joomla_3.5.0-beta-Beta-Update_Package.zip, on top zero-24's *fix2.zip.

@franz-wohlkoenig No, zero-24's package is the complete update package, no need to do the 3.5.0 update with the bad beta-1 container and then zero_s on top.

Use only zero's container to update from 3.4.5 to 3.5.0 beta-1 in one step.

According to your version number you have a pre-utf8mb4 database which does not support utf8mb4, so you can compare with my test results if necessary.

@franz-wohlkoenig To be more precise: After you changed the Joomla update channel from anything to "Testing" you will see that the Joomla update is available. Ignore this and not use the update function. Instead of this, goto the extension manager's install view, tab "Install from URL", and copy the URL from my link above and start the update.

@richard67 update 3.4.5 with zero's container was my last update where i got the Message

"Database query failed (error # 1091): Can't DROP 'idx_tag'; check that column/key exists SQL=-- Drop obsolete indexes ALTER TABLE x_contentitem_tag_map DROP INDEX idx_tag;

Database query failed (error # 1091): Can't DROP 'idx_type'; check that column/key exists SQL=ALTER TABLE x_contentitem_tag_map DROP INDEX idx_type;

Installation of the file was successful."

and installed Plug-Ins "Button - Module", "System - Joomla! Update Notification" and "System - Joomla! Statistics".

@franz-wohlkoenig Then for you all went ok, besides these 2 messages about database query failures, which in this case are OK because the indexes to be dropped are not existing (because already dropped?) and so they did not cause any problem, only some warning in mysql.

According to @roland-d 's test description at the top you can set your test result to success, the best is to use the issue tracker for that.

But maybe Roland wants other stuff to be checked too, any maybe he will do some further commit, so maybe you should ask him if you should do this or wait with it? I am not sure, it's on you.

@richard67 Thanks for Informations, Roland can say, whats easy for him.

@roland-d The internal server error which occurred in my tests with the installation methods "Upload & Install package file" and "Install from URL" (but not with "Install from Folder") defintely were caused by Joomla caching.

It might need a separate PR for that, with the possible simple solution to advice in the release notes and the release news to switch off caching before updating.

When I switch off caching in global configuration (and also switch off the page cache plugin, which is just to be on the safer side but not the problem I think), the "Install from URL" works for both utf8mb4 capable and non-capable databases.

In both cases the result screen shows following messages:

Database query failed (error # 1091): Can't DROP 'idx_tag'; check that column/key exists SQL=-- Drop obsolete indexes ALTER TABLE `xxxxx_contentitem_tag_map` DROP INDEX `idx_tag`;
Database query failed (error # 1091): Can't DROP 'idx_type'; check that column/key exists SQL=ALTER TABLE `xxxxx_contentitem_tag_map` DROP INDEX `idx_type`;
Installation of the file was successful.

Below the messages, the special message for switching on or off the new statistics plugin is shown.

I will test as next the install from folder method for both kinds of databases to see if there it works the same way or if there still are differences.

@roland-d Same result as before with "Upload & Install package file" and "Install from URL" also for "Install from Folder", regardless if utf8mb4 capable or non-capable database, i.e. in all cases the caching was the additional problem I had, and in all cases the 2 messages about the drop indexes were shown but everything else was OK.

I have not checked yet for consistency between stuctures of new installs and updates, which was the additional thing to be corrected with this PR but not mentioned in the test description at the top, and I do not know if anything else is not done yet from @zjw 's recommendations:

This server version check should also be added to serverClaimsUtf8mb4Support() in libraries/joomla/database/driver/mysql.php and in libraries/joomla/database/driver/mysqli.php.
As for pdomysql, you should take a look at __construct() in libraries/joomla/database/driver/pdomysql.php. It tests for utf8mb4 support by attempting to connect.

And maybe you might want to get rid of the 2 remaining messages maybe (maybe the 2 drop index statements at the end of 3.5.0-2015-07-01.sql are redundant?).

So my question is: Should I wait for any additonal changes by you, Roland, before I check for consistency between new installs and updates and then set my test result to OK?

Finally: Sorry having caused confusion or additional work with my tests having cache switched on ... but the info gathered there might be usedful for a new issue or PR for the caching problem. Before I make such: Do you know anything about known problems with installing or updating extensions in extension manager and Joomla caching? If it is already known or already recommended somewhere to switch it off, it would not need a new issue. Let me know your opinion or if you have some info.

@roland-d Meanwhile I have compared a new installed 3.4.5 updated with the patched 3.5.0 Beta 1 container from link above and a new installed 3.5.0 Beta 1 (with contained patched for this PR).

I compared for databases with and without support for utf8mb4.

Beside the differences in auto increments and so on, which are not relevant here, I have found following structural differences between new install and update for the 2 kinds of databases:

Differences in both kinds of databases:

New installed 3.5.0 Beta 1 with patch from this PR here:

Table #__menu, column `params` text NOT NULL COMMENT 'JSON encoded data for the menu item.',

Table #__sesion, column `data` mediumtext,

New installed 3.4.5 and then updated with 3.5.0 Beta 1 update package with patch from this PR here:

Table #__menu, column `params` text NOT NULL,

Table #__sesion, column `data` mediumtext NOT NULL,

Difference only in the pre-utf8mb4 (i.e. not utf8mb4 capable) database:

New installed 3.5.0 Beta 1 with patch from this PR here:

Table #__menu, column KEY `idx_path` (`path`(191)),

New installed 3.4.5 and then updated with 3.5.0 Beta 1 update package with patch from this PR here:

Table #__menu, column KEY `idx_path` (`path`(255)),

So it seems beside the few remaining small problems mentioned in my previous 2 comments all is ok.

@richard67 That last comment is the best thing I have read this week so far :)

I will take another stab at that later tonight, thanks for your continued and elaborate testing.

I had created a PR addressing some of the problems above: roland-d/joomla-cms#3.

@roland-d @zjw I've reviewed zjw's suggested changes (roland-d/joomla-cms#3) and they look good to me.

I think they could solve even all the remaining problems I have mentioned, not only some of them.

Or am I wrong?

@roland-d cc: @zjw @franz-wohlkoenig I see you have merged zjw's PR and done the pdomysql. Shall we test this now? Or wait for further changes?

@zero-24 Just in case you like to provide again a patched update container I'd like to
1. remind you to check if the right subfolder is used , and
2. ask whether you could also provide a patched container for full installation .

@zjw Thank you for the changes, I am going to test them now and see if anything is left.
@richard67 Please test the changes, so we can see if anything else is left to straighten out.

I am feeling we can finally put the lid on this tonight :P

@roland-d Yes, looks all very good to me. Unfortunately I will be busy for the next 3 hours, and then it will take a while for me to do all my tests, so it maybe be around midnight CET (UTC+1) until you hear from me again.

I've added another commit: roland-d/joomla-cms#4

Still needs doing: Incorporate the client/server version checks into the main db driver files.

Also: if you look at the schema updates in administrator/components/com_admin/sql/utf8mb4/mysql/3.5.0-2015-07-01.sql, you see that the first 5 are not really utf8mb4 conversions. It seems like they should be moved out and placed in a regular schema update file -- they apply regardless of whether utf8mb4 conversion is required. They can be pulled out because regular schema updates are applied first before these in the conversion file.

@richard67 That is fine, it will be good to compare our test results.

@zjw I have merged your other commit as well, it looks good.

Those first 5 queries are actually utf8mb4 related because the length is reduced to 191, so I like to keep them where they are.

So I have done some testing

UTF8MB4 supported DB - Upload

UTF8MB4 supported DB - From folder

The contentitem_tag_map still has 2 indexes that the 3.5.0 clean installation doesn't have:
KEY `idx_tag` (`tag_id`),
KEY `idx_type` (`type_id`),

I wonder why others have problems with the keys not being present. They are in the installation SQL of Joomla 3.4.5.

UTF8MB4 NOT supported DB - Upload

UTF8MB4 NOT supported DB - From folder

The update_sites_extensions table engine is still MyISAM instead of InnoDB

Main DB drivers

Still needs doing: Incorporate the client/server version checks into the main db driver files.

I am going to look at that next.

@richard67 I must have undone too many things :) The typo is fixed again. Thanks.

@zjw Can you please review my changes on the main database drivers? I think all is good to go as it is.

I have no issues anymore with a non-utf8mb4 supported database after doing an update. Database is equal for me.

The only thing I am wondering now, am I the only one who has the indexes still left on the contentitem_tag_map tables on a utf8mb4 database?

@roland-d Just I was preparing patched container for update as well as for new install when your latest commit came. Shall I wait or prepare them again now? I wanna upload them to my server and provide links here but this will take some 30 minutes because I have slow upload link.

@richard67 You can prepare them, I am done with my commits for now.

In the db drivers, you have eliminated the test version_compare($client_version, '5.5.3', '>='). I think that test needs to be kept.

I am also wondering if such version tests should be added to the pdomysql driver. The comments therein describe a "chicken and egg" problem, and the assumption is made that successfully opening the connection implies utf8mb4 support on both the client and server. Is this really known to be true, or just an assumption?

Further regarding my previous comment:

The thing about the administrator/components/com_admin/sql/utf8mb4/mysql/3.5.0-2015-07-01.sql file is that it is not a static one-time update. It is something that will need to be edited and changed with future releases (so in that regard, it shouldn't have a date or a version embedded in its name). The file represents the conversion necessary to go from an up-to-date utf8 schema to an up-to-date utf8mb4 schema. As the schema changes with future releases, so might this file.

For example: a user updates to V3.5.0, and he doesn't have utf8mb4 support. Then V3.5.1 comes out with a change that deletes column data from table #__session. Before the user installs the update, he updates his server so that it supports utf8mb4. When the user applies the V3.5.1 update, his utf8 schema will be modified by deleting the data column. Then the script.php will run and it will notice that his server supports utf8mb4, so it will apply the conversions. But currently, the conversions contain a line that modifies the data column which no longer exists in the current schema, so the conversion will fail. Hence, the file must be edited so that it is always an up-to-date conversion of the schema for each version release.

And further regarding the splitting out of the 5 sql lines out from the file: Doing that makes the file's purpose strictly one of conversion. That being the case, there would no longer be any need to process the file with str_replace('utf8mb4', 'utf8', $query). Instead, apply the conversion if the server supports utf8mb4, and totally skip applying the file if utf8mb4 is not supported.

@roland-d @zjw Regardless of the discussion I started now to upload each one patched container for full installation and update and will provide the links here in another comment below as soon as finished in about 20 minutes.

@roland-d @zjw @zero-24 @franz-wohlkoenig Here you can find patched packages including the latest commit 14b7e21 "Make sure the update_sites_extension table is InnoDB":

Full package: Joomla_3.5.0-beta-Beta-Full_Package_utf8mb4_fix3.zip

Update package: Joomla_3.5.0-beta-Beta-Update_Package_utf8mb4_fix3.zip

Thanks I will close my downloads than so we have only one correct place to download the package. Thanks

@richard67, regarding your last commit: I had noticed that, too. It occurs because of updateDatabaseMysql() in administrator/components/com_admin/script.php. On older servers, MyISM was the default engine, so the script method was changing that table's engine from InnoDB to MyISM. Newer servers have InnoDB as the default. I don't know the purpose for updateDatabaseMysql(), but if the table should always be InnoDB, then that method in script.php should probably be eliminated.

I have tested this item successfully on 14b7e21

@roland-d I have tested now with success that there are no differences in data structure between a new installed 3.5.0 Beta 1 with patch and a new installed 3.4.5 and then updated to 3.5.0 Beta 1 with patch, using the containers I have linked 2 comments above.

I have tested this for both database without utf8mb4 (MySQL 5.1.73) and such with utf8mb4 (MySQL 5.5.46) support.

I have not tested different installation methods anymore because the differences between them disappeared after not having any errors anymore and having switched off Joomla! caching, so I used install from URL for the update from 3.4.5 to the patched 3.5. beta 1 in both cases.

In both cases remaining differences have no relevance or are not related to the utf8mb4 changes.

Both new installation and update on both databases worked without any issues.

Comparison between the 2 database types for each new install and update have shown the expected differences caused by the successfully applied utf8mb4 changes.

Therefore I set my test result to success (until a new commit based on discussion above or whatever changes this).

Hmm somehow the issue tracker was hanging so i pushed the button a second time, now we have my duplicate bla bla here. Sorry for that, guys.

@zjw I guess with your last comment you wanted to "@ mention" @roland-d and not me (because I did not do any commit)?

@richard67 i have just removed the dublicate ;)

By the way: I hope all our past and future efforts for utf8mb4 support have not only the purpose to be able to have symbols like U+1F4A9 PILE OF POO (????) in our data but have a more useful reason

For all who are interested in the utf8mb4 subject I have found an interesting link: How to support full Unicode in MySQL databases · Mathias Bynens with useful explanations and a guide on how to do what we do now, migrate existing databa from MySQL utf8 to MySQL uft8mb4 collations.

But I am not sure if it really would have been necessary for Joomla!

@richard67 it’s more for security matters not for the emoticons…
Here is the reason: https://poststatus.com/the-trojan-emoji/

@dgt41 Oh my god, trojan emojis ... I am too good for this world so I did not expect THIS.

I hope everybody will kick his/her hoster in the ass to provide a modern enough MySQL version.

Users of the German provider 1&1 having installed their Joomla! database(s) a while ago I strongly encourage to create new empty database(s) - new databases have MySQL 5.5.46 there, old ones stay old - and migrate their data to the new database(s) so with Joomla! 3.5 they will benefit from utf8mb4.

Not forget to change the standard collation of the database from utf8 to utf8mb after having created the empty database.

I use 1&1 shared hosting and recently had noticed this and done then as I described and kept the old databases just for testing this PR here.

Yes -- sorry. My previous comment was concerning @roland-d's commit 14b7e21. It didn't seem like the proper fix, because the ENGINE for #__update_sites_extensions is being intentionally manipulated by updateDatabaseMysql(). The question is why.

@roland-d I have not seen any problem with the keys idx_tag (tag_id) and idx_type (type_id) after my tests: All 4 databases' SQL dumps do not contain them.

What remains to be thought through is how in future versions > 3.5.0 updates from diverse older versions shall be handled, as @zjw mentioned a few comments above.

Use cases are that tables or columns relevant for utf8mb4 conversion have been added since 3.5.0, or that relevant tables or columns have been removed.

I think in both cases it should be sufficient only to run the latest conversion script, e.g. 3.5.1-2016-04-01.sql, but not any previous ones, right now only 3.5.0-2015-07-01.sql, or am I wrong?

If I am right and is it that easy we should leave somewhere some information for the maintainers/contributors on what to do in case of data structure changes, i.e. how to maintain the conversion script(s).

Question is: Shall this problem be a show stopper for the this PR? I think no.

I think even if this is not clear yet how to handle future updates and changes, the initial PR introducing the utf8mb4 conversion is already merged, and this PR corrects the errors with it, nothing more, and if it needs again changes for handling the diverse future updates then it should be done with another, new PR or issue, which might also be a show stopper for 3.5.0, i.e. 3.5.0 not being released as long as not clear how it will work in future, but this PR here should not be used for this purpose I think.

We should get this here tested and RTCed and merged and then do the rest if necessary with a new clean starting point, maybe even with the Beta 2 being released in between so we do not have to patch too much to get usable containers for testing.

I have additional suggested fixes here: roland-d/joomla-cms#5.

@zjw Thanks for those fixes, they look good to me.

As for the matter of the update file having a date, I agree your arguments are good. So I have changed the filename to simply mysql.sql and placed it in the utf8mb4 folder. The first 5 queries I have moved to a named file in the regular sql/mysql folder. I have also updated the function to check utf8mb4 support at the start, if it is supported continue, otherwise do nothing.

You are also correct about my commit 14b7e21 not being proper. I completely missed that part of the database update. Your last pull removed that updateDatabase code but I have reinstated it and removed my line. If you have a database not supporting InnoDB, you will have a problem. The reason it didn't work for me is because the default is set to MyISAM. As to why that code is there, I had to go back in the log to 15th October 2011 and find this commit 4a2128b It is there for Unicode support. That will have to do with extension names being in a different language is my guess.

To get to our final question, how are we dealing with future support of these updates. Definitely not a show-stopper for now. I believe this is no different than handling all the other SQL files, we have to be aware of the changes and also update the utf8mbf4 script as well when other scripts gets changed.

My latests changes have been comitted and I think we are there guys :)

I did do an update check again and it's looking good here.

@roland-d @zjw @zero-24 I will be busy for the next hour but then start to provide patched update and full install containers, which may take another hour then until made and uploaded. If you can do it faster you are welcome, just let me know then so I save the work.

RolandD wrote:

You are also correct about my commit 14b7e21
not being proper. I completely missed that part of the database update.
Your last pull removed that updateDatabase code but I have reinstated it
and removed my line. If you have a database not supporting InnoDB, you
will have a problem.

Indeed. If you have a database not supporting InnoDB, then you can't use Joomla, because every table in installation/sql/mysql/joomla.sql explicitly requires it.

The reason it didn't work for me is because the
default is set to MyISAM.

Yes, MyISAM was the default in MySQL up until V5.5.

As to why that code is there, I had to go back
in the log to 15th October 2011 and find this commit 4a2128b

Yes, I already reviewed the history (including that commit) in my commit message.

I recognize that the code in question has nothing to do with utf8mb4, so it doesn't belong in this PR. But since you had already altered the table, I thought it would be good to remove the code as well, because I think the code has been left there by mistake. Honestly, I don't understand why the code was added in the first place because a simple ATLER TABLE #__update_sites_extension ENGINE='DEFAULT' would have done the same thing.

@zjw I am completely following your reasoning and agree with it as well. So let me reinstate your changes and I think we are good to go.

Honestly, I don't understand why the code was added in the first place because a simple ATLER TABLE #__update_sites_extension ENGINE='DEFAULT' would have done the same thing.

I think nobody knew any better :)

@roland-d Does your previous comment for @zjw mean that I should wait with new patched containers until you made another commit to reinstate his changes?

@richard67 Ah yes, let's do that, so we have the latest changes.

This PR has received new commits.

CC: @richard67

@zjw I have removed the obsolete table change but I did add the change to the SQL file with the query you mentioned. Although I wonder if it is needed because the table only has 2 int columns. To be consistent I put it in there.

Thanks everybody for your feedback.

@roland-d Something else coming? Or was the latest commit e7ba512 "Remove obsolete database change" the last one, and I can start to patch and pack?

@richard67 That was the last one unless @zjw comes up with more bright ideas :)

@roland-d Well, my opinion was that the commit history indicated that the current engine type for that table should be InnoDB (not DEFAULT), just as you had set it previously. Type DEFAULT now conflicts with what is in installation/sql/mysql/joomla.sql for that table, as well as what's in one of the administrator/components/com_admin/sql/updates/mysql/*.sql files.

This PR has received new commits.

CC: @richard67

@zjw That was my mistake to leave the 'DEFAULT' in there, I used that to test the query. It's set back to InnoDB now. Thanks.

Okay. I think I'm done :)

Ok, I start to patch and pack containers, will provide new download links soon.

@roland-d @zjw @zero-24 @franz-wohlkoenig and anybody else willing to test this PR

Here you can find patched packages including the latest commit c805e82 "Set correct engine":

Full package: Joomla_3.5.0-beta-Beta-Full_Package_utf8mb4_fix4.zip

Update package: Joomla_3.5.0-beta-Beta-Update_Package_utf8mb4_fix4.zip

Note the changed package name in case you used "Install from URL" for the update tests. The links of the old packages will produce a 410 "gone" now.

tested this item successfully.

I have tested this item unsuccessfully on e7ba512

New install with patched container fix4 works on both kinds of databases with and without utf8mb4 support. Update of a new installed 3.4.5 using the patched update container on database with utf8mb4 support works, too. I have not checked for differences in data stucture yet, because ...

... Update of a new installed 3.4.5 using the patched update container on database without utf8mb4 support ends with internal server error, and this time I did not have Joomla! caching on!!!

Maybe the changes in exception handling cause this now?

To give you an idea where it stopped, i.e. which sql not applied because stopped before, here what the database tab sais:

Warning: Database is not up to date! 6 Database Problems Found.
Database schema version (3.4.0-2015-02-26) does not match CMS version (3.5.0-2015-11-05).
Database update version (3.4.5) does not match CMS version (3.5.0-beta).
Table 'abcde_session' does not have column 'session_id' with type 'varchar(191)'. (From file 3.5.0-2015-07-01.sql.)
Table 'abcde_user_keys' does not have column 'series' with type 'varchar(191)'. (From file 3.5.0-2015-07-01.sql.)
Table 'abcde_contentitem_tag_map' should not have index 'idx_tag'. (From file 3.5.0-2015-10-26.sql.)
Table 'abcde_contentitem_tag_map' should not have index 'idx_type'. (From file 3.5.0-2015-10-26.sql.)

After Fix => Database is ok, reported number of executed statements and so on is then equal to those tests where no error occurred.

But the 3 new plugins are not installed, i.e. the update installation stopped before installing them.

P.S.: Used update method was "Install from URL", which in my tests at the beginning always behaved the same as "Upload & Install package". I have not tested "Install from Folder" yet.

P.P.S.: Regarding the not installed new plugins: "Discover" did not find them, too.

Ah, I see it is related to moving some statements back to "sql\updates\mysql\3.5.0-2015-07-01.sql". The changes of column lengths from 255 to 191 should be only done if utf8mb4 is supported, so this script should only contain the "ALTER TABLE #__update_sites_extensions ENGINE=InnoDB;" statement, the others should go back to "sql\utf8mb4\mysql.sql" from my point of view.

Meanwhile I tested install from folder for the update method with the non-utf8mb4 database, and it was successful. But the comparison to the new install for the same kind of database shows that in the new install many columns have length 191, while in the update they have 255. It starts with table banners, column alias. So maybe I was wrong with my comment before, and we need to keep these statements in the "sql\utf8mb4\mysql.sql" and also add more of them for changing column lengths of other tables from 255 to 191, too?

@roland-d @zjw Please check my test results and succeeeding comments, especially the last one, discuss if necessary and let me know if there will be some further commits and when I can again pack patched containers then.

@richard67 I have tested a clean Joomla 3.4.5 site update with your Joomla_3.5.0-beta-Beta-Update_Package_utf8mb4_fix4 on a non-utf8mb4 supported database (MySQL 5.1) and the installation complets without problem. To be sure it wasn't a fluke, I did it four times :)

I still wonder what is the real error behind the Internal Server Error. The error just indicates an error happened but won't tell you what. That is usually written to the server error log that the hosting provider can see.

@zjw and @franz-wohlkoenig What happens in your case of the update of 3.4.5 to 3.5.0 on a non-utf8mb4 supported database?

@richard67 wrote:

The changes of column lengths from 255 to 191 should be only done if utf8mb4 is supported

I don't think that is necessary, these queries work as well on a non-utf8mb4 supported database because it only shortens the length of the changed field.

Without the real error behind that internal server error, it is more a stab in the dark. It may be an invalid query but which one is the question in that case.

@roland-d Which installation method did you test on MySQL 5.1, Install&Upload package file, Install from folder (where the zip was unpacked first) or Install from URL? And did you check differences betwen the new install and the upgrade from 3.4.5 on the same database type? I could see that in my case, after the latest test with Install from Folder method, which succeeded, in the sql dump from the update columns still had length 255 which have length 191 in all other cases.

@roland-d Maybe the difference I have now comes from the last change in script.php?
Before the change, the sql script also was executed if the server did not support utf8mb4, but with replacement as follows:

if (!$utf8mb4IsSupported)
{
    $query = str_replace('utf8mb4', 'utf8', $query);
}

I agree that "Internal Server Error" could be anything, including things unrelated to Joomla, such as hitting a max. runtime limit. The server log should help identify the problem.

As for the 191/255 differences: I see that. I can prepare a fix.

@roland-d as i know by @richard67 ("According to your version number you have a pre-utf8mb4 database which does not support utf8mb4") i use this kind of db; in case of the update of 3.4.5 to 3.5.0 by "Install from URL" from http://test5.richard-fath.de/Joomla_3.5.0-beta-Beta-Update_Package_utf8mb4_fix4.zip i got an sucessfull update without any warning or Error-Message. The 3 Plug-Ins "Button - Module", "System - Joomla! Update Notification" and "System - Joomla! Statistics" are also sucessfull installed.

In my Position all looks good.

@zjw Unfortunately I do not have access to the apache log, it is shared hosting environment.

@richard67 I have used the Upload package file part. You are right, the column length isn't changed here either. I will wait for the fix being prepared by @zjw

Would it be possible to ask your host why you get an internal server error? They should have access to the log.

@roland-d I'd like to avoid asking them because it is a cheap shared hosting package. Their FAQ refer to PHP parameters like max. script execution time or max. memory being exhausted in most cases. I would prefer to wait for the fix by @zjw and then prepare container and test again and then see what happens.

@richard67 Fair enough, it seems it is host related so I don't think it is a major problem. Indeed let's see what happens with the fix.

@roland-d Just let me know when I can pack and upload again patched containers.

@richard67 see 1and1 apache logs location

@wojsmol Yes, these I know, but they don't show me the 500 internal server errors and the reason for these. Any other logs than those in the log folder I do not have access to.

@roland-d: See what you think of roland-d/joomla-cms#6.

I've removed the comment about not renaming it because that was primarily an issue with the hard-coded file name.

In fact, I don't know that it should matter if the file is renamed. Maybe giving it a current date-name would assure that anyone using the 3.5.0-beta who updates to this would have a current schema.

This has been tested on servers with and without utf8mb4. Comparisons of schema after updating vs. clean installs show no differences for me.

@roland-d @ziw's changes look goot to me. Let me know when I shall provide again patched containers.

This PR has received new commits.

CC: @richard67

@zjw Changes look good, thank you.
@richard67 The new containers can be created. Thank you.

@roland-d @zjw @zero-24 @franz-wohlkoenig and anybody else willing to test this PR

Here you can find patched packages including the latest commit 4cf6347 "Merge pull request #6 from zjw/pr4872test":

Full package: Joomla_3.5.0-beta-Beta-Full_Package_utf8mb4_fix5.zip

Update package: Joomla_3.5.0-beta-Beta-Update_Package_utf8mb4_fix5.zip

Note the changed package name in case you used "Install from URL" for the update tests. The links of the old packages will produce a 410 "gone" now.

Hmm, now updating on both kinds of databases (utf8mb4 non-capable and capable) fails here. I wait for your test result before I continue tomorrow, today I think I am too tired now.

tested this item successfully.

I have not tested this item.

@roland-d @zjw and maybe also @zero-24

Good news: I have hope that I could find the reaosn for my problems with the internal server errors, but I am not 100% sure yet. At least the install from folder method worked now for updating bith kinds of databases, and comparing with the corresponding new install does not show any relevant differences in data structures (only at 1 place ordering of index definition was different but indexes were the same.

When I compared both updates and new installs for the same database kind, in both cases only differecnes like "utf8" - "utf8mb4" were shown for data structures, so everything else is the same. This means for both utf8mb4 and utf8 (i.e. non-utf8mb4) database collations we have at the end the same lengths of database string measured in bytes, i.e. different in characters, i.e. we give away characters in string lengths for the non-utf8mb4 databases. Depending on how it works in Joomla (characters or bytes counted for limits e.g. of fields) this might cause a data loss for old installations even if not being migrated. On the other hand, having database generation with different lengths in bytes (191 or 255) would maybe not be possible to handle in the right way.

So I'd like this to be discussed among us here.

Meanwhile I will test the update method via URL to see if I really found my problem (I'll let you now what it was then).

I will alter my test result to success when I see problems have gone and when you both agree that like it is now is what we want, i.e. having same lengths in bytes for the database strings and so different lenghts in characters for utf8mb4 and utf8 (i.e. no mb4) collations.

I have tested this item unsuccessfully on e7ba512

I have not found the reason for the internal server errors. They seem to occur is something goes wrong with the update installation when I install via URL. The install from folder worked as I wrote before.

But when installing the update via URL, I get following for update from clean 3.4.5 to patched 3.5.0 Beta 1:

Update on MySQL 5.5.46

Internal server error. Then goto backend again, goto database tab, check: 14 problems => Fix => OK, message:

Database query failed (error # 1071): Specified key was too long; max key length is 767 bytes SQL=ALTER TABLE `#__redirect_links` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Discover finds only the Button - Module plugin, install works, but other plugins not found and not installed.

Update on MySQL 5.1.73

same as above except the message on SQL error # 1071 is not shown.

Summary

It seems they key of the redirect links table (is the old URL as far as I remember) is too long for the particular type of column.

@roland-d @zhw Can you check this?

I have not tested this item.

@roland-d @zjw and maybe also @zero-24 hmm I did read again stuff about itf8mb4 conversion and think meanwhile I was wrong with my comment regarding text lengths when I changed test result to "not tested". Just forget about it.

Another thing is that the install from folder worked well, only install from URL not, and the database query failure shown could be just caused by having already been executed before when running Fix button.

So maybe this PR really is OK and the problems I had were server-related.

I am not sure if I can say now I have successfully tested, but at least I should change test result to not tested so I do not block RTC.

I have tested this item successfully on e7ba512

A full installation and an update of 3.4.5 to 3.5.0 went without a problem. The database looks clean, all plugins are installed.

@richard67 With your latest packages I have no issues, all installs and updates good. I also tried the install from URL and that worked fine as well for me.

Finally I would like to ask @zjw and @zero-24 to do their tests one more time and hopefully all is good.

I have tested this item successfully on e7ba512

Finally I have found the reason for my internal server errors: It is related to 1&1's shared hosting, the umasks (0072) and default modes for this (705 for directories and 604 for files), which makes sense for shared hosting if the other shared host users belong to the same group. But because they have different accounts for the ftp user (whom files belong to normally) and the web server user, it seems to make problems.

I could pass this by by enabling Joomla!'s FTP layer, so now install from url works fine, and all comparisons between the sql files exported from databases of the different tests are OK.

I remember that a while ago 1&1 had done some change on the umask stuff, and there occasional problems with installing extensions had started for me.

I am not sure if there is a problem in Joomla! so it does not work on 1&1 shared hosting without the FTP level, but for sure this would not be related to this PR here.

So finally I can test this with success.

Sorry @roland-d i have just discoverd two issues with the pdosql driver ;)

This PR has received new commits.

CC: @franz-wohlkoenig, @richard67, @roland-d

@roland-d Shall we test again? Or shall we wait for other changes?

@richard67 do you have PDO Support on you host? I think this should be tested :)

@roland-d @zjw @zero-24 @franz-wohlkoenig and anybody else willing to test this PR

Here you can find patched packages including the latest change for the PDO driver.

Full package: Joomla_3.5.0-beta-Beta-Full_Package_utf8mb4_fix6.zip

Update package: Joomla_3.5.0-beta-Beta-Update_Package_utf8mb4_fix6.zip

Note the changed package name in case you used "Install from URL" for the update tests. The links of the old packages will produce a 410 "gone" now.

tested this item successfully.

i got an sucessfull update ("Install from URL") without any warning or Error-Message. The 3 Plug-Ins "Button - Module", "System - Joomla! Update Notification" and "System - Joomla! Statistics" are also sucessfull installed.

I have tested this item unsuccessfully on e7ba512

I did not redo all my 4 tests (update and nww install on both kinds of databases) again with the mysqli driver as I did before, and then again with the PDO driver. 8 tests ... uffff.

All installations and updates succeed, i.e. there are no errors and all 3 new plugins are installed, but:

When I compared with previous correct results, I have seen that with the PDO driver all is correct, but with for the mysqli driver for both kinds of databases, the conversion to utf8mb4 has not taken place, i.e. the databases updated from 3.4.5 to patched 3.5.0 beta 1 using the mysqli driver still have utf8 collations (without mb4) and old text length (e.g. 255).

@franz-wohlkoenig As we clarified above, your database does not support utf8mb4, so the update tests which failed for me because utf8mb4 conversions were not applied are not relevant for you, so your test is successful. But just for info: Which kind of database driver did you use, and if not PDO, can you test also with PDO?

P.S. to the failed test result

In case of the MySQL 5.1.73 (= non-utf8mb4), following notice is permamently shown at the top of the page (error reporting was maximum for all my tests):

Notice: Undefined property: JDatabaseDriverPdomysql::$connection in /path_to_joomla/libraries/joomla/database/driver/pdo.php on line 118

P.P.S.: The notice mentioned in my comment above of course was only shown when testing with the PDO driver.

@richard67 i don't know, which Database-Driver i use or how to test PDO (after searching i learned it means PHP Data Objects). In Future i will set Error-Reporting to Maximum.

@franz-wohlkoenig you adjust which database dfriver you use when doing a new install (on the second tab). Because I have MySQL, I have the choice between the MySQL, the MySQLi and the PdoMySQL driver. I am not sure if there are only shown available options, but I asume so because I am not offered e.g. MS-SQL or so.

Later you can change the database driver also in the global settings of the admin site.

@richard67 Thanks for info about "Database Typ" = Driver.

I installed from URL without Warning about utf8mb4, but

• no Info (blue Background) about how to use Joomla! Statistics (Info with 3 Buttons on Bottom).

In Error-Reporting (Maximum) got

• Notice: Undefined property: JDatabaseDriverPdomysql::$connection in xx/Joomla/libraries/joomla/database/driver/pdo.php on line 118

Plug-Ins

1. "Button - Module",
2. "System - Joomla! Update Notification" and
3. "System - Joomla! Statistics"

are sucessfull installed.

@franz-wohlkoenig Could you check if in your database the column 'alias' of table '#__banners' has a lenght of 191 (= OK) or of 255 (= not OK)?

If not OK, you should set your test result to "failed", and if OK, it is on you how you judge the notice about the undefined property in pdo.php, but from my point of view it is an error even if the result of the update seems to be OK.

@franz-wohlkoenig P.S.: Could you repeat the test also with the MySQLi driver (which is the default), and check database column as mentioned in my previous comment?

In my tests, the results with the PDO driver were OK except of the notice, but for the MySQLi driver I had the old column length (255), which is not OK, and in case of database with utf8mb4 support (not your case) also the collations were the old ones (no mb4).

If you have the same, you should set your test result to not successful.

@zero-24 @roland-d Reading the code I have no idea why after this small and obviously correct change for the "pdomysql' case, the utf8mb4 column conversions are not applied when using the MySQLi driver. Maybe the previous syntax error caused something to be skipped for the MySQLi driver which now is not skipped but has another error which we have not found yet?

@richard67 PDO-Driver: column 'alias' of table '#__banners' has a lenght of 191 after update. I will check now MySQLi driver.

@richard67 MySQLi-Driver: column 'alias' of table '#__banners' has a lenght of 191 after update.

@franz-wohlkoenig Then for you it seems everything worked with the MySQLi driver. But there is still the issue with the notice on undefined property in case of PDO driver.

This PR has received new commits.

CC: @franz-wohlkoenig, @richard67, @roland-d

@richard67

But there is still the issue with the notice on undefined property in case of PDO driver.
Notice: Undefined property: JDatabaseDriverPdomysql::$connection in /path_to_joomla/libraries/joomla/database/driver/pdo.php on line 118

I have no idea how you get that. The file pdo.php is not changed by this PR nor do I see any issues with that line because it says $this->connection and the connection variable is defined at the top of the file.

The last commit doesn't need a retest, it is simply updating this PR to be in line with the staging branch. I will also do one more PDO test and see what gives.

@roland-d Please read my last test result and comments afterwards: I had problems with the MySQLi driver, too: The database changes for utf8mb4 (collations) and changes on text lengths (e.g. 255 to 191) were not applied at update installations. Did you check MySQLi again, too? If not, please if possible test also this and not PDO only.

This PR has received new commits.

CC: @franz-wohlkoenig, @richard67, @roland-d

@richard67 I will test MySQLi again as well. Just done the PDO and all looks good now. I was able to get the undefined notice you get as well, this I have fixed now. You will need need packages to test that fix.

@richard67 I have tested MySQLi again as well, the alias column in banners has a length of 191 here. So looks good to me.

Once you have new packages, I do want to test the PDO once more to be sure the notice is gone.

@roland-d @zjw @zero-24 @franz-wohlkoenig and anybody else willing to test this PR

Here you can find patched packages including the latest change of pdomysql.php and from the merge with latest staging the change in script.php.

Full package: Joomla_3.5.0-beta-Beta-Full_Package_utf8mb4_fix7.zip

Update package: Joomla_3.5.0-beta-Beta-Update_Package_utf8mb4_fix7.zip

Other changes coming from update of your (Roland) branch to latest staging I did not include in the new containers.

Note the changed package name in case you used "Install from URL" for the update tests. The links of the old packages will produce a 410 "gone" now.

tested this item successfully.

i update by "Install from URL" using PDO-Driver without Warning or Error-Message; blue Message about using Joomla! Statistics is shown.

The 3 Plug-Ins "Button - Module", "System - Joomla! Update Notification" and "System - Joomla! Statistics" are sucessfull installed.

I have tested this item successfully on e7ba512

Tested all with success.

My final tests are good as well. I believe we have made it :)

Thanks everybody involved for your dedication into getting this resolved. Much appreciated.

When using the pdomysql driver, I'm still seeing (even as of a14edea):

Notice: Undefined property: JDatabaseDriverPdomysql::$connection in /var/www/html/libraries/joomla/database/driver/pdo.php on line 118

To me, the problem looks like it has to do with libraries/joomla/database/driver/pdo.php, line 328. It should not use unset(). All it needs to do is assign null, per http://php.net/manual/en/pdo.connections.php.

Except for that, everything tests fine.

This PR has received new commits.

CC: @franz-wohlkoenig, @richard67, @roland-d

@zjw Good point, I have updated the code. Can you check if it works for you?

@roland-d @zjw @zero-24 @franz-wohlkoenig and anybody else willing to test this PR

Here you can find patched packages including the latest commit 39bd052 "Rather set the connection to null instead of unsetting it".

Full package: Joomla_3.5.0-beta-Beta-Full_Package_utf8mb4_fix8.zip

Update package: Joomla_3.5.0-beta-Beta-Update_Package_utf8mb4_fix8.zip

Note the changed package name in case you used "Install from URL" for the update tests. The links of the old packages will produce a 410 "gone" now.

I have tested this item successfully on e7ba512

Here everything is still ok after the latest commit.

I no longer observe the notice with a clean install of 39bd052 using the pdomysql driver, so I consider it fixed.

FYI, I can trigger the notice when using the master branch as well, so it is not anything new with this PR.

It can be triggered by using these settings:

System->Global Settings->System->Debug System = yes
System->Global Settings->Server->Error reporting = debug.

Go to Extensions->Manage->Database: the notice appears on the left.

The debug plug-in explicitly closes the database connection, which resulted in calling unset(), which resulted in the notice when the connection was reopened.

@zjw Could you set your test result to success in the issue tracker? Just go to This PR in the issue tracker and use the button "Test this" on the top left side.

A few days ago, @richard67 wrote:

Depending on how it works in Joomla (characters or bytes counted for limits
e.g. of fields) this might cause a data loss for old installations even if not
being migrated. On the other hand, having database generation with different
lengths in bytes (191 or 255) would maybe not be possible to handle in the
right way.

So I'd like this to be discussed among us here.

Yes, there are instances where columns are being downsized from (usually) varchar(255) to varchar(191), and there is the possibility of data loss in those instances. The relevant changes occur in administrator/components/com_admin/sql/updates/mysql/3.5.0-2015-07-01.sql (and some of them are repeated in administrator/components/com_admin/sql/utf8mb4/mysql.sql because of the utf8mb4_bin conversions). Here are my thoughts on those changes.

ALTER TABLE `#__banners` MODIFY `alias` varchar(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE `#__contact_details` MODIFY `alias` varchar(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE `#__content` MODIFY `alias` varchar(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE `#__newsfeeds` MODIFY `alias` varchar(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';

alias in these instances is not used as part of a key. Because these columns are not part of any key, there is no reason that they need to be resized. I recommend that they be left unaltered, thus avoiding the data loss issue completely.

ALTER TABLE `#__categories` MODIFY `alias` varchar(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE `#__tags` MODIFY `alias` varchar(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE `#__ucm_content` MODIFY `core_alias` varchar(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';

alias is a key in these instances, but it is not unique. #__ucm_content.core_alias is a key, but it, too, is not unique. There is no real need to alter these column types, either. Instead, just alter the key sizes. For example,

ALTER TABLE `#__categories` DROP KEY `idx_alias`, ADD KEY `idx_alias` (`alias`(191));

Chances are good that people are not using aliases longer than 191. But if they are, it's unlikely that they are using aliases where the first 191 characters are the same (and so look-ups using the key will still be quick). But again, if they are using multiple aliases where the first 191 characters are the same, the lookup might be slowed by having to search for a match. But it shouldn't really be a big deal, and a slower search would be preferable to truncating their aliases, which could possibly leave duplicate aliases.

ALTER TABLE `#__session` MODIFY `session_id` varchar(191) NOT NULL DEFAULT '';
ALTER TABLE `#__user_keys` MODIFY `series` varchar(191) NOT NULL;

The #__session.session_id is a primary key. (It originally was varchar(200).)
#__user_keys.series is a primary key.

When the column is part of a unique key (and primary keys are unique), then the issue is less clear. You could leave the column size unchanged and just shorten the key, but that still requires that keys be unique within the first 191 characters, so I can't imagine keeping the characters beyond that is helpful.

I think session_ids are, in practice, relatively short (< 30), so I see no problem with resizing that column. For that matter, it's never going to contain utf8mb4 characters, so another alternative is to not convert it.

I really don't know anything about the series column. I'm guessing that it is not based on user input. It probably(?) is unharmed by shortening it. But maybe it doesn't need converting, either.

ALTER TABLE `#__menu` MODIFY `alias` varchar(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The SEF alias of the menu item.';
ALTER TABLE `#__menu` DROP KEY `idx_client_id_parent_id_alias_language`, ADD UNIQUE KEY `idx_client_id_parent_id_alias_language` (`client_id`,`parent_id`,`alias`(191),`language`);

alias is part of a unique key, and that key is modified as well, as shown above.

Based on what I've already said, I doubt it would be a problem to leave the alias column as varchar(255). That avoids loosing data. However, users will be required to make the first 191 characters unique (uniqueness beyond 191 doesn't count).

Either way this is handled, the key must be shortened if the column is to hold utf8mb4 characters. And shortening the unique key means there is the possibility that duplicates are found when looking at only the first 191 characters. If that happens the ALTER TABLE will fail. That sort of failure has not really been addressed. It probably should be caught so that it does not abort later queries that follow. But the risk is small because -- who uses 191 character aliases?

Also of note is:

ALTER TABLE `#__redirect_links` DROP KEY `idx_link_old`, ADD UNIQUE KEY `idx_link_old` (`old_url`(191));

old_url is used as a unique key, but its type remains unchanged: varchar(255). So there is no possible data loss. But the issue mentioned above applies here, too. If shortening a unique key results in duplicates found when looking at only the first 191 characters, the ALTER TABLE query will fail, possibly aborting later queries. I would think it better to gracefully catch the error (or detect the problem beforehand) and leave the column as utf8. Also note that old_url might seem like it (being an "URL") would not contain utf8mb4 characters, but I don't think that is guaranteed. The url is first passed through rawurldecode() before it is stored.

@roland-d From my point of view it should be decided by PLT (good that you are a member) before release of 3.5.0 if Joomla! wants to face site owners or admins with data loss and, if so, how far it shall go, apply it like it is with this PR now, i.e. shorten more texts than necessary, or try to optimize that, as @zjw 's comment explains.

If you or PLT decide to apply further changes on this PR (or do it with another one), everything has to be tested again, i.e. both new install and update on both kinds of databases for at least database drivers mysqli and pdomysql if not also mysql (without i), so we have 8 to 16 tests to be done, all with comparison of data structures in sql export files afterwards.

So or so, if it is taken as it is now or if further optimizations as suggested above by @zjw are applied, the release notes of 3.5.0 have to clearly list all table columns where data losses may appear from my point of view.

Releasing this PR as it is now with 3.5.0 and not telling anything about it will very likely cause a lot of complains, because such data losses may not be detected when checking things after upgrade and then a few weeks or months later when they are detected it may be too late to fall back to old data, and forums will be full with support requests.

What can be done in order not to further delay the next beta release is to release the beta with status of this PR and then before the final 3.5.0 work on it further and optimize things, because there is no update path from any beta to the next beta.

But it has to be finally decided before 3.5.0 which way it goes.

I will be offline for the next hours but later I am available for further discussion and testing.

I have put the issue before the PLT so we can decide how to move forward. In my opinion we can't cancel this request and leave sites vulnerable just because sites may have aliases beyond the 192 character limit.

My idea would be to add a check in the preflight to see if there are any aliases longer than 192 and if so we can cancel the installation with a message informing the user. What that message is going to be is to be determined of course.

My idea would be to add a check in the preflight to see if there are any aliases longer than 192 and if so we can cancel the installation with a message informing the user.

You can't. The way the core update component works, the update is unpacked in full then the upgrade.finalise task is called which triggers a mocked version of a JInstallerAdapter upgrade path.

@mbabker Thanks for the feedback.

One way to check and notify could be to release a 3.4.6 which does the check and shows the result in a postinstall message so site admins could react before they upgrade then to 3.5.0, but this would be 1. much additional work maybe and 2. maybe delay the project schedule and 3. only be a theoretical solution since it is not granted every site is going the way from <= 3.4.5 via 3.4.6 to 3.5.0, at least when I see how many questions in the support forum refer to outdated version and even 2.5.x I daubt if this would really help.

We could force a step between using the update XML files. There we can target exactly which version of Joomla should an update be shown. So to Joomla <= 3.4.5 only the 3.4.6 would be shown and 3.5.0 to those on 3.4.6.
But it's a bit complex and I actually doubt users will read the postinstall message before they upgrade further to 3.5.0. So it would just create a tedious step for most users without actually being useful in the end.

Yes, I have the same daubt, but I thought I should mention it here for discussion.

I have prepared another PR: roland-d/joomla-cms#7. It addresses the issues that I brought up earlier.

Downsizing of columns to varchar(191) has been eliminated, except for #__session.session_id and #__user_keys.series, where it does no harm. The size of a session_id is dependent upon the PHP configuration (session.hash_function and session.hash_bits_per_character), but the worst-case scenario is a 512-bit hash (sha512 or whirlpool) and an encoding of 4 bits per character, resulting in a 128 character session_id, so there is no danger of data loss there. The series column contains a 20 character key generated in PlgAuthenticationCookie::onUserAfterLogin(), so there is no danger of data loss there, either.

I had mentioned that there was a particular problem with reducing key sizes to 191 characters in cases where those keys were UNIQUE. The solution, in my opinion, is to not declare those keys UNIQUE. For the most part, the Joomla code is already performing adequate checks for duplicate entries before inserts and updates for the 2 columns at issue (#__menu.alias and #__redirect_links.old_url). I have improved that code some so that duplicates should not occur.

@wilsonge: The problem is when updating from 3.4.5 or before. In those cases, $db->hasUTF8mb4Support() doesn't exist. Consequently, everything needed for conversion must be contained in script.php, even though that seems like a duplication of code.

The script file is not triggered until the filesystem has been updated to 3.5 unless you are updating through the Extension Manager.

@mbabker: Yes, but the old (3.4.5) database classes have already been loaded before the script is triggered, so I don't think updating the class files makes any difference. The old classes are still the ones in use.

The <3.5.0 code is ONLY used if you are upgrading via the Extension Manager. Because of the use of AJAX in the update component, the package is extracted in full in several smaller steps then a request to the update component's update.finalise task is made and this is the point where the install script and database update queries are executed. This request is 100% in the context of a 3.5.0 filesystem.

Ah -- that makes sense.

I suspect that people performing update tests of this PR are doing so via the extension manager. That method seems like it needs to be supported.

@roland-d @zjw and others involved into this PR:

I have added xml files for using the patched update container (links see above somewhere) to the donwload location, so the container can be used for update via Joomla! Update Component, i.e. it is not necessary to use the Extension Installer anymore for the update tests.

To do so, select "Custom URL" as update channel in the Joomla! Update Component's options, and enter "http://test5.richard-fath.de/list_test.xml" as URL:

After "Save & Close" you should see then:

The update can be started then in the usual way.

This method changes the URL in update sites table. To come back to the standard, change the update channel back to what it was before (e.g. Testing).

P.S.: Of course I have tested again with the latest container how I described in my previous comment, and it was successful.

@roland-d @zjw and others involved into this PR:

I have created new full installation and update containers in case if testing this PR goes on somehow, e.g. with regular Joomla! Update method (i.e. not Extension Installer) or as a base for new containers after any further commits.

The containers patched by this PR#s changes are based on latest staging, i.e. they also include the security fixes done with 3.4.6.

For update tests with Joomla! Update, use "http://test5.richard-fath.de/list_test.xml" as custom update URL as I described in my comment above.

For full installation tests or update tests with the Extension Installer (from folder or upload & install package) use following links:

Joomla_3.5.0-beta-Beta-Full_Package_utf8mb4_fix9.zip

Joomla_3.5.0-beta-Beta-Update_Package_utf8mb4_fix9.zip

Note the changed package name "..._fix9.zip" in case you used "Install from URL" for update tests using the Extension Manager. The links of the old packages "..._fix8.zip" will produce a 410 "gone" now.

at least for me updating worked great from joomla 3.4.5 to 3.5 fix 9 on a host where utf8mb4 is not available

-- update

receive the following error when applying an old database (utf8) to a already patched 3.5.0 fix9 and doing database repairs on an utf8mb4 compatible server afterwards

(Fehler # 1071)!: Specified key was too long; max key length is 767 bytes SQL=ALTER TABLE #__redirect_links CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

All other alter table commands went fine!

Just to give an update, I have not forgotten about this but there are a lot more things to be considered/checked. I will be back soon with an update on how we will proceed.

thx

On 04.01.2016, at 18:09, RolandD notifications@github.com wrote:

Just to give an update, I have not forgotten about this but there are a lot more things to be considered/checked. I will be back soon with an update on how we will proceed.

—
Reply to this email directly or view it on GitHub.

@roland-d Any news? Meanwhile Beta 2 is out and this PR here was claimed to be the show stopper for 3.5, so I have expected this PR here going into the Beta 2.

Please see the release notes
On 28 Jan 2016 11:14 am, "Richard Fath" notifications@github.com wrote:

@roland-d https://github.com/roland-d Any news? Meanwhile Beta 2 is out
and this PR here was claimed to be the show stopper for 3.5, so I have
expected this PR here going into the Beta 2.

—
Reply to this email directly or view it on GitHub
#8472 (comment).

@richard67 The status is that there will be a number of new PRs that will superseed this PR. We have been working behind the scenes to get this issue really cleared up. Once the PRs are ready to be tested, I will link them from here.

You are correct, this is a showstopper for 3.5 and will be fixed before the stable is released.

Closing this based on us merging #9045 as this is now dated. We can come back to this if it doesn't work out in beta 3