I almost agree. The utf8mb4-conversion.sql file must also contain the SQL to drop and create indices BEFORE converting the tables and columns to utf8mb4.

Remember that our problem is that our indices are using all 255 3-byte characters in the current schema. When you update the table to utf8mb4 this increases to 255 4-byte characters which is too big for MySQL indices, therefore the schema update FAILS. As a result we need to change the indices to a length of 100 characters BEFORE doing the character conversion to prevent MySQL from erroring out on character set conversion. If you don't do that your PR is futile ;)

Yes, but the modifications of the indexes (limit to first 100 chars) and then modifications of the columns (enlarge to 400) are still in the 3.5.0-2015-07-01.sql update file. This should be sufficient for the update if all works as it should, or am I wrong?

Also I am not sure if I understand you right: Do you suggest to leave these statements there in 3.5.0-2015-07-01.sql and do the same statements then again in the new separate update script? Or do you suggest to move them from the 3.5.0-2015-07-01.sql update file to the new separate update script?

You are only going to call convertTablesToUtf8mb4 from the fix() method
which is triggered by the Extensions, Manage, Database, Fix button. You
will only get there because applying the 3.5.0-2015-07-01.sql file has
failed for whatever reason. We have already established that the schema
fixer doesn't like it when we try to redefine indices or when we ask it to
convert tables and columns to utf8mb4. This is why you are
modifying convertTablesToUtf8mb4 to use a special .sql file in the first
place.

Considering all of the above, the utf8mb4-conversion.sql file must be
self-contained. In other words it should contain all SQL statements
necessary to complete the utf8 to utf8mb4 transition. It is our last
resort. So I suggest that you copy the index altering commands from
3.5.0-2015-07-01.sql to the top of the utf8mb4-conversion.sql.

Well, yes, you are right, either I move the index modifications, or I do what I tried with my other PR, make the schema manager handle them, but this is a never ending story then wich opens the blackhole to handle alter table statements having multiple sub statements separated by commas in any case, so yes, you are right, we better go the easy way. I make a commit in a moment for that.

@nikosdion Question: How is it with the columns enlargement to 400? Might this break indexes on non-utf8mb4 databases? If so, we have to move these, too, and at the end the 3.5.0-2015-07-01.sql will be empty.

The size of the column is irrelevant. Remember that when we redefine the
indices (per Eli's guidance) we tell MySQL to only take into account the
first 100 characters of the column. This both overcomes the index size
problem *and *makes index parsing faster (so our queries are executing much
faster, improving performance of the CMS!).

Yes, but the 3.5.0-2015-07-01.sql still contains the statements to enlarge columns to a size of 400, and I have no idea if this is maybe too much for existing indexes on these columns with utf8 before the conversion to utf8mb4 will take place.

@nikosdion If what I mentioned in my previous comment is not a problem, is this PR here then OK now from your point of view?

Ah, the indices must be redefined before the columns are made bigger. Otherwise 3 * 400 = 1200 bytes which is larger than the 790 or so bytes allowed in a MySQL index.

Other than that this PR looks good to me.

Yes, and so we have to move this, too.

And then we have to perform the conversion script on both kinds of servers with and without utf8mb4 support, with the new function to replace utf8mb4 by utf8 in case if no support. The character set conversion and collation change should then be useless, ok, but also do not any harm.

Or as an alternative, we have another separate script for the non utf8mb4 databases having the resizing and index limitations inside butn not the character set convesions, but this would mean 2 scripts to be maintained simultanously in future, so I prefer the first way.

OK for you?

Or we do what I tried to do with my old PR, make the schema manager handle the comnined drop and add of index in 1 statement, so we can leave that in the update script, like it was before latest commit.

@nikosdion Hmm, what we have up to now is not sufficient because if no errors occur, nothing to be fixed in the database view, and so the conversion script will not be called. Just checked on both database with and without utf8mb4 support. For non-utf8mb4 we could force some error to happen so the fix can be used by creating a dummy table with utf8mb4 character set in a new update script, but for utf8mb4 I have no idea yet. Or is there a way to call the script at the end of installation?

Please don't test yet, currently updates finish with success on both kinds of database with and whtout utf8mb4 support without having performed the utf8mb4 conversion, which after the latest commit will be performed for both kinds of databases but with converting the statements, but because there is nothing to be fixed, nobody will press the fix button and run it, so what is missing is to create a table holding a flag with value 0 and check this when running the schema manager, and show a message that conversion has to be done among the found problems, so the user will press the Fix button.

@wilsonge @nikosdion @infograf768 @sovainfo @810 @aschkenasy @mbabker @brianteeman and all others: Please test.

@richard67
I have tested the patch with the following error.

After installing the patch checking the DB

Error
Table 'aviondemo.#__mysql_utf8mb4_test' doesn't exist SQL=SELECT `converted` FROM `#__mysql_utf8mb4_test` WHERE `converted` = 1;

•Database schema version (3.5.0-2015-11-05) does not match CMS version (3.5.0-2016-02-26).
•Table 'rizjf_mysql_utf8mb4_test' does not exist. (From file 3.5.0-2016-02-26.sql.)
•Table utf8mb4 conversion does not exist. (From file utf8mb4-conversion.sql.)

After pressing fix
•Table utf8mb4 conversion does not exist. (From file utf8mb4-conversion.sql.)

Steve

@Stevec4 And after again pressing "Fix"?

@richard67
J3.4.8 with sample data
Updated via update component custom url http://test5.richard-fath.de/Joomla_3.5.0-beta2-Beta-Update_Package_test2.zip
First error fixed
Then error
Table 'dp1va_user_profiles' does not have column 'profile_value' with type 'TEXT
That fixed

FULL SUCCESS

@Webdongle @Stevec4

Yes, Webdongle, thanks for testing. This is how it should work, I just updated test instructions via GitHub, it may take a time until visible on the issue tracker.

It may need several "Fix" steps but at the end all should be ok.

@Webdongle Could you mark your test result in the issue tracker?

I have tested this item successfully on 38c002c

full success

@richard67
•Table utf8mb4 conversion does not exist. (From file utf8mb4-conversion.sql.)
Message remains no matter how many times I select fix
this is a beta 2 install if that maters

Steve

@Stevec4 Try with a 3.4.8 or so please, and use my container or custom URL link, or use current staging when doing it with the alternative method descibed by wilson, but not a Beta 2.

same issue:
1) on update: Failed to start the session: already started by PHP.
2) On database check: Table utf8mb4 conversion does not exist. (From file utf8mb4-conversion.sql.)
3) Then click Fix
4) I see other issue not fixed
5) I need again click fix

Then everything is ok

@wilsonge Or could @Stevec4 bad test result be related to the fact that I did not use db->quoteName() in my select and update statements?

@810 Read my test instructions please: The PHP session issue is not related to this PR, neither the profile value column. When you press the "Fix" button in the database view, it will be fixed. So not an issue with this PR, please consider your test be successful and mark your test result on the issue tracker. Thanks.

Thanks Richard using the latest staging build now. Still showing the error will try on a 3.4.8 db
I was going to mention that the error is not showing the database prefix and wonder if its related to that. The table s not listed if I check via myphpadmin

Database Version 5.5.5-10.1.9-MariaDB

Database Collation utf8_general_ci

Database Connection Collation utf8mb4_general_ci

PHP Version 7.0.1

As far as I know there are issues with Maria db support regarding version detection and so also utf8mb4 support detection. Just search for issues containing Maria, not remeber if it was open or closed.

Regarding the db prefix: I see my mistake, will commit a correction soon.

