Added link to custom url for update test and added info about the name of the update zip and its md5sum.

Sorry, test container not ready yet. Please wait with testing.

Now PR is ready to be tested.

@andrepereiradasilva @wilsonge A bit time and mood left for this PR here? Would be useful to have it in 3.5.0 release.

Hmm, wait, there is something wrong. Just am checking. Not test yet please.

Ah, no, I am just blind. Was all OK. Ready for being tested.

Closing this because it is obsolete if my other PR #9468 is accepted. With help of Andre I could successfully test that PR #9468 is sufficient to handle all cases for extensions, and so this one here is really not needed anymore.

Reopening because it could be a useful thing in addition to PR #9468 , e.g. for extension developers who not want to maintain sql scripts for utf8mb4 conversion with each new version of their extension but have own php scripts to handle conversion and data migration.

FYI: This change, if accepted will break upgrades between RC4 and stable.

I know that upgrades between RC releases aren't supported, but that doesn't stop people installing RC releases to their live sites...

As you said, we don't support updating from Betas and RCs to stable, so that's a moot point.
If people are installing RCs on their life site, that's what they get for doing so. There is a big warning in the release announcement for that reason.

As for this PR, I like the idea to make that table useable for other extensions as well. It's the right thing to do.
However I would love to see a step further. Currently extensions have to run their own script to migrate the tables to utf8mb4. As an extension developer I would love to see an API where I can pass in my table name and it does the converting for me. So I don't have to search for the code that does it for Joomla and copy-pasting it without knowing what it does. Or even worse do something stupid myself.

@Bakual My point was just an observation while I was looking to the change, and I agree with you.

As shown in the comment I referenced from #9468 (see above), this change is very much needed in order to even enable components to change their collation in a reliable and easy way.

But even more than needing an API, we need to allow extensions to do the conversion without writing custom PHP installer script. Having a good API would be a nice side effect of having that even simpler solution.

I agree, I do not want to copy-paste Joomla internals or do something stupid myself in order to support Joomla! 3.5 in my extensions. Both ways are just too error prone, especially when you need to take into account older versions of Joomla and the fact that even J! 3.5 may still use utf8 if the database doesn't support anything better.

We also need to remember that most of the extension developers aren't as experienced developers than we are, especially when it comes to Joomla internals. Even I have hard time to fully understand the change as I've not paid too much attention into it until a few days ago when I noticed in my PHP7 testing that all components still had the old utf8 collation in the database.

Using utf8mb4 will be a great change, but its also something that needs to be supported by every 3rd party extension. In addition there needs to be an easy way for admins to change their DB collation without needing to install newest version of every single extension manually in order to trigger the collation change.

I guess that Joomla 3.5 could be released without these features, but at the same time automating collation change is something that needs to be implemented soon, together with documentation for both developers and administrators alike.

If people were as willing with testing as they are with writing comments ...

@Bakual that's impossible - the only way core could do this is by accessing the system information table to get detailed index information (I mean more than what's given by SHOW INDEXES) and on many shared hosts this is not given. Therefore it will always be down to extensions to do this upgrade I'm afraid

I did kind of test it, and the discussion above is the direct result from that attempt to test. The change is a great start, but it just raises the question "What I'm supposed to do with all of this?". I agree that without API or some automated logic, this change will not help anyone on anything.

@wilsonge It is possible to automate collation change with the help of this table. We do not need to get any information from the database; we just need to know if the collation change has been performed or not.

What it is good for? Well, extensions still will have to provide conversion sql script(s), but they could be handled separately from their schema updates.

The xml manifests of extensions could be extended a path to conversion scripts, like it is for schema updates but separate from them, e.g. "utf8conversionpath".

Wheneever the core now detects that a conversion is necessary because the expected conversion status according to server and client versions is not equal to the one saved in the database (0 = not converted, 1 = converted to utf8_unicode_ci except the utf8_bin columns, 2 = converted to utf8mb4), the core will run conversion scripts again if necessary.

This scenario would cater for data migration from old db not supporting utf8mb4 to newer db supporting it.

Like the core, each extension package would contain a conversion script for latest version of the extension (which is the package where the script is in).

All this will be fully B/C. Because we have RC here, I did not make a schema update for adding the new column to the table, but if this change will not come with 3.5.0 but with 3.5.1, I will do so and add it to this PR. But it would be much easier if it would go in to 3.5.0 so the utf8_conversion table not needs a schema update.

Regarding updating live sites to Betas and RCs I think about making another PR which detects this scenario and if detected, deletes their site and formats their harddisk and sends their site email address to known spammer companies, just to give them what they deserve!!! (joke)

You can find out if it's happened for sure - but you cannot automate the collation change because you have to check index lengths and ensure that if you have unique keys people's data can be migrated without loss etc. This is not something core can do for all extensions

@mahagr Please read my previous comment, it explains what it is good for and why it does not break B/C (because when coming with 3.5.1 and not 3.5.0 there will be a schema update sql adding the new column).

Guys please test.

And @wilsonge , if possible please consider this one and my other one which already is RTC for 3.5.0 if there is still enough time.

I'm not merging either for 3.5.0 stable :( Sorry - I'd rather we spend more time and get it merged in one go with a good workflow for extension developers than rush it on the day of release

@wilsonge Means you wanna release today?

Ok, then it would be too little time for merging.

After you released 3.5.0 I have to make my patched update package behind my custom url for the update test be a faked 3.5.1 then, so there is something to be updated. Maybe I will need your help for that. And I have to include schema update into the update package for adding the new extension_id column to the utf8_conversion table, and also same schema update to the core, means a new commit for this in this PR.

I did not want to provide the final all in one solution with 1 single PR because 1. it was not 100% clear which way to go when I made my 2 PRs, means if we need one only or both, and 2. I want to keep the particular changes in small testable portions, so the particular portions can be tested separately.

Yup as per the RC4 release info - release day is today

Did not see that ... was still busy with reading release info for RC1

Tested successfully both installation and upgrade. Everything looks good.

Do not get me wrong; I'm just trying to help by considering real life use cases and I agree that while this PR is really important, its just a start. I agree with @wilsonge that its better to get J3.5 out today and after that take the time needed to really solve the whole issue by giving extension developers something they can use without paying too much attention to the details.

I think we also need to inform extension developers that Joomla team is working for an easy way to update extension specific tables, asking them to be tuned.. :)

Sure, and I appreceate your help. Only I think updating a live site to Betas or RCs is not a real life use case to be considered.

@wilsonge Maybe give this PR here a milestone 3.5.1 so people can see it is scheduled somehow?

Adapted this PR to the fact that meanwhile 3.5.0 is released, so it needs a new schema update script for adding the new column to the meanwhile existing table.

Updated also the XMLs and the zip container behind the custom update URL for Test 2, so you can update also a 3.5.0 (or even a 3.5.0 RC).

PR can be tested now.

@mahagr If you test this again, could you this time mark the test result on the issue tracker after your test?

Just go to https://issues.joomla.org/tracker/joomla-cms/9487 and use the "test this" button.

I meanwhile noticed this PR will not work as it is, because it does not create the new column when being updated from 3.5.0 to e.g. 3.5.1 (with this PR in) with the files copy and database fix method. It will use the new column in the query for conversion status when it does not exist yet. We have to add the new column also in the function prepareUtf8mb4StatusTable() to check and repair the table in database.php, and we need such function too in script.php.

Because there is more stuff to be done for automatic conversion also for extensions than only adding this new column, I am closing this PR now, and I will prepare a new PR solving all this.