Then you can test again (also on Maria db, but then it may happen that Joomla! thinks it will not support utf8mb4 and so the tables still are in utf8_general_ci collation.

Understood thanks Richard

@Stevec4 Hmm, here I saw the db prefix in the message, and before I had no exception or so without showing the prefix. Please test again after my next commit.

Ahh, I found my mistake: I should not name the table so the utf8mb4 will be replaced by utf8 within the table name when no utf8mb4 suppport or support not dectected because MariaDB!!!

Please wait my next commit and then text again.

This PR has received new commits.

CC: @Webdongle

@Webdongle @Stevec4 and all others: Please test.

@Stevec4 Please note that as I mentioned before utf8mb4 support might not be detected on your MariaDB.

The error you had when testing was caused by an error in my PR for the case of no utf8mb4 support, which should be fixed now. So the error should have gone now.

@wilsonge This is it now, am sure. Please test, too.

Test as before with http://test5.richard-fath.de/Joomla_3.5.0-beta2-Beta-Update_Package_test2.zip ?

@Webdongle @Stevec4 @wilsonge and all others: Please wait, 1 more commit. Of course i also should convert my new table to utf8mb4, even if it not is necessary or has effect.

@Webdongle Yes, same zip, just wait until I updated it and then made my next commit.

just •86 database changes were checked successfully. ?

I have tested this item successfully on 9deb617

@richard67 OK ... fresh 3.4.8 installed. Will wait until you say

This PR has received new commits.

CC: @810, @Webdongle

@Webdongle @Stevec4 @wilsonge @810 and all others: Now it can bet tested (again). I think this really was it now.

P.S. Is still the same zip container and update url, the zip is already updated.

@810 Yes, just 86 changes checked because the utf8mb4 conversion statements now - in opposite to my old PR - not count anymore, the conversion is performed separately at the of the "Fix".

I updated test description, can take a while until visible in the issue tracker, so check it in the PR on GitHub.

Tested with http://test5.richard-fath.de/list_test5.xml as custom url
Same result ... is that expected ?

Yes, same result for databases which support utf8mb4. No change to your last test. Only for those without utf8mb4 support my recent changes have an effect.

The PHP error about session already started is not cause by this PR as far as I know, if you have such.

And it is normal that after dating Joomla you have to use the Fix button at least 1 time, if necessary 2 times.

Could you remove the #__mysql_utf8_mb4_test table after migration, or do we need it

We need it on every "Fix" button use, so I cannot remove it so as this PR now is like it is. It is a kind of hack but works well. Not nice, sure. But it is not a big table and needs not much space, and as soon as Joomla! drops support for mysql versions not supporting utf8mb4 it can dropped.

ok then maybe rename it, looks better in the ordering

maybe #_update_check

@Webdongle The dastabase problem with the "Table #__user_profiles does not have column profile_value with type TEXT" is also not related to this PR here.

@810 I want this to be tested with success now. If people don't like the table name we can rename it later, or committer can do that. At the momnent the name is ok for me because is shows the purpose of the table.

I have tested this item successfully on cc269ed

@Webdongle Was your test a success now from your point of view? If so, could you mark the test result on the issue tracker? Thanks.

I have tested this item successfully on cc269ed

Success

@Webdongle @810 Just for the records, which mysql server version did you test on?

@Stevec4 Could you repeat your test with the MariaDB? It should be treated as if no utf8mb4 support, which maybe is not right but is another issue of Joomla! (it officially does not support MariaDB and has an issue with its version detection for it), but you should be able to update as described in my test instructions, i.e. it may need a few times the "Fix" button but at the end all should be ok.

@Stevec4 @wilsonge The MariaDB issue I refer to is #9062 . This leads to utf8mb4 support not being dectected correctly on MariaDB, so it is always using utf8 (without mb4). Should also be fixed but is not an issue caused by or related to this PR here.

@richard67

localhost xampp Win 10... (not sure why Joomla reports it as win 8 but here's the stats

PHP Built On Windows NT MY-COMPUTER 6.2 build 9200 (Windows 8 Home Premium Edition) i586
Database Version 5.6.26
Database Collation utf8_general_ci
Database Connection Collation utf8mb4_general_ci
PHP Version 5.5.28
Web Server Apache/2.4.16 (Win32) OpenSSL/1.0.1p PHP/5.5.28

@Webdongle So your successful test is for the case of utf8mb4 supported.

Just for info: I see your database collation is still set to utf8_general_ci, which may be ok in case if some 3rd party extensions not explicitely specify their character set and collation when creating tables, and having too long columns or indexes and so may have problems with utf8mb4.

But as soon as Joomla 3.5 is out and all 3rd party extensions are ok with utf8mb4, the database collation should be changed to utf8mb4_general_ci.

Joomla! does not change the database default collation (not before and not after this PR) because it does not know what else you have in your database beside Joomla! core tables, but all Joomla! core tables should be converted to utf8mb4 after this patch when your database supports this (what it did in your test).

Database Version 5.7.11

Database Collation utf8mb4_bin && utf8_general_ci
Database Connection Collation utf8mb4_general_ci

PHP Version 5.6.16

Web Server Apache/2.4.18 (Win64) PHP/5.6.16

5.7.9 is failing, so there is a bug on that version

@810 Ok, thanks, I see in your case is also utf8mb4 supported. The collations of the tables should be utf8mb4_general_ci, while database collation still might be utf8_general_ci, see also my previous comment for Webdongle.

So we also need testers for the case utf8mb4 not supported (my own test does not count unfortunately, because I tested both cases with mysql 5.5.something and other with 5.3.somethingelse.

@richard67 I have the same result using the latest updated patch. On my local host using Xampp & php7

•Table utf8mb4 conversion does not exist. (From file utf8mb4-conversion.sql.)

Database Version 5.5.5-10.1.9-MariaDB
Database Collation utf8_general_ci
Database Connection Collation utf8mb4_general_ci 
PHP Version 7.0.1

I also installed the latest staging build on my webserver and receive the same error. Sorry I know how hard you are working on this issue,

•Database schema version (in #__schemas): 3.5.0-2016-02-26.
•Update version (in #__extensions): 3.5.0-beta2.
•Database driver: mysqli.
•85 database changes were checked successfully.
•148 database changes did not alter table structure and were skipped

•Table utf8mb4 conversion does not exist. (From file utf8mb4-conversion.sql.)

Database Version  5.6.28-log  
Database Collation  utf8_general_ci  
Database Connection Collation  utf8mb4_general_ci  
PHP Version  5.6.18  
Web Server  Apache  
Joomla! Version  Joomla! 3.5.0-beta2 Beta [ Ember ] 27-January-2017 23:00 GMT  
Joomla! Platform Version  Joomla Platform 13.1.0 Stable [ Curiosity ] 24-Apr-2013 00:00 GMT 

@Stevec4 I do not understand. Do you mean the reported database problem does not disappear after using the Fix" button (if necessary several times) in "Extenions -> Manage -> Database"?

@Stevec4 If so, can you copy the statement from administrator/components/com_admin/sql/updates/mysql/3.5.0-2016-02-26.sql into your database client (myphpadmin or whatever is used with MariaDB), replace the "#__" with your db prefix and execute the SQL and let me know if some errors (and if so, which) or if not?

@richard67 yes correct the error remains after using fix 10-15 times. I will manually copy the statement and advise.

@Stevec4 Thanks. I am curious if you will find something.

If not: May maintainers like e.g. @wilsonge decide whether a test with MariaDB counts or not.

The sql executes fine the table_mysql_utf8_mb4_test table is present. But the error message
•Table utf8mb4 conversion does not exist
Still exits only 85 DB changes were checked

I do not see a table UTF8mb4 listed in file utf8mb4-conversion.sql is there suppose to be?

@Steve Well regarding the 85 checked statements:

It should be not many more db changes checked. Those dealing with utf8mb4 conversion will not be counted anymore. I had 86 being checked because I had a dab problem not related to this pr, which I could solve with the Fix button, too. If you did not have this problem, then the count of 85 is ok. Did you try to use the "Fix" button again after you "manually" executed the SQL? If not, try 1 or 2 times, and if you did, let me know.

Regardfing your last comment

I do not see a table UTF8mb4 listed in file utf8mb4-conversion.sql is there suppose to be?

I do not understand exactly what you mean with your question.

The error message is referring to *•Table utf8mb4 conversion does not exist *so just wondering it is suppose to be created during the check
•Table utf8mb4 conversion does not exist. (From file utf8mb4-conversion.sql.)
I have tried the fix button many times 10-15 and still have the one message

@Stevec4 Ah, now I understand.

That's a thing which has to be made nicer after this PR. Currently I abuse an existing message text for missing table to report the converison has not been done yet. I mentioned this in code comments and at the top of the PR in the descripiton. There is no table "utf8mb4 conversion" expected to exist.

But this message should disappear after using the "Fix" button.

understood sorry I messed that bit.

What do you get when you enter following in SQL (replace "#__" by your db prefix):

SELECT converted FROM #__mysql_utf8_mb4_test WHERE converted = 1;

And what from this one:

SELECT converted FROM #__mysql_utf8_mb4_test;

MySQL returned an empty result set (i.e. zero rows) for both

@Stevec4 I know what the problem is: If you test not with Joomla! Updater but with the test method from @wilsonge , or maybe this happens with the Extension Installer, too, then the database fix will create the new table but not insert the record from the same 3.5.0-2016-02-26.sql. I have to add this at another place. This may take a while.

@810 @Webdongle Sorry, you will have to test again after I fixed it. Please be patient.

@richard67

phpMyAdmin creates database with Collation as latin1_swedish_ci by default

Joomla install creates database with Collation as uft8_general_ci

After updating with http://test5.richard-fath.de/list_test5.xml as custom url Collation is utf8mb4_general_ci for some tables and uft8_general_ci

SELECT converted FROM sykps_mysql_utf8_mb4_test WHERE converted = 1; returns error table doesn't exist. Visually checked the tables (in case I got the syntax wrong) edit found it

makes sense @richard67 that would explain the empty table.
Standing by to test when your done.

00:50 hours here will stop up a little longer,

This PR has received new commits.

CC: @810, @Webdongle

This PR has received new commits.

CC: @810, @Webdongle

@810 @Webdongle @Stevec4 Please test again as soon as you find mood and time.

My zip container mentioned in the test description is already updated to latest staging + latest state of this PR.

@Stevec4 The problem you had should be solved now.

Note that when testing with @wilsonge 's method it can happen that at the top of the database problems an error message about a failed select for the new table is shown because the new table does not exist. This required an additional click on the "Fix" button after database problems were solved. But I discovered this only with that test method. When using Joomla! Update or the Extension Installer, all works.

I updated test description to tell that.

The fact that you are logged out when using the extension installer is not related to this PR, this is another open issue.

I've added this to the test description, too.

P.S. @Webdongle This PR converts only the Joomla! core tables, not such of extensions, they have to cater themselves for that.

@richard67 problem solved a good test for me

Steve

@Stevec4 Thanks for testing. Could you mark your test result in the issue tracker https://issues.joomla.org/tracker/joomla-cms/9221?

Finally I made it work with all test methods .. I know, it is not a nice solution, some may call it an ugly hack ... but it works and maybe can be improved later ... I think this can help to make 3.5.0 come.

Will do Richard, good work getting it fixed

Well, I need sleep now after all that ... here it is 03:55 am

I have tested this item successfully on 39c0530

Tested on two separated DB successfully

Sleep well!! you deserve it.

Tested by updating with http://test5.richard-fath.de/Joomla_3.5.0-beta2-Beta-Update_Package_test2.zip
Clicked 'fix' on both database errors
SELECT converted FROM #__mysql_utf8_mb4_test WHERE converted = 1; result shows
Is that the expected result.?

I have tested this item successfully on 39c0530

Test OK in a site with 200MB db (10-20 secs to fix the db)

Warning: Database is not up to date! 
1 Database Problem Found.
Table utf8mb4 conversion does not exist. (From file utf8mb4-conversion.sql.)

... Fix ...

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

... Fix ...

Database table structure is up to date. 

• Database Version 5.5.47-0ubuntu0.14.04.1
• Database Collation latin1_swedish_ci
• Database Connection Collation utf8mb4_general_ci
• PHP Version 5.6.18-1+deb.sury.org~trusty+1
• Database Type MySQLi

I would have also tested with no utf8mb4 support ... but I don't know if it is possible to disable it in this system.

@Webdongle Depends. If you go to the Extensions -> Manager -> Database view again, and maybe click the Fix button again even if no reason, do they becomre more records? Or will it be 1 at the end?

@anibalsanchez Cannot be disabled, can only be determined by version numbers of database server and client library, so you would have to hack some code to change that for test purpose.

Thanks for testing.

@Webdongle Can be that I have to fix something with these records, at the end there should 1 only.

Tests:
1. On an already beta2, overwriting

Error
Table 'testsql.#__mysql_utf8_mb4_test' doesn't exist SQL=SELECT `converted` FROM `#__mysql_utf8_mb4_test` WHERE `converted` = 1;




    Database schema version (3.4.0-2015-02-26) does not match CMS version (3.5.0-2016-02-26).
    Database update version (3.4.8) does not match CMS version (3.5.0-beta2).
    Table 'jos_contentitem_tag_map' should not have index 'idx_tag'. (From file 3.5.0-2015-10-26.sql.)
    Table 'jos_contentitem_tag_map' should not have index 'idx_type'. (From file 3.5.0-2015-10-26.sql.)
    Table 'jos_mysql_utf8_mb4_test' does not exist. (From file 3.5.0-2016-02-26.sql.)
    Table utf8mb4 conversion does not exist. (From file utf8mb4-conversion.sql.)



After fix

Error
Table 'testsql.#__mysql_utf8_mb4_test' doesn't exist SQL=SELECT `converted` FROM `#__mysql_utf8_mb4_test` WHERE `converted` = 1;



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

but when looking at phpmyadmin, this exists
profile_value   mediumtext  utf8mb4_general_ci      No  None        Change Change   Drop Drop   

    Primary Primary
    Unique Unique
    Index Index
    Spatial Spatial
    Fulltext Fulltext
    Distinct values Distinct values

1. On a clean 3.4.8, overwriting

Error
Table 'testsql.#__mysql_utf8_mb4_test' doesn't exist SQL=SELECT `converted` FROM `#__mysql_utf8_mb4_test` WHERE `converted` = 1;



    Database schema version (3.4.0-2015-02-26) does not match CMS version (3.5.0-2016-02-26).
    Database update version (3.4.8) does not match CMS version (3.5.0-beta2).
    Table 'jos_session' does not have column 'session_id' with type 'varchar(191)'. (From file 3.5.0-2015-07-01.sql.)
    Table 'jos_user_keys' does not have column 'series' with type 'varchar(191)'. (From file 3.5.0-2015-07-01.sql.)
    Table 'jos_contentitem_tag_map' should not have index 'idx_tag'. (From file 3.5.0-2015-10-26.sql.)
    Table 'jos_contentitem_tag_map' should not have index 'idx_type'. (From file 3.5.0-2015-10-26.sql.)
    Table 'jos_mysql_utf8_mb4_test' does not exist. (From file 3.5.0-2016-02-26.sql.)
    Table utf8mb4 conversion does not exist. (From file utf8mb4-conversion.sql.)


After Fix
Same result as 1.

@infograf768 Thanks for testing. I will have to look into that deeper again, and have to fix something anyway. Could you mark your test result as not successful? This will prevent it to get RTC because already 2 good tests.

All other testers and readers: Sorry when causing additional work, will have to change some stuff and so it might require new tests. Thank you all very much for helping me with tests to correct things. Please be patient, it may take a few hours until I can do something again.

@richard67
Shall I make it not successful although I simply overwrote the site (not using extension or joomla update)?

@infograf768 Hmm, depends. For George it is a valid test method.

Can you restore your db to the pre-update state but leave the file system as it is, i.e. patched, and then remove following files

• administrator/components/com_admin/sql/updates/mysql/3.5.0-2016-02-26.sql
• administrator/components/com_admin/sql/others/mysql/utf8mb4-conversion.sql

and then try again?

Without these files, nothing will be left related to the utf8mb4 conversions, and if you then still have the same problem, that database Fix does not work for you, then your problem is not related to the utf8mb4 conversions, and in this case you cannot really test it.

But even if so I will later try to find out what the problem is with help of your detailed output from above.

And of course if you have time, try extension installer or joomla update too, maybe also without these 2 files.

@infograf768 P.S.: The profile table columns thing is reported because the older sql once created or modified it as text while later is seems to have changed to mediumtext, see your last check, but because the schema manager checks from old to new it complains about the newer status when checking the older file. But this then should be fixed with the Fix button, if necessary step by step.

That the error with the non existing table "#__mysql_utf8_mb4_test" is still shown at the top may just be caused by the error messages not being cleaned up after a run of the fix button and so remain there even if with the next run the table is there, but i am not 100% sure if that is the case.

Re-loading the formerly 3.4.8 site (case 2. above) and looking at Extensions: database, the only remaining error was the user_ profiles one and doing a new Fix got rid of it.

@infograf768 Ok, thanks for testing, that information might help me to find the problem, or at least make me not search at the wrong place. I think you should really mark your test as not successful then, I will not be sad about that.

I have tested this item unsuccessfully on 39c0530

Not yet successful

@infograf768 Ahhh, did you remove those 2 sql files I mentioned 3 comments abover before you did your fallnack to the old database and checked again and only profile thing was shown and this could be fixed in 1 step? I ask just to be sure.

I did not do a fallback. I did not remove any files. Just reloaded the site and fix a second time (case 2.)

With fallback I mean restore the pre-update database status.

But I understand your test result better now.

It really seems to be necessary to reaload the site, either some cachig stuff or whatever.

I do not assume you test with PHP 7, but if so, you should switch off opcache.

I also do not assume you tested with Joomla caching on, but if so, this could also cause problems.

And if you can, can you fall back with the database, then remove those 2 sql files I mentioned and then start again the test? I could see then if you have any problems and have to reload the page also if everything related to utf8mb4 conersions (and these are the 2 sql files) is excluded from the procedure.

But even if we find out that this PR works for you I plan to do some changes on it to avoid the table not exists error, so you can let your test result be unsuccessful until it will be reset by a commit anyway. Thanks again for helping with tests because here I cannot reproduce all problems. I hope I do not waste your time.

@richard67

"I hope I do not waste your time."
If you can put the effort into creating the patch then we can put time in trying to break it

So ... more testing to come ? will install 3.4.8 again

Unfortunately I have something else to do for the next 2 ... 3 hours before I can start to work on this again, so please be patient

I have taken into account MySQL version AND PHP version in my tests.
Results I am showing here were all done online (not on a localhost).

Updates with YOUR version, from the .xml file.

• 1st test From 3.4.8

Database Version 5.1.73-log

Database Collation latin1_general_ci

Database Connection Collation utf8_general_ci

PHP Version 5.6.18

1062 - An error has occurred.
Duplicate entry '452' for key 'PRIMARY' SQL=INSERT INTO #__extensions (extension_id, name, type, element, folder, client_id, enabled, access, protected, manifest_cache, params, custom_data, system_data, checked_out, checked_out_time, ordering, state) VALUES (452, 'plg_system_updatenotification', 'plugin', 'updatenotification', 'system', 0, 1, 1, 0, '', '', '', '', 0, '0000-00-00 00:00:00', 0, 0);

Error
Table 'db509406158.#__mysql_utf8_mb4_test' doesn't exist SQL=SELECT converted FROM #__mysql_utf8_mb4_test WHERE converted = 1;

fix OK

Database schema version (in #__schemas): 3.5.0-2016-02-26.

• 2nd test from 3.5 beta 2,

Database Version 5.1.73-log

Database Collation latin1_general_ci

Database Connection Collation utf8_general_ci

PHP Version 7.0.3

Error:
Table utf8mb4 conversion does not exist. (From file utf8mb4-conversion.sql.)
Fix OK

• 3rd test form 3.4.8

Database Version 5.5.47-0+deb7u1-log

Database Collation latin1_general_ci

Database Connection Collation utf8mb4_general_ci

PHP Version 5.6.18

Error:
Table utf8mb4 conversion does not exist. (From file utf8mb4-conversion.sql.)
Fix OK

@ghazal Hhm, your 1st result surprises me, the system plugin for update notification is something new in 3.5 (Beta 1 and 2) and so it cannot having been there in a 3.4.8. But this is not related to this PR or the utf8mb4 problems, so I would consider your test as successful, BUT: I will do an update on this PR later anyway as mentioned in my comments before, so you might have to test again anyway.

But thanks for your tests, they confirm what I saw before and what I have to fix to make it better.

Happy to test again if necessary and useful.
Just tell us.

Shall I make it not successful although I simply overwrote the site (not using extension or joomla update)?

So what i was doing with git is kinda replicating the old 1.5 update method where people unzip the package and then hit fix (and discover install any extra extensions). It sucks so hard but there are a lot of people who still do this :(

@richard67 i'm free pretty much all weekend - let me know what you need me to do!

@wilsonge Just be patient, I just am testing here a new set of changes which will make everything nicer, e.g. better handling of database errors and special messages for display in the database problems view, and so this will require also to update my test instructions a bit. I am close before commit. Maybe 1 hour, maybe 2. Just am testing here everything first with diverse methods on 2 kinds of databases.

@wilsonge P.S.: One question which occurred here from a tester: What shall be done with core tables which have a local character set collation, e.g. swedish or so? Currently they will be converted to either character set utf8 with collations utf8_general_ci and for binary columns utf8_bin if no utf8mb4 support, and to character set utf8mb4 with collations utf8mb4_general_ci if utf8mb4 support.

But maybe we should leave them untouched and only convert utf8 without mb4 stuff to utf8mb4 stuff?

I would prefer it as is it, but maybe something speaks against it.

Let me know what you think.

OK I've pinged Eli - I really am not expert enough to know about the differences between the collations as to how important it is :/ My gut is that to have things standardized is better but I will reserve final judgement until I hear from the expert :)

I strongly hope we can force the conversion to either utf8 or utf8mb4 depending on utf8mb4 support for the Joomla! Core tables, because everything else would require to check the database similar to what I wanted to do in my old PR with enancing the schema manager.

Updated test description in advance to my changes coming soon. Please do not test yet.

OK So just talked to Eli. His opinion is that as per the comments http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci/766996#766996 That using unicode is much better than general. However let's leave that for another PR. For this PR all that is important is that we don't mind changing any existing collations in tables

@richard67 thank you for the awesome job you did on this.

@aschkenasy Hmm, it is not finished yet. Or do you maybe mix me up with the guy who fixed the forced logout / session problem?

@Richard no, I mean you. Even though the issue isn't fully resolved I wanted to express my appreciation for the work you've put in.

Ah, thanks. Well there is still something to do.

This PR has received new commits.

CC: @810, @anibalsanchez, @infograf768, @Stevec4, @Webdongle

Ready to test again ?

With Joomla! Update component or Extension Installer: Yes.

With the method of George (unpack zip of the branch): Not yet.

In opposite to the branch, the zips already contain latest changes on staging for the session/logout problem, so updating succeeds now with success.

Then go to Extensions -> Manager -> Database

You will see that the conversion to UTF-8 or UTF-8 Multibyte (utf8mb4) has not been done yet.

After using the Fix button either all is OK, or you see this again plus some other database problem. In this case then Fix again -> other problem gone, then Fix again -> all done, database ok, 86 or so changes checked.

@wilsonge Now I am ready, but I have the problem that my update zip contain latest staging changes for session problem, while my branch not, so using your test method will behave much different and som require complicated description.

I can now either do a rebase of my branch, or provide an own zip for full installation, and you could compare it with current staging (right now) and see it differs only by my changes.

But since the last rebase mess I am a bit scared of that.

Would following procedure be correct:

In GitHub for Windows client have my branch selected as actual branch.

Open in git shell.

Then in git shell:

git fetch upstream
git rebase upstream/staging
git push origin correct-utf8mb4-conversion --force

Would that be correct?

Or should I better make an own zip? Or maybe make new PR?

Please advice.

Updated test description

Just play safe and merge in staging rather than rebase. I can deal with the impact of that when merging :)

So

git fetch upstream
git merge upstream/staging
git push origin correct-utf8mb4-conversion

Although what you've written there for the rebase looks fine to me....

So this results then in a zip for my branch being latest staging plus my changes?

correct :)

@wils Btw. when I was testing, test with Joomla Update! went smooth for database with utf8mb4 support, but for the one without, only Joomla Update! worked, not the Extension installer. It ended with an internal server error 500. Could be related to my host (is cheap shared 1and1 hosting), or to the session/logout issue fix nor working somehow, or another problem. Your test method I have not tested yet.

This PR has received new commits.

CC: @810, @anibalsanchez, @infograf768, @Stevec4, @Webdongle

@wilsonge

git fetch upstream
git merge upstream/staging
git push origin correct-utf8mb4-conversion

Done.

@wilsonge Hmm, there is again this composer.lock included in this merge commit. Hope it will not make trouble.

No - that's to be expected. Looks fine to me :)

Changed test description again, now ready for new tests.

@810 @anibalsanchez @infograf768 @Stevec4 @Webdongle

Could you test it again?

And @ghazal @aschkenasy @wilsonge and whoever has time and mood, too?

If so, please note the changed test description.

Thanks in advance.

@richard67 I receive a 1146 error when pressing the fix button.#__utf8_conversion doesn't exist
I confirm it is not in the DB using the c0m_patchtester to apply the pr
Steve

@Stevec4 How did you test? Joomla! Update Component? Or Extension Installer? Or George's method with unzipping the zip from this branch?

extension installer

I am going to update to the latest staging build and try again

@Stevec4 Please follow the test instructions: Update a 3.4.8 to this patched Beta 2 from my PR, and not use latest staging and patchtester. There is no update path from beta to beta.

Will do

I have tested this item successfully on 56861a9

Updated again to make it more clear, e.g. by bold letters:

This is not to be tested with patch tester on latest staging, this is a test of updating a clean 3.4.8.

@810 Thanks for testing. Just for the records: You tested again with database server version 5.7.11, right? I make a list of all test to be sure we cover both kinds of databases.

5.7.11 yes, i will try 5.7.9 again. if its still failing

Also described a bit more in the Summary.

5.7.9 is now also working

To all who use the alternative test method provided once by @wilsonge

I have updated again test description by details for this method, see new item "Important if you tested before with this method". Please read and mention.

@810 Thanks, noted it. Ah, almost forgotten: Did you use Joomla Update Component and my custom URL? Or uploaded and installed my update zip with Extensions Installer? Or used the 3rd method?

@wilsonge Hmm, I just see it can be that your test method does nto work yet. But the update component and extension installer will and do here.

The reason is that your method does not run the update SQL, the updater and the extension installer do, and so my new table for status check does not exists with your method, so it fails.

I suggest to remove your test method from the test description and see that we get the other methods tested and merged then, and for your thing maybe make a new PR.

What do you think?

Please advice.

To all: I just see there is a problem with the test method not using the Update Component or the extension installer but just unzipping the files.

Please test with the Jooma! Updater or the Extension Installer as I described as first alternative, and not use the second alternative from @wilsonge with zipping yourself for testing.

I update the test description to make that clear, too.

Tested by updating with http://test5.richard-fath.de/Joomla_3.5.0-beta2-Beta-Update_Package_test2.zip
Clicked 'fix' on both database errors
This time there is no #__mysql_utf8mb4_test table

Is that a pass or a fail ?

i tried the xml and extension upload

@Webdongle Yes, I gave it a better name.

@810 @wilsonge @Webdongle Sorry, new commit coming soon to fix the alternative test method.

already installing 3.4.8 again

This PR has received new commits.

CC: @810, @anibalsanchez, @infograf768, @Stevec4, @Webdongle

@richard67 good test

Database Version 5.6.28-log
Database Collation utf8_general_ci
Database Connection Collation utf8mb4_general_ci
PHP Version 5.6.18
Web Server Apache
WebServer to PHP Interface cgi-fcgi
Joomla! Version Joomla! 3.5.0-beta2 Beta [ Ember ] 27-January-2017 23:00 GMT

I have tested this item successfully on 2f477f7

Successful test using the following

Database Version 5.6.28-log

Database Collation utf8_general_ci

Database Connection Collation utf8mb4_general_ci

PHP Version 5.6.18

Web Server Apache

WebServer to PHP Interface cgi-fcgi

Joomla! Version Joomla! 3.5.0-beta2 Beta [ Ember ] 27-January-2017 23:00 GMT

Changed description to reflect latest changes for the hardcore update method (by George).

@Stevec4 Thanks for quick test. Which method did you use? Joomla! Update, Extensions Installer, or the method by George?

url and zip ready to test ? and what am I looking for ?

extension installer

@Webdongle Your test was a pass, but please test again after previous commit.

@Webdongle all ready for test. Read the test description again if not done since a while. Regarding your last test: The modules_menu table shoudl also have been converted and at the end have collation utf8mb4_general_ci, your screenshot shows it was not. Check again after next test if all core tables have been converted.

If it is not converted again, we can see later if this will result in another new PR and this one is OK because it solves at least that it did not work at all before.

@wilsonge What do you think?

Run it again via Joomla Update custom url ... The modules_menu table is still utf8_general_ci


Tried the 'fix' button again ... no change. Looks like a fail ?

Addendum
The nj6x3_utf8_conversion table shows Converted 2

@Webdongle Ahhhhh, the modules_menu tables does not contain any text column, only 2 numeric id columns, so this could be ok maybe.

Otherwise if an error had occurred, it would have been shown.

So I think this is a good test, but am not 100% sure. Let's ask experts:

@wilsonge @aschkenasy Can you check this? Whe a table does not have any text columns, can it be on some mysql databases that the "ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci" statement is not applied?

@Webdongle Rergarding the value of 2: 2 means that conversion was made to utf8mb4. On databases not supporting this, conversion is done to utf8. In this case the value is 1. So when later data is migrated from such an older to a newer server which support utf8mb4, the schema manager will say "I want it to be 2" because utf8mb4 supported, but a 1 is found, and so conversion to utf8mb4 is shown as to be done.

sql (renamed .txt) attached if you wish to examine it
j348d.txt

Addendum
Understood about the value '2' ... only mentioned it in case it was relevant.

Not really needed for me. As I see the newsfeeds table, which is converted in the sequence after the modules_menu, has been done, so it was not the hole thing which crashed.

For table finder_taxonomy_map (or so) it's the same: The table does not have any text columns, only 2 integers. Here for me collation is ok, on your screenshot it is not.

It seems that either for tables not having any text columns the database default is used, or that in such cases a "CONVERT TO CHARACTER SET" causes the statement not to have any effect.

Maybe for this reason we will need for every table not only statements like "ALTER TABLE xxx CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci" but in additon also such like "ALTER TABLE xxx DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci"?

Let's see what @aschkenasy says.

@Webdongle Or can you try following:

First enter statement

ALTER TABLE #__modules_menu CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

and check the collation of the table, and then do a

ALTER TABLE #__modules_menu CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

and check again?

(replace "#__" by your db prefix)

Here mysql seems to behave different, so my tables default collations are ok, and I cannot check this.

Ahh, I know what the difference is:

On my system with utf8mb4 support I have already set the default database collation (that default for the whole database I mean, not for single tables) to utf8mb4_general_ci.

So for tables not having any text columns, the database default is used.

@aschkenasy Please confirm.

So from my point of view this is a successful test.

I will update the test instructins to mention that.

@aschkenasy @wilsonge

Shall the conversion also change the default database collation? If not, then this PR is ok, but I have to update test instructions that for tables not having any text columns, only integers, like e.g. modules_menu, the database default is used.

If you need time to think it over we can do that in another future PR, andlet this one here be good.

@Webdongle I think it is like I wrote above, and your test is successful. It's the database default collation shown for the tables not having any TEXT or VARCHAR or other text format columns.

@richard67 installed from URL (http://test5.richard-fath.de/Joomla_3.5.0-beta2-Beta-Update_Package_test2.zip)

Joomla: 3.5.0 staging
php: 5.6.18 (opcache disabled)
MariaDB: 5.5.5-10.0.23
Database default collation: utf8_general_ci

Result of URL install: Update sucessful.
Extensions -> Database shows that utf8mb4 has not been done yet.
Pressed "Fix" and fixed!

Checked database and all tables (joomla core) are now utf8mb4_general_ci (except the new #_utf8_conversion that is utf8_general_ci).

Note: i installed over a already existent DB that before add issues with fix database as i told wilsonge in fcb6d90. I always use the same the db, i just change the joomla files when updating staging.

@andrepereiradasilva Thanks for the test. I just found out that for tables not having any text columns (like the new one) the default database collation is used. This seems to be normal. Just updated test description to reflect that. Also tables not belonging to core are not changed.

So I see your test as successful.

shall i mark as sucessuful? i can make more tests if you like

If you have time and mood you can test Joomla Update! method and also Extension Installer method on a 3.4.8. If you have some server or setup with old mysql versions (5.3 or so) it would also be cool, we need more tests for system which not support utf8mb4.

In this case, if you make more tests, report at the end which methods and database server versions you tested.

But I am very confident to it, tested myself a lot, all methods, and all worked.

So if you wanna sleep or feel lazy you can mark it now.

Thanks.

i only have php 5.6.18 (OS is CentOS 7).

I have joomla 3.4.8 too, so i will test too.

Great. You can find links to zip container (for method "upload and install with extension installer") or to an xml (as custom url for Joomla Update component, can be entered in its options after having selected custom url update channel) you find at the top in the test instructions, if you wanna test these methods, too.

System information

Joomla: 3.4.8 with clean DB
php: 5.6.18 (opcache disabled)
OS: CentoOS 7
MariaDB: 5.5.5-10.0.23
Database Collation: utf8_unicode_ci
Database Connection Collation: utf8mb4_general_ci

Method used

Your custom URL update method in Joomla Update.

Process

Did a Joomla update.

Joomla Version Update Status
Your site has been successfully updated. Your Joomla version is now 3.5.0-beta2.

All tables are still utf8_general_ci.

Then gone to Extensions > Database > Fix

Database table structure is up to date.
Other Information
Database schema version (in #__schemas): 3.5.0-2016-02-26.
Update version (in #__extensions): 3.5.0-beta2.
Database driver: mysqli.
86 database changes were checked successfully.
147 database changes did not alter table structure and were skipped.

No warning shows.

Pressed "Fix" anyway

No change.

So, no conversion. Is this as supposed?

UPDATE: See updated results below

Yes, because MariaDB is officially not supported by Joomla! and has an issue with getting the correct version number on windows hosts, see #9062 . This leads to utf8mb4 support not being dectected correctly on MariaDB, so it is always using utf8 (without mb4). Should also be fixed but is not an issue caused by or related to this PR here.

So I consider your test as successful.

@andrepereiradasilva but at the first time after the successful update when you go to the Extensions -> Manage -> Database view, there should be shown that the conversion has to be done, in this case to UTF-8, not UTF-Multibyte (utf8mb4). Maybe you have to clear broswer cache before going to that view again? Or did you see this and meant in your previous comment that there was just no other warning shown?

Maybe you can do a "SELECT * FROM #__utf8_conversion" (with your db prefix of course)?

It should show 1 record with 1 column only, and this shoudl have value 1 on your db (and 2 for those where utf8 support is detected).

System information

Joomla: 3.2.7 with clean DB
php: 5.6.18 (opcache disabled)
OS: CentoOS 7
MariaDB: 5.5.5-10.0.23
Database Collation: utf8_unicode_ci
Database Connection Collation: utf8mb4_general_ci

Method used

Your custom URL update method in Joomla Update.

Process

Did a Joomla update.

Joomla Version Update Status
Your site has been successfully updated. Your Joomla version is now 3.5.0-beta2.

(got a blank page after update, but logged again and ignored it)
All tables are still utf8_general_ci.

Then gone to Extensions > Database > Fix

Warning: Database is not up to date!
1 Database Problem Found.
The Joomla! Core database tables have not been converted yet to UTF-8 Multibyte (utf8mb4).
Other Information
Database schema version (in #__schemas): 3.5.0-2016-02-26.
Update version (in #__extensions): 3.5.0-beta2.
Database driver: mysqli.
85 database changes were checked successfully.
147 database changes did not alter table structure and were skipped.

Warning shows

Pressed "Fix".

1 Database Problem Found.
Table '#_user_profiles' does not have column 'profile_value' with type 'TEXT'. (From file 3.3.4-2014-08-03.sql.)

Checked database and a lot of tables are not converted to utf8mb4.

Pressed "Fix" again.

Database table structure is up to date.
Other Information
Database schema version (in #__schemas): 3.5.0-2016-02-26.
Update version (in #__extensions): 3.5.0-beta2.
Database driver: mysqli.
86 database changes were checked successfully.
147 database changes did not alter table structure and were skipped.

Checked database and still a lot of tables still are not converted to utf8mb4.

in the 3.5.0 staging test: SELECT * FROM #__utf8_conversion
Converted: 2

in the 3.4.8 test: SELECT * FROM #__utf8_conversion
No results (but the table is there)

in the 3.2.7 test: SELECT * FROM #__utf8_conversion
Converted: 2

I have tested this item successfully on 2f477f7

Success 3.4.8 updated via Joomla update custom url
PHP Built On Windows NT MY-COMPUTER 6.2 build 9200 (Windows 8 Home Premium Edition) i586
Database Version 5.6.26
Database Collation utf8_general_ci
Database Connection Collation utf8mb4_general_ci
PHP Version 5.5.28
Web Server Apache/2.4.16 (Win32) OpenSSL/1.0.1p PHP/5.5.28

@andrepereiradasilva but at the first time after the successful update when you go to the Extensions -> Manage -> Database view, there should be shown that the conversion has to be done, in this case to UTF-8, not UTF-Multibyte (utf8mb4). Maybe you have to clear broswer cache before going to that view again? Or did you see this and meant in your previous comment that there was just no other warning shown?

I got no warning, just whatt i wrote.

UPDATE:
Wait i logged agiain and there is the warning now! (in the 3.4.8 test)

Warning: Database is not up to date!
1 Database Problem Found.
The Joomla! Core database tables have not been converted yet to UTF-8 Multibyte (utf8mb4).

Pressed "Fix"

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

Checked database and a lot of tables are not converted to utf8mb4.

Pressed "Fix" again.

Database table structure is up to date.
Other Information
Database schema version (in #__schemas): 3.5.0-2016-02-26.
Update version (in #__extensions): 3.5.0-beta2.
Database driver: mysqli.
86 database changes were checked successfully.
147 database changes did not alter table structure and were skipped.

Checked database and still a lot of tables still are not converted to utf8mb4.

In other words the same result as the 3.2.7 test.

in the 3.4.8 test: SELECT * FROM #__utf8_conversion
NOW show: Converted: 2

Tables not converted in 3.4.8 and 3.2.7 tests

#_banner_tracks
#_content_frontpage
#_finder_links_terms0
#_finder_links_terms1
#_finder_links_terms2
#_finder_links_terms3
#_finder_links_terms4
#_finder_links_terms5
#_finder_links_terms6
#_finder_links_terms7
#_finder_links_terms8
#_finder_links_terms9
#_finder_links_termsa
#_finder_links_termsb
#_finder_links_termsc
#_finder_links_termsd
#_finder_links_termse
#_finder_links_termsf
#_finder_taxonomy_map
#_modules_menu
#_ucm_base
#_update_sites_extensions
#_user_usergroup_map
#_utf8_conversion
#_weblinks (this only in 3.2.7, doesn't exist in 3.4.8)

@andrepereiradasilva Ok, 3.4.8 was cache issue maybe, it is recommended to refresh broswer cache after an update.

Regarding number of tables being converted:

Only Joomla! Core tables which contain text or varchar or such columns are converted, table containing numeric columns only not, like e.g. the modules_menu table, or finder_taxonomy_map.

Extensions tables will also not be updated, even if not 3rd patrty, so patchtester and weblinks will be untouched.

See test instructions and summary of changes above.

#_banner_tracks
#_content_frontpage
#_finder_links_terms0
#_finder_links_terms1
#_finder_links_terms2
#_finder_links_terms3
#_finder_links_terms4
#_finder_links_terms5
#_finder_links_terms6
#_finder_links_terms7
#_finder_links_terms8
#_finder_links_terms9
#_finder_links_termsa
#_finder_links_termsb
#_finder_links_termsc
#_finder_links_termsd
#_finder_links_termse
#_finder_links_termsf
#_finder_taxonomy_map
#_modules_menu
#_ucm_base
#_update_sites_extensions
#_user_usergroup_map
#_utf8_conversion

I would guess those contain only numerical columns, but have not tested all of them.

#_weblinks

is not core is an extension

So your check looks good to me, I would not expect more.

So your check looks good to me, I would not expect more.

Good! just checking. :)

Ionly stranged because in my first 3.5.0 all got converted.
But will do a clean 3.5.0 staging to check if performs like 3.2.7 and 3.4.8.
5 minutes.

No something seems wrong :( https://github.com/joomla/joomla-cms/pull/9221/files#diff-a85cd4073fd85d63a651557b2b3465aeR58 this should have converted the tables to utf8mb4

@andrepereiradasilva Ha, if you install a staging, the tables are already utf8mb4_general_ci collated, see installation/sql/joomla.sql, so that's the reason why there all are shown like this.

Is seems that the tables not having text columns have their collation as they were created. It might be done in another PR to add statements to change defualt collation for these, too, but not in this PR here anymore if I can avoid it.

System information

Joomla: 3.5.0 staging with clean DB
php: 5.6.18 (opcache disabled)
OS: CentoOS 7
MariaDB: 5.5.5-10.0.23
Database Collation: utf8_unicode_ci
Database Connection Collation: utf8mb4_general_ci

Method used

Your zip URL update method in Joomla Extensions.

Process

Did a Joomla install from URL

Installation of the file was successful.

*All tables are in utf8mb4_general_ci. #_utf8_conversion conversion table DOESN'T exist. *

Then gone to Extensions > Database > Fix

Error
Table 'database_name.#__utf8_conversion' doesn't exist SQL=SELECT converted FROM #__utf8_conversion WHERE converted = 2;

Warning: Database is not up to date!
2 Database Problem Found.
Table 'zrzy4_utf8_conversion' does not exist. (From file 3.5.0-2016-02-26.sql.)
The Joomla! Core database tables have not been converted yet to UTF-8 Multibyte (utf8mb4).

Other insformation
Database schema version (in #__schemas): 3.5.0-2016-02-26.
Update version (in #__extensions): 3.5.0-beta2.
Database driver: mysqli.
84 database changes were checked successfully.
147 database changes did not alter table structure and were skipped.

Pressed "Fix".

Database table structure is up to date.

Other Information
Database schema version (in #__schemas): 3.5.0-2016-02-26.
Update version (in #__extensions): 3.5.0-beta2.
Database driver: mysqli.
86 database changes were checked successfully.
147 database changes did not alter table structure and were skipped.

*All tables are in utf8mb4_general_ci. #_utf8_conversion conversion table EXISTS (No records). *

ok so all good, right? Should i now mark as tested sucessufully?

@wilsonge These tables not have any text columns so there is nothing to convert.

Mysql knows another statement "ALTER TABLE ... DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci", which is a different statemtn than a "... CONVERT TO CHARACTER SET", see mysql manuals for syntax of alter table statement, see http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

I am pretty sure that the convert to does nothing if nothing to convert, but the default character set sets the default for the tbale, which then is also used when new text columns are added.

@andrepereiradasilva Can you try?

On a table not being converted and having only numerical columns, like "#_utf8_conversion" try the first kind of statement

ALTER TABLE #_utf8_conversion CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

If this is still collation utf8_general_ci, try this one:

ALTER TABLE #_utf8_conversion DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

It may be necessary after each step to refresh the mysqladmin broswer page to see changes.

Does this then work?

Let us know the result please.

Thanks in advance.

@andrepereiradasilva From my point of view yes, your last test was good, that's how it works.

In PHPMyAdmin

ALTER TABLE #_utf8_conversion CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Still utf8_general_ci

ALTER TABLE #_utf8_conversion DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Now is utf8mb4_general_ci :)

I manually did that sql command in all tables that had not been converted and all of then got converted.

@wilsonge See @andrepereiradasilva comment just before this one here: It is exactly as I said!!!

He prooved it.

The statement to CONVERT text columns in a table to a certain character set and collation to which you referred and what was intorduced before i worked on that issue and on which I relied to be the right one is another statement than the one for changing a table's DEFAULT character set and collation for text columns being created in future.

See again http://dev.mysql.com/doc/refman/5.7/en/alter-table.html.

I relied on the "MySQL experts" with these statement, not my fault.

Shame for them that an Oracle database user like me who knows nothing about MySQL has to fix all that stuf!!!

Of course I can add those statements to the conversion script, too, and commit this to my PR here. It would simplify test instructions much, but then all guys have to test again.

Or we leave it as it is here and test it in a bit more complicated way (because more discussions) and then add these statements in the next PR.

@wilsonge Please decide.

@wilsonge OK, I decided myself: I will soon do a commit to this PR adding the statements. Just am testing it here before.
Sorry guys you have to test again, but it's the better way and needs to be done.

Last test, going back in time :)

System information

Joomla: 2.5.28 with clean DB
php: 5.6.18 (opcache disabled)
OS: CentoOS 7
MariaDB: 5.5.5-10.0.23
Database Collation: utf8_unicode_ci
Database Connection Collation: utf8mb4_general_ci

Method used

Your zip URL update method in Joomla Extensions.

Process

Did a Joomla install from URL

Result: Blank page with a lot of errors.

Error on updating manifest cache: (type, element, folder, client) = (component, com_mailto, com_mailto, 0)
...
Error on updating manifest cache: (type, element, folder, client) = (plugin, tags, plg_search_tags, 0)
Fatal error: Call to undefined method JSession::isActive() in /path/to/joomla/tmp/install_56d23f3a6c21b/administrator/components/com_admin/script.php on line 1618

(a fatal error on update?? ignored and logged in to admin again and i'm now in 3.5.0-beta2)

All tables are in utf8_general_ci. #_utf8_conversion conversion table EXIST (empty table).

Then gone to Extensions > Database > Fix

Warning: Database is not up to date!
1 Database Problem Found.
The Joomla! Core database tables have not been converted yet to UTF-8 Multibyte (utf8mb4).

Other information
Database schema version (in #__schemas): 3.5.0-2016-02-26.
Update version (in #__extensions): 3.5.0-beta2.
Database driver: mysqli.
86 database changes were checked successfully.
149 database changes did not alter table structure and were skipped.

Pressed "Fix".

Warning: Database is not up to date!
2 Database Problem Found.
Table 'ewrus_user_profiles' does not have column 'profile_value' with type 'TEXT'. (From file 2.5.28.sql.)
Table 'ewrus_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-2016-02-26.
Update version (in #__extensions): 3.5.0-beta2.
Database driver: mysqli.
85 database changes were checked successfully.
149 database changes did not alter table structure and were skipped.

Most tables are in utf8mb4_general_ci (only the ones reported above aren't). #_utf8_conversion conversion table EXIST (converted: 2).

Pressed "Fix" again.

Database table structure is up to date.

Database schema version (in #__schemas): 3.5.0-2016-02-26.
Update version (in #__extensions): 3.5.0-beta2.
Database driver: mysqli.
87 database changes were checked successfully.
149 database changes did not alter table structure and were skipped.

Most tables are in utf8mb4_general_ci (only the ones reported above aren't). #_utf8_conversion conversion table EXIST (converted: 2)

@richard67 thank you a lot for all your hard work on this. I know it's not easy and it's time consuming.

@andrepereiradasilva Thanks for your many tests. For me they are successful, errors on the 2.5 are not related to this PR and would also happen without the utf8mb4 stuff.

But sorry, I am just preparing a new commit and updated zip files, which should result in ALL cotre tables to end with utf8mb4_general_ci collation, also thise not having text columns.

I will update test instructions soon, will be shorter a bit then and easier.

Hope it will work.

For me they are successful, errors on the 2.5 are not related to this PR and would also happen without the utf8mb4 stuff.

Yeah i know, just warning that fatal error to others more session related (if they don't know).

Sure, i will rebuild my clean installs to do the 3 tests again when you are ready (2.5.28, 3.2.7 and 3.4.8).

This PR has received new commits.

CC: @810, @anibalsanchez, @infograf768, @Stevec4, @Webdongle

@810, @anibalsanchez, @infograf768, @Stevec4, @Webdongle @andrepereiradasilva @wilsonge

My zip update container is already updated to last commit, so as soon as travis ok you can test again.

My last change will result in all core tables having utf8mb4_general_ci default collation, also those not aving any text but only numerical columns, so when new TEXT columns are added in future, they will be utf8mb4 with utf8mb4_general_ci collation if nothing else (e.g. utf8mb4_bin colation) is specified when creating the column.

I will update the test instructions, they should become a bit easier now.

Adapted test instructions to latest changes

Update from 3.4.8 test

System information

Joomla: 3.4.8 with clean DB
php: 5.6.18 (opcache disabled)
OS: CentoOS 7
MariaDB: 5.5.5-10.0.23
Database Collation: utf8_unicode_ci
Database Connection Collation: utf8mb4_general_ci

Method used

Extension > Install from uplaod (you custom zip).

Process

Did the update.

Installation of the file was successful.

All tables are still utf8_general_ci. #_utf8_conversion EXISTS (no results).

Then gone to Extensions > Database > Fix

Warning: Database is not up to date!

1 Database Problem found.
The Joomla! Core database tables have not been converted yet to UTF-8 Multibyte (utf8mb4).

Other information
Database schema version (in #__schemas): 3.5.0-2016-02-26.
Update version (in #__extensions): 3.5.0-beta2.
Database driver: mysqli.
85 database changes were checked successfully.
147 database changes did not alter table structure and were skipped.

Pressed "Fix".

Warning: Database is not up to date!

1 Database Problem found.
Table 'prq5m_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-2016-02-26.
Update version (in #__extensions): 3.5.0-beta2.
Database driver: mysqli.
85 database changes were checked successfully.
147 database changes did not alter table structure and were skipped.

All tables are utf8mb4_general_ci. #_utf8_conversion EXISTS (Converted: 2).

Pressed "Fix" again.

Database table structure is up to date.

Other Information
Database schema version (in #__schemas): 3.5.0-2016-02-26.
Update version (in #__extensions): 3.5.0-beta2.
Database driver: mysqli.
86 database changes were checked successfully.
147 database changes did not alter table structure and were skipped.

All tables are utf8mb4_general_ci. #_utf8_conversion EXISTS (Converted: 2).

So, conversion done!

Update from 3.2.7 test

System information

Joomla: 3.2.7 with clean DB
php: 5.6.18 (opcache disabled)
OS: CentoOS 7
MariaDB: 5.5.5-10.0.23
Database Collation: utf8_unicode_ci
Database Connection Collation: utf8mb4_general_ci

Method used

Extension > Install from upload (you custom zip).

Process

Did the update.

(got logged out after update, but logged again and ignored it)

All tables are still utf8_general_ci. #_utf8_conversion EXISTS (no results).

Then gone to Extensions > Database > Fix

Warning: Database is not up to date!

1 Database Problem found.
The Joomla! Core database tables have not been converted yet to UTF-8 Multibyte (utf8mb4).

Other information
Database schema version (in #__schemas): 3.5.0-2016-02-26.
Update version (in #__extensions): 3.5.0-beta2.
Database driver: mysqli.
85 database changes were checked successfully.
147 database changes did not alter table structure and were skipped.

Pressed "Fix".

Warning: Database is not up to date!

1 Database Problem found.
Table 'vvcp2_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-2016-02-26.
Update version (in #__extensions): 3.5.0-beta2.
Database driver: mysqli.
85 database changes were checked successfully.
147 database changes did not alter table structure and were skipped.

All tables are utf8mb4_general_ci. #_utf8_conversion EXISTS (Converted: 2).

Pressed "Fix" again.

Database table structure is up to date.

Other Information
Database schema version (in #__schemas): 3.5.0-2016-02-26.
Update version (in #__extensions): 3.5.0-beta2.
Database driver: mysqli.
86 database changes were checked successfully.
147 database changes did not alter table structure and were skipped.

All tables are utf8mb4_general_ci. #_utf8_conversion EXISTS (Converted: 2).

So, conversion done!
Some results as 3.4.8.

@andrepereiradasilva That's a valid and successful tests (happy).

And if proofs to @wilsonge that I am right with what I say about the different statements ALTER TABLE .. CONVERT CHARACTER SET ... " and "ALTER TABLE ... DEFAULT CHARACTER SET ..."

Until a few day ago I did not know anyting aybout MySQL, had to research all.

And I do not speak any PHP. So that really was not an easy thing for me.

The Oracle SQL I am used to allows a bit more than MySQL does.

So now I feel a bit as having slept in a running washing machine now.

yeah in my machine the test are sucessufully and i will mark as so.
I will still test 2.5.28 again and post the results

@richard67
Swapping from one computer product is much like swapping from a car to a van of a different make. Both do much the same thing but all the switches have different symbols

Shame for them that an Oracle database user like me who knows nothing about MySQL has to fix all that stuf!!!

Dude do you want to chill with the passive aggressive? There is only one expert on mysql in the room which is Eli. I know pretty much about as much as you did when you start on mysql. The last statement I made was entirely on me and not anyone else. As several other people said in here thankyou for doing this though - it's really really appreciated that you stepped upto the plate and put a huge amount of time into this :)

I've spent today setting up a VM which I can do some MariaDB tests with (the separate maria DB issue said the version detection was only an issue on Windows and not Linux - so I want to check that we are OK here with the utf8mb4 stuff on MariaDB + linux). I'll also test on my regular windows/xampp setup tomorrow when I'm up :)

Update from 2.5.28 test

System information

Joomla: 2.5.28 with clean DB
php: 5.6.18 (opcache disabled)
OS: CentoOS 7
MariaDB: 5.5.5-10.0.23
Database Collation: utf8_unicode_ci
Database Connection Collation: utf8mb4_general_ci

Method used

Extension > Install from upload (you custom zip).

Process

Did the update.

Result: Blank page with a lot of errors.

Error on updating manifest cache: (type, element, folder, client) = (component, com_mailto, com_mailto, 0)
...
Error on updating manifest cache: (type, element, folder, client) = (plugin, tags, plg_search_tags, 0)
Fatal error: Call to undefined method JSession::isActive() in /path/to/joomla/tmp/install_56d23f3a6c21b/administrator/components/com_admin/script.php on line 1618
(a fatal error on update?? ignored and logged in to admin again and i'm now in 3.5.0-beta2)

All tables are still utf8_general_ci. #_utf8_conversion EXISTS (no results).

Then gone to Extensions > Database > Fix

Warning: Database is not up to date!

1 Database Problem found.
The Joomla! Core database tables have not been converted yet to UTF-8 Multibyte (utf8mb4).

Other information
Database schema version (in #__schemas): 3.5.0-2016-02-26.
Update version (in #__extensions): 3.5.0-beta2.
Database driver: mysqli.
86 database changes were checked successfully.
149 database changes did not alter table structure and were skipped.

Pressed "Fix".

Warning: Database is not up to date!

2 Database Problem found.
Table 'h328q_user_profiles' does not have column 'profile_value' with type 'TEXT'. (From file 2.5.28.sql.)
Table 'h328q_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-2016-02-26.
Update version (in #__extensions): 3.5.0-beta2.
Database driver: mysqli.
85 database changes were checked successfully.
149 database changes did not alter table structure and were skipped.

All tables are utf8mb4_general_ci. #_utf8_conversion EXISTS (Converted: 2).

Pressed "Fix" again.

Database table structure is up to date.

Other Information
Database schema version (in #__schemas): 3.5.0-2016-02-26.
Update version (in #__extensions): 3.5.0-beta2.
Database driver: mysqli.
87 database changes were checked successfully.
149 database changes did not alter table structure and were skipped.

All tables are utf8mb4_general_ci. #_utf8_conversion EXISTS (Converted: 2).

So, conversion done! Some results as 3.2.7 and 3.4.8.

I got a FAIL ... just going to test again

@wilsonge Sorry, was not my intention to be aggressive, and by far not against you or Eli.

There are other people who sometimes appear as if they knew everything but at the end just dropped in a quick solution which is a crab. I will not tell names now, because it might be that I just misinterpret their behavior. I don't know the people here so well. My problem is that in my job I cleaned up so much shit from so-called gurus that I sometimes remember these times and then get a bit ironic, or however you call it.

But as I said, not meant you or Eli.

Fatal error: Call to undefined method JSession::isActive() in /path/to/joomla/tmp/install_56d23f3a6c21b/administrator/components/com_admin/script.php on line 1618
(a fatal error on update?? ignored and logged in to admin again and i'm now in 3.5.0-beta2)

@wilsonge not reggarding this PR, but did you see my comment above in the 2.5.28 test?

@andrepereiradasilva I did not even have the courage to try to update a 2.5.28.

But if this shall be supported, then this shall work.

Hope someone finds the reason.

Run the 'fix' button again (even though showed no problems) database looks ok now

I have tested this item successfully on 87b8114

The 3 tests reggarding the utf8mb4 convertion i made results in all core tables being converted to utf8mb4_general_ci collation on my server.

@Webdongle

@andrepereiradasilva had a similar issue at one of his tests: The need to run the conversion was not displayed in the database problems list. But when he logged out and in again, it was shown. Could have been some javascipt still hanging in the browser cache?

I always reload backend page after an pdate forcing the browser to not use cache, e.g. on Firefox with + reload button. Or clear broswer cache.

Seems something not related to this PR but to caching.

Do you wanna test again with clear cache before going to the database problem tab?

Or consider your test successful?

I have tested this item successfully on 87b8114

Updated 3.4.8 via Joomla update custom url

Success

The session issue is related to #9230 :)

@Webdongle Ah I see, you decided. Thanks for testing.

@wilsonge One thing to be possibly done when this PR is merged:

After an update the user should be redirected to the database problems display.

Otherwise, if he never checks that, he never will use the "Fix" button.

Or maybe something like the message popping up for the statistics plugin after successful installation?

@wilsonge
Would it not be possible to auto run the fix during update ?

@Webdongle Not really, because depending on which data left from which old Joomla version, it can require several steps, and errors may occur, and it is not predictable if 1 or 2 or 3 times Fix button necessary, this really depends on how old the thing is which you update.

I think it should be viewn by the user.

But you asked George, and of course I ams also curious on his opinion.

Mine is not to automize it.

We should be able to run this manually in the update script I think I'd need to test first which database driver version we are running at that point in com_admin's script.php file - I think it ought to be the most upto date version (i.e. 3.5) in which case we can definitely detect + manually trigger it

@richard67

Not really, because depending on which data left from which old Joomla version, it can require several steps, and errors may occur, and it is not predictable if 1 or 2 or 3 times Fix button necessary,

Not even with an IF ELSE ? ok just a thought. Because you know what many users are like ... they will see the database screen and immediately post on the forum that "the update broke my site"

@Webdongle Then we at least know that posting in the forum still works

Well, an automated solution has to be reliable and resilient against broken database schemes, it has to alert when something goes wrong which canot be fixed automatically ... if all this can be fulfilled we should make it automatic, it not, we should guide the user so he knows what he shall do.

Do we have any update for http://forum.joomla.org/viewtopic.php?f=9&t=898600&p=3362809&#p3367946 ? might stop the natives from revolting

I have tested this item successfully on 87b8114

Lets merge this.

@810 Thanks for testing. Just for the records: You used again a 5.7.11 and a 5.7.9 Mysql server versin, and tested Joomla! Update method and Extesion Installer, was yesterday, right?

Summary on tests up to now

According to my list we have (except me) no tests yet with old mysql versions not supporting utf8mb4.

But we have diverse mysql server version 5.6.x and 5.7.y tested, also MariaDB, with all 3 update methods Joomla! Update (custom URL), Extesion Installer (Upload & Install Package) and the good old hardcore brute force method (unzip new Joomla, kep old configuration.php and old database and run database fix then).

And thanks to @andrepereiradasilva we have tests for updating diverse old Joomla! versions (2.5.28, 3.2.7) in addition to 3.4.8.

I tested 5.7.9/11 with xml and with the installer. Today.

Great, thanks.

We can include this, and we can see it on beta 3 , if there are any issues

Unsubscribe

Most recent test.

Installed a clean 3.4.8
Overwrote with staging + this PR.
Use the Fix button
remained:
Table 'jos_user_profiles' does not have column 'profile_value' with type 'TEXT'. (From file 3.3.4-2014-08-03.sql.)

Clicked the FIx button again and no more errors.

If we have to click twice the Fix button, could we have a message to tell user about it?

@infograf768
The problem with messages users don't often read them and if they do then they might forget what it said when the first 'fix' has completed. Wouldn't it be better for the install to detect ( with IF ELSE in a DO WHILE loop) if the fix needs to be run. Because the 'fix' sometimes needs to be run more than twice ?

Maybe just update the string
COM_INSTALLER_MSG_DATABASE_ERRORS="Warning: Database is not up to date!"

Yes but any error warning (after an update) about the database not being up to date will frighten many inexperienced users. Would it not be better (if possible) to have the install run the 'fix' ?

I have tested this item successfully on 87b8114

Please, note that this time, convertion was stopped by database inconsistencies and I have to fix database inconsistencies to complete it.

Warning: Database is not up to date! 

The Joomla! Core database tables have not been converted yet to UTF-8 Multibyte (utf8mb4).

... Fix ...

Error
Duplicate entry 'http://..../2002-joomla-news-feeds/latest-extensions' for key 'idx_link_old' SQL=ALTER TABLE `#__redirect_links` ADD UNIQUE `idx_link_old` (`old_url`(100));

... Manually clean the table of inconsistencies ... Fix ...

Warning: Database is not up to date! 

Table 'xerci_user_profiles' does not have column 'profile_value' with type 'TEXT'. (From file 3.3.4-2014-08-03.sql.)
The Joomla! Core database tables have not been converted yet to UTF-8 Multibyte (utf8mb4).

... Fix ..

Database table structure is up to date. 

• Database Version 5.5.47-0ubuntu0.14.04.1
• Database Collation latin1_swedish_ci
• Database Connection Collation utf8mb4_general_ci
• PHP Version 5.6.18-1+deb.sury.org~trusty+1
• Database Type MySQLi

  ---

  _{This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9221.}

@andrepereiradasilva
What version did you update from ? Can you reproduce the error ?

@anibalsanchez I see, you helped yourself. Well this data inconsistency comes from having 2 records with the same old URL in the redirect links. Such errors would have come with and without this patch, I think that's why you marked your result as success.

@wilsonge regarding ready to commit: Just when I was in bed I rememberd I maybe have a little issue in this PR, but it could be fixed in a succeeding PR if we not wanna let all the guys re-test.

I queue the messages of exceptions in the database model of the installer, found that code somewhere, but I never flusg this queue or orfce to show the messages or whatever. This can maybe be the reason why it needed a logout / login or cache refresh in one of @andrepereiradasilva ' tests to see the message that the conversion is to be done?

@richard67 Yes, it worked as expected. However, conversion detects MySQL schema inconsistencies and it would be great to have a wiki/documentation page to guide users in the conversion.

From a user point of view, it is not obvious why a Joomla upgrade optionally leads to a utf8mb4 conversion that must be "fixed" to be completed and it may detect schema inconsistencies.

@Webdongle i updated from 3.4.8, 3.2.7 and 2.5.28, as you can see in my comment above.

The fatal error has with 2.5.28 -> 3.5.0 beta2

@andrepereiradasilva regarding the 2.5.28 updateing: there is a new PR #9240 dealing with that, maybe you can help with test there?

already doing that ;)

@anibalsanchez I absolutely agree with you that there must be some documentation somewhere about what to do after an update or, if this database check is automated somehow, how to understand the possible error messages.

There would be still time to do that between a Beta 3 and an RC with anew PR, but not after RC because language freezen then, so I hope Beta 3 will not be skipped so we can work on that.

If you have any good ideas what could be done, let us know.

I could like something like currently the statistics plugin does, to show the user at the end of an update immediately a screen where he can see if all ok and or something has to be done.

This screen also could be the existing database problems view, just a redirect to there at the end of installation could be made, so the installation success or error messages are still shown at the top, also the statistics plugin thing, and below this then the database check and a link to a wiki what to do if some problems shown.

@andrepereiradasilva I already downloaded a 2.5.28 so I can help there later, too, if necessary.

@richard67 The current upgrade path has one BIG benefit... 99% sites are not going to be forced to convert the database to utf8mb4. Then, if everything works well, it can be "suggested" to all sites in Joomla 3.6.

@anibalsanchez Also agree, but currently it is suggested by the db schema manager view as soon as he/she opens that. So maybe still something to do wit a new PR after this one is merged?

I have not tested this item.

Successfully tested extension install method and vis the patch tester
Database Version 5.6.28-log

Database Collation utf8_general_ci

Database Connection Collation utf8mb4_general_ci

PHP Version 5.6.18

@Stevec4 Not tested? Or successfully tested?

that should be successfully tested

Do you still need tests ?
Which config would be interesting for you (joomla version, php version, mysql version, whatever) ?

@Stevec4 Forgotten to check the status on the issue tracker using "Test this" button?

Need more coffee!!. is there a way to edit it I don't see it? or just resubmit?

@ghazal we could need test with a mysql server not supporting utf8mb4, e.g. a 5.3.x. or 5.4.y.

All test methods would be nice to be tested, i.e. Joomla Update component with my custom URL, install like a package my zip, and optionally also use George's hardcore method.

@Stevec4 In the PR on GitHub I don't know, but in the issue tracker when you push button "Test" or so left top corner, and have the form then to fill in the optinal text, there is check boxes for status. Pre-selected is "I have not tested this item." you can change then so success or failed

ok I will re submit it I have successfully test on mairaDB as well

I do not like MariaDB so much .. it sounds so catholic, and I am protestant ....

I have tested this item successfully on 87b8114

2 successful tests

Test 1 Extension installer & patch tester

Database Version 5.6.28-log
Database Collation utf8_general_ci
Database Connection Collation utf8mb4_general_ci

PHP Version 5.6.18

Test 2 Extension installer & patch tester

Database Version 5.5.5-10.1.10 MariaDB
Database Connection Collation utf8mb4_general_ci
PHP Version 5.6.15

I have tested this item successfully on 87b8114

Detail :
joomla 3.4.3 -> 3.4.8
then
3.4.8 -> 3.5.0 beta2

Database Version 5.5.38
Database Collation utf8_general_ci
PHP Version 5.4.31
Tested on 2 sites with this config

1st - Your version via Upload & Install Joomla Extension
2nd - via joomla update and URL specifique (your xml file)

In both cases
The Joomla! Core database tables have not been converted yet to UTF-8 Multibyte (utf8mb4).

FIX

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

FIX AGAIN
OK
Database schema version (in #__schemas): 3.5.0-2016-02-26

So we have a lot of good tests for databases with UTF8MB4 support, but none yet (beside mine which do not count) for such databases without.

Required mysql server version would be lower than 5.5.3 for this test.

Anyone has such an old mysql database server?

I couldn't find any image with MySQL 5.1.
On Feb 28, 2016 1:32 PM, "Richard Fath" notifications@github.com wrote:

So we have a lot of good tests for databases with UTF8MB4 support, but
none yet (beside mine which do not count) for such databases without.

Required mysql server version would be lower than 5.5.3 for this test.

Anyone has such an old mysql database server?

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

@anibalsanchez You also could use an new mysql server but an old PHP version with a mysql client library not suppoorting UTF8MB4, e.g mysqlnd lower than 5.0.9 or libmysql lower than 5.5.3. This would also be a valid test for non-utf8mb4.

I have xampp, wamp and unicontroler as localhosts. Is there anyway I can configure them not to use UTF8MB4 ? Don't mind editing a few files or changing them as I can always return them back but nothing too complicated.

You could hack utf8mb4 detection in the mysqli database driver, but no need to do that. Let @wilsonge decide or maybe test himself.

So or so thanks for patience with many commits causing need to test again, and for testing.

Trying to set up a mysql 5.1 install now

@richard67 @andrepereiradasilva

Updated 2.5.28 to 3.5.0 beta2 full success
Used the Extension manager install because the Joomla component could not fin the update(don't think 2.5.28 is in your update xml ?)

Right, 2.5.28 is not in my XML.

OK I think I have a mysql 5.1 vagrant box going (it's repeatedly crashing doing tasks tho :/)

What's the most important thing to test before my box blows up?

Do Joomla! Update either using my custom URL or my zip for upload, of do it like you diescribed. Experience has shown that if some prolem occcur, they occurred with your method. See my test description at the top of this PR.

If using Jomla Updater or Extensio Installer, after successful update goto Extenions -> Manager -> Database. It should show as a database prolem that conversion to UTF-8, i.e not UTF8-8 Multibyte (utf8mb4) has to be done, and using the Fix button should solve this and all be ok (possibly after another 1 or 2 steps to Fix, depending what you updated.

I've not participated to this repository before but can confirm that this is working in PHP7.0.3, MariaDB 10.1.12 on Windows.

Datenbankversion 5.5.5-10.1.12-MariaDB
Datenbankzeichensatz utf8_general_ci
Database Connection Collation utf8mb4_general_ci
PHP-Version 7.0.3
Webserver Microsoft-IIS/10.0
PHP-Interface für den Webserver cgi-fcgi
Joomla!-Version Joomla! 3.5.0-beta2 Beta [ Ember ] 27-January-2017 23:00 GMT
Joomla!-Plattform-Version Joomla Platform 13.1.0 Stable [ Curiosity ] 24-Apr-2013 00:00 GMT

Before running the database fix shows the following error:

After one run everything is fine.

I'll do a second test on an outdated linux server not capable of utf8mb4 soon and add a second comment to this thread

@swiffer Yes, is expected behavior, this error shown. It shall force the user to click the Fix button to run the conversion.

@swiffer @wilsonge As mentioned in this PR's test instructions (unfortunately a bit long and so not easy to read maybe), there is no conversion to utf8mb4 on systems not supporting this, and to utf8 is maybe no change. But you can test columns named "alias" of tables like categoes, menu. They have a lenght of 400 after the conversion. You cal anso check all indexes where this columns go in, e.g. "idx_alias" of same tables. The columns should go nto the index unly with the first 100 characters.

OK So I did the upgrade path through Joomla Update, mysql crashed after redirect (due to instability of my box) so attempted to run the database fixer for the remaining issues:

After nearly 30 attempts of mysql crashing (again instability of my vagrant box) finally got to:

Running

mysql> show create table dev3_content;

returned:

CREATE TABLE `dev3_content` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `asset_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'FK to the dev3_assets table.',
  `title` varchar(255) NOT NULL DEFAULT '',
  `alias` varchar(400) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `introtext` mediumtext NOT NULL,
  `fulltext` mediumtext NOT NULL,
  `state` tinyint(3) NOT NULL DEFAULT '0',
  `catid` int(10) unsigned NOT NULL DEFAULT '0',
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_by` int(10) unsigned NOT NULL DEFAULT '0',
  `created_by_alias` varchar(255) NOT NULL DEFAULT '',
  `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_by` int(10) unsigned NOT NULL DEFAULT '0',
  `checked_out` int(10) unsigned NOT NULL DEFAULT '0',
  `checked_out_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `publish_up` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `publish_down` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `images` text NOT NULL,
  `urls` text NOT NULL,
  `attribs` varchar(5120) NOT NULL,
  `version` int(10) unsigned NOT NULL DEFAULT '1',
  `ordering` int(11) NOT NULL DEFAULT '0',
  `metakey` text NOT NULL,
  `metadesc` text NOT NULL,
  `access` int(10) unsigned NOT NULL DEFAULT '0',
  `hits` int(10) unsigned NOT NULL DEFAULT '0',
  `metadata` text NOT NULL,
  `featured` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Set if article is featured.',
  `language` char(7) NOT NULL COMMENT 'The language code for the article.',
  `xreference` varchar(50) NOT NULL COMMENT 'A reference to enable linkages to external data sets.',
  PRIMARY KEY (`id`),
  KEY `idx_access` (`access`),
  KEY `idx_checkout` (`checked_out`),
  KEY `idx_state` (`state`),
  KEY `idx_catid` (`catid`),
  KEY `idx_createdby` (`created_by`),
  KEY `idx_featured_catid` (`featured`,`catid`),
  KEY `idx_language` (`language`),
  KEY `idx_xreference` (`xreference`)
) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=utf8 |

So I think this is a successful test :/

@wilsonge Yes, I would count this as successful.

There is an additional feature of this PR, if you want you can test this, too:

If you now export this database and import it into a never mysql server which supports utf8mb4, and then login at the backend and go to the database problems display again, then you should see again a message about conversion to be done, but this time for "UTF-8 Multibyte (utf8mb4)", and if you use the Fix button, again, it should be fixed, and then you can see all core tables will have utf8mb4_general_ci collation, and these alias columns will have collation utf8mb4_bin.

I did a second test on the not utf8mb4 capable site as mentioned in my previous post. After uploading modified Files from your latest zip i got the following errors shown in database section in backend.

clicking fix once solved all database errors from joomla. it took 5 minutes on the server to complete these changes so this process could be stopped by max_execution_time on some servers...

    CREATE TABLE `jml_content` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `asset_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'FK to the #__assets table.',
 `title` varchar(255) NOT NULL DEFAULT '',
 `alias` varchar(400) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
 `introtext` mediumtext NOT NULL,
 `fulltext` mediumtext NOT NULL,
 `state` tinyint(3) NOT NULL DEFAULT '0',
 `catid` int(10) unsigned NOT NULL DEFAULT '0',
 `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `created_by` int(10) unsigned NOT NULL DEFAULT '0',
 `created_by_alias` varchar(255) NOT NULL DEFAULT '',
 `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `modified_by` int(10) unsigned NOT NULL DEFAULT '0',
 `checked_out` int(10) unsigned NOT NULL DEFAULT '0',
 `checked_out_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `publish_up` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `publish_down` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `images` text NOT NULL,
 `urls` text NOT NULL,
 `attribs` varchar(5120) NOT NULL,
 `version` int(10) unsigned NOT NULL DEFAULT '1',
 `ordering` int(11) NOT NULL DEFAULT '0',
 `metakey` text NOT NULL,
 `metadesc` text NOT NULL,
 `access` int(10) unsigned NOT NULL DEFAULT '0',
 `hits` int(10) unsigned NOT NULL DEFAULT '0',
 `metadata` text NOT NULL,
 `featured` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Set if article is featured.',
 `language` char(7) NOT NULL COMMENT 'The language code for the article.',
 `xreference` varchar(50) NOT NULL COMMENT 'A reference to enable linkages to external data sets.',
 PRIMARY KEY (`id`),
 KEY `idx_access` (`access`),
 KEY `idx_checkout` (`checked_out`),
 KEY `idx_state` (`state`),
 KEY `idx_catid` (`catid`),
 KEY `idx_createdby` (`created_by`),
 KEY `idx_featured_catid` (`featured`,`catid`),
 KEY `idx_language` (`language`),
 KEY `idx_xreference` (`xreference`)
) ENGINE=InnoDB AUTO_INCREMENT=831 DEFAULT CHARSET=utf8

and finally some details about server environment

Datenbankversion 5.1.73-1
Datenbankzeichensatz utf8_general_ci
Database Connection Collation utf8_general_ci
PHP-Version 5.6.15
Webserver Apache/2.2.16 (Debian)
PHP-Interface für den Webserver cgi-fcgi
Joomla!-Version Joomla! 3.5.0-beta2 Beta [ Ember ] 27-January-2017 23:00 GMT
Joomla!-Plattform-Version Joomla Platform 13.1.0 Stable [ Curiosity ] 24-Apr-2013 00:00 GMT

@richard67 i hope this helped to get this out finally!

@swiffer Sure. From my point of view these were successful tests. We already have already more tests than the required 2 testsm but if you want and for the recods you can mark your test result as success (this issue in the issue tracker, button "Test" in top left area, then select result status).

Regarding the long execution times, I did not see that in my tests, and I tested on 1&1 shared hosting, so I think your case was an exception.

RTC

I call this good to go. I need to quickly finish preparing dinner then will merge. is there any asides from this last two long PR's left. I know we need to change general_ci to unicode_ci - any others?

@wilsonge What do you mean with

is there any asides from this last two long PR's left

?

You mean if there is anything else to be done beside this one here and which other one?

And what is this _general_ci to _unicode_ci? means we have to change the collations from utf8mb4_general_ci to utf8mb4_unicode_ci in installation/sql/joomla.sql, maybe some recent update sqls and my 2nd conversion sql introduced with this PR?

If so, I an make a new PR for that after this one is merged.

Just let me know so I understand right what to be done.

I do not know what else beside this and the session/logout problem had to be solved.

The one we closed - between 400 messages here and there I'm starting to loose track
yes I do mean utf8mb4_general_ci to utf8mb4_unicode_ci - this was something Eli recommended. But to be done outside of this PR once this is merged.

As far as I know, these differences general or unicode determine how columns are sorted in order by statements.

Ah, and there is still the issue open, with label "3.5.0-stopper" or so, #9156 . You meant that one with the other big one to be closed?

Yes, that one should be closed after merge, and maybe also something said in the mailing list, https://groups.google.com/forum/#!topic/joomla-dev-cms/8h-GY28VVoY, and maybe in the forum, too, but this seems to be offline now, and I do not remember the topic where the rumor was going on.

P.S.: I did not know about this recommendation to change the general collations to unicode collations.

So thanks god I made this PR to handle also the non-utf8mb4 systems, because this makes sure they will then also be changed from the general to the unicode ones.

I just ask myself if I can foresee future or just was lucky with having the right idea.

Just one last test in an older mysql version

Database Version: 5.1.73
Database Collation: utf8_general_ci
Database Connection Collation: utf8_general_ci
PHP Version: 5.4.45
Web Server: Apache/2.2.15 (CentOS)

Upgrade path: 3.4.8 -> your custom 3.50-beta 2
Method: Extensions -> Update from URL (your custom URL)

1. Updated: sucess
2. Extensions Database shows: Warning: Database is not up to date! | The Joomla! Core database tables have not been converted yet to UTF-8. (all tables in utf8_general_ci)
3. Pressed "Fix": Database table structure is up to date. (all tables in utf8_general_ci)

So, they were not converted as you said before.
Success also.

@andrepereiradasilva yes, no character set or collation to convert for them, but column sizes have been adapted, e.g. alias columns enlarged to 400 characters, and length of alias columns in indexes like idx_alias, and as I just heard from George a few comment above, there will soon be a conversion also for those non-utf8mb4 systems, changing general collatins to unicode collations for correct alphabetic sorting in e.g. russian or chinese, and then really something will be converted also for those, so i think the message is ok.

Thanks a lot for detailed testing and reporting.

@richard67 thanks a lot for your hard work on this.

Just a question i have, not for this PR of course that is already RTC.

The question is: What happens if a user that has a non utf8mb4 capable mysql version (ex: 5.1.x) does the database "Fix" (like i did in the previous post).
Then, after a time he upgrades his database server to a utf8mb4 capable mysql (ex: 5.5.5) version? Will he be warned again to convert? or should he do the conversion manually?

@andrepereiradasilva See my comment for George above: #9221 (comment)

That's why my PR here adds 2 new message texts.

ok cool

@richard67 you do't have a crystal ball. you just understood the problem with general vs. unicode.
@wilsonge This should be a separate PR

@aschkenasy Means I should make a new PR after this is merged, for changing utf8_general_ci to utf8_unicode_ci on non-utf8mb4 and utf8mb4_general_ci to utf8mb4_unicode_ci on utf8mb4 capable servers for all core tables? I'd like to do that of course, just need a "go" from you and maybe George.

@richard67 that would be my suggestion, yes

@aschkenasy @wilsonge

That's what I quickly found on stackoverflow:

For any Unicode character set, operations performed using the _general_ci collation are faster than those for the _unicode_ci collation. For example, comparisons for the utf8_general_ci collation are faster, but slightly less correct, than comparisons for utf8_unicode_ci. The reason for this is that utf8_unicode_ci supports mappings such as expansions; that is, when one character compares as equal to combinations of other characters. For example, in German and some other languages “ß” is equal to “ss”. utf8_unicode_ci also supports contractions and ignorable characters. utf8_general_ci is a legacy collation that does not support expansions, contractions, or ignorable characters. It can make only one-to-one comparisons between characters.

(Cited from http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html ).

So there could be slight performance decrease when changing from general to unicode collation, but sorting would be correct then.

Let me know if you decide against it, otherwise I will make a new PR then tomorrow or so, when this is merged.

We should do this before Release Candidate, so this change will come with 3.5., otherwise I will have to extend that I did in this PR by dectedting if the change from general to unicode has been performed, in addition to the maybe already performed conversion as I have it not in this PR here.

@richard67

It was your instructions that clinched things here. They were so clear that even I knew what to do. Great work creating the patch and guiding us through the test procedure/results

@Webdongle thanks for the flowers. Was necessary because no easy test.

@aschkenasy @wilsonge After having read more, i also recommend to go to unicode collations instead of general. The performance impact is very little, but it will solve all issues related to sorting and duplicate primary keys or so in german or other languages where a capital "ß" (sharp s) does not exist, and can be replaced by double s.

Hmm, RTC label still missing.

Don't stress - merging it now :)

Not wanted to stress ... but this green button "RTC" looks so nice on a PR

Just for all the effort you put in

Haha thanks ... if I knew now where to find this emoji with the champaign bottle

well no champaign but you have beer ;)

Created an xml with
<extension name="Joomla" element="joomla" type="file" version="3.5.0-beta2" targetplatformversion="2.5" detailsurl="http://test5.richard-fath.de/extension_sts5.xml" />

Get error when Update component reads the xml

We can't install the update to 3.5.0-beta as your host doesn't support the minimum requirements for Joomla 3.5.0-beta and there is no alternative available.

btw
wine , saki and to help you keep awake

The links to my zip and xml will in a minute lead to a http status 410

@Webdongle For hacking xmls to provide your own update path, you also have to hack the details xml, in my case extension_sts5.xml

@richard67 great Job.

on a staging website, i get
Duplicate entry 'http://www.kunena.org/Array/169-K-16-and-K-17-Support-Archive/11' for key 'idx_link_old' SQL=ALTER TABLE #__redirect_links ADD UNIQUE idx_link_old (old_url(100));

on the fix with the message:
•The Joomla! Core database tables have not been converted yet to UTF-8 Multibyte (utf8mb4).

Well, means your old data has 2 redirect links for the same URL to be redirected, which is regarded to be corrupt data. The reason that in old times there was no index on this column, so this was allowed, but it is a mistake, you cannot redirect 1 old URL to 2 new targets.

Maybe one of these redirect links is enabled, and the other one nor (or trashed or so)?

So or so not related to this PR or the utf8mb4 conversion.

You have to delete the one of those 2 records with same old URL which you do not need anymore.

Can be done by trashing and deleting in backend I hope, and if this fails try in SQL (myphpadmin or so).

both disabled.

but there where 2 different urls:

http://www.kunena.org/Array/169-K-16-and-K-17-Support-Archive/113513-problem-assumed-resolved-no-response-from-user-no-karma-related-buttons-increase-decrease
http://www.kunena.org/Array/169-K-16-and-K-17-Support-Archive/113513-problem-assumed-resolved-no-response-from-user-no-karma-related-buttons-increase-decrease#113513

I tried to delete 1 of them, but i still get the same error

@810 Ah, no, sorry! Is not an old issue and not data. The index on this column was in fact introduced by the utf8mb4 conversion, I remember now. Can you open an issue on the issue tracker for that and describe your error there? I could make a PR to solve it then. But having an issue from you with a description would help me to write the testing instructions for that PR. Thanks in advance.

That the error is still there after having deleted them might be a caching issue. You have to refresh the database problems view.

But the error could be my fault, I made this index new as far as I remember.

Ah, no, I did not make it new, just modified it as the other indexes. But make a new issue please and ping me again here when done, with issue number, then I will check and comment on it what I can find out.

^^

Ok, thanks.

Hi, I know I'm a little late here, but have only since recent bumped into this issue ("max key length is 767 bytes.."). All my Google searches bring me to this patch by @richard67 (and his extensive amount of work for it!). But what I'm wondering is, as this PR is added to the staging branch, I can get this issue today? Aren't there thousands of other than me having the same? Now that all links to Richard's files are no longer available anymore, what is one supposed to do?

What happens with
Components >>> Joomla Update >>> Clear cache >>> Reinstall Joomla core files
Extensions >>> Manage >>> Discover >>> Discover
Extensions >>> Manage >>> Update >>> Find updates
Extensions >>> Manage >>> Database >>> Fix

Perhaps the forum may be a better place for you to post the problem.

@planetcrypton That happens if some 3rd party extension added some index, or some index has been added "manually" (I.e. In PhpMyAdmin) e.g. for performance tuning. The Joomla! core only caters for indexes added by regular Joomla! installation and updates. Check the Sql error message for the name of the index making the problem, and drop that index in PhpMyAdmin.

@richard67 thanks for that!
I started with finding the indexes:
SHOW INDEX IN xyz_menu WHERE Column_name='alias';
Which gave me 3: idx_alias, idx_alias_parent_id, idx_client_id_parent_id_alias_language. Dropped them all:
ALTER TABLE xyz_menu
DROP INDEX idx_alias;

ALTER TABLE xyz_menu
DROP INDEX idx_alias_parent_id;

ALTER TABLE xyz_menu
DROP INDEX idx_client_id_parent_id_alias_language;

Ran a Database repair from Joomla! and that worked :).
Afterwards there were only 2 indexes shown for Column_name='alias'; idx_alias, idx_client_id_parent_id_alias_language

@planetcrypton Yes, the idx_alias_parent_id seems not to come from core, or it was a remainder from pre-2.5 Joomla! which has not been removed when updating to 2.5 years ago.

Always a pleasure to help.