[#9303] - Bug Fix - Correction for PR #9260 utf8mb4 / utf8 conversion for newly installed Joomla!
- Closed
- 5 Mar 2016
- Medium
- Build: staging
- # 9303
- Diff
- richard67:correct-utf8mb4-new-install-2
- Success The Travis CI build passed Details
User tests: Successful: Unsuccessful:
Summary of Changes
This PR corrects a mistake I made with PR #9260 for the utf8mb4 / utf8 conversion to detect that it runs on a newly installed Joomla! and so nothing to be converted.
With PR #9260 I decided to initialize the conversion status in database table "#__utf8_conversion" with a value of 3 and later when running the conversion to adjust that to the expected value 1 or 2, depending on utf8mb4 supoort.
But if after a new installation nobody ever goes in the backend to "Extensions -> Manage -> Database -> Fix", and Joomla! is not updasted to a newer version, the status never is set to the final value.
This PR here corrects this by setting the correct conversion status at the end of a new installation.
So the changes to deal with the value 3 from PR #9260 can be undone with this PR here,. too.
This is pre-requisite for a later PR to correct the conversion in case of update and Database -> Fix in order not to run again when it is not necessary anymore by checking the conversion status in database (which without this PR here may still be 3 when migrating to another db server and so not correctly be handeled).
Testing Instructions
Hint: Replace "#__" by your db prefix.
- Perform a new installation with latest staging, the source you can find here: https://github.com/joomla/joomla-cms/archive/staging.zip
- Before login at backend, check in your database with e.g. phpMyAdmin the value of column "converted" in table "#__utf8_conversion". Result: Value = 3.
- Login at backend and go to 'Extensions -> Manage -> Database'. Result: No database problems shown.
- Press the "Fix" button even if no problems shown.
- Check again in database the value of column "converted" in table "#__utf8_conversion". Result: Value = 1 if no utf8mb4 support (collations of core tables are uft8_unicode_ci) or 2 if utf8mb4 suport (collations of core tables are uft8mb4_unicode_ci).
Now repeat steps 1 to 3 (4 and 5 are not necessary now) with the branch for this patch.
Source for it is here: https://github.com/richard67/joomla-cms/archive/correct-utf8mb4-new-install-2.zip
Result: The final value 1 or 2 of column "converted" in table "#__utf8_conversion" is already set in step 2, step 3 shows also in this test no database problems.
| Status | New | ⇒ | Pending |
| Labels |
Added:
?
|
||
@andrepereiradasilva @infograf768 Could you test this one here if you find time? Would be important for 3.5.0, see my comment just before.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9303.
will do it today, just need to have some time.
Great, thanks in advance.
Hi Richard, thanks for the excellent intro & instructions. Really got me bought into the issue.
Unfortunately I got to step 2 and then couldn't see the __utf8_conversion table
My bad. Table is there, this was down to me not paying attention to prefix and expecting new install to clear out old tables. Damn that ass in assume.
I have tested this item 
successfully on 636a664
Test OK. Initial value 2.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9303.
I have tested this item successfully on 636a664
Received Converted: 2 on utf8mb4 supported machine.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9303.
Thanks for testing.
joomla-brian-or-wilsonge-bot please RTC 
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9303.
I have tested this item successfully on 636a664
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9303.
@andrepereiradasilva Thanks for testing.
Just a hint: Collations should be utf8mb4_unicode_ci for the core tables of a new install with my zip.
sure and they are.
BTW, not related to this PR, but shouldn't the other joomla core extensions use utf8mb4_unicode_ci too?
That i know of, that use database tables there are only:
Patchtester Component: https://github.com/joomla-extensions/patchtester
(already created in utf8mb4_unicode_ci in latest patchtester staging)Weblinks Component: https://github.com/joomla-extensions/weblinks
(not yet in utf8mb4_unicode_ci)
@andrepereiradasilva Yes, they should. They should use utf8mb4_unicode_ci (which will be changed to utf8_unicode_ci in sql statements by the database driver in case of no utf8mb4 support).
I had seen you had prepared some PRs for some of the extensions a while ago with general. Those would have to be changed, yes.
Btw. thanks for your connection collation info above, just am preparing a new PR for the joomla database driver to set connection collation to utf8_unicode_ci or utf8mb4_unicode_ci, too (not general like now). Was not aware of that. The unicode sorting is always correct while the general is a compromise, and I think we need it in the connection settings too, to get things sorted right in russian and so on. But I will ask Eli in my new PR later to confirm that.
I had seen you had prepared some PRs for some of the extensions a while ago with general. Those would have to be changed, yes.
i only did for the patchtester and with utf8mb4_unicode_ci (already merged)
See https://github.com/joomla-extensions/patchtester/pull/127/files
So patchtester is OK, great.
yeah, as soon as mbabker releases a new beta (or stable) they will be CREATED (only clean install) with utf8mb4_unicode_ci. only weblinks missing i think.
Yes, conversion of existing extensions tables is an open point still.
not for the patchtester, it has no upgrade path yet. so no need.
I see. Forget about my new PR I wanna make for setting the connection collation also to unicode sorting. This seems to be a bit complicated, see discussion on https://mathiasbynens.be/notes/mysql-utf8mb4
ok, so maintainers please RTC this and let's move on :)
| Labels |
Added:
?
|
||
| Labels |
Removed:
?
|
||
Setting RTC - but can we just fix up my comment richard - as long as that's the only change won't need redoing of the tests
The method used in the database fixer was new for 3.5 - so the other one below just never got updated. Can we please just update this one instance so UTF8MB4 is consistent - we can do the other one in a new PR :)
This PR has received new commits.
CC: @andrepereiradasilva, @anibalsanchez, @Bodge-IT
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9303.
| Status | Pending | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2016-03-05 19:41:26 |
| Closed_By | ⇒ | wilsonge |
| Milestone |
Added: |
||
when i do a new install with default utf8mb4 collation, then after the install has changed to utf8_general_ci as default collation.
i will explain what i mean.
1) create new database with utf8mb4_unicode_ci collation
2) Run installer (database mysqli)
3) After install the default collation is set to utf8_general_ci see image
@810 I see. Well, is not related to this PR or our utf8mb4 conversions, because those definitely not touch thd database default collation, and it does not do any harm for Joomla! because Jomla! and the official extension(s) specify collation for every table on table creation.
So it is not related to this PR here, but feel free to make a new issue for that in the issue tracker, or a new PR.
I'll put on my to do list to check if I find somewhere in Joomla! if this database default collation is set somwhere (php code or sql scripts), but if you are faster and find it, let me know.
I know is not related to this PR, but when you install joomla without a database pre created,
In
https://github.com/joomla/joomla-cms/blob/staging/libraries/joomla/database/driver.php#L858
CREATE DATABASE dbname CHARACTER SET `utf8mb4` COLLATE utf8mb4_unicode_ci;
should be something like this for mysql i think
i think its line 766 let me check it
The driver.php contains "_general" in several code and comment lines, code lines are 737 and 766. Should be changed all I think but don't ask me for testing instructions. Who wants to do a PR for changing the driver.php?
i have changed them,but still not working. but for this one i can do a pr
i think is in the line i said before, because is when the database is created, not the tables.
https://github.com/joomla/joomla-cms/pull/9311/files
@andrepereiradasilva i have added it on your line, but is not working, so there is somewhere else the issue
@wilsonge This PR here is a pre-requisite to solve your 3.5.0-blocker PR #9297 in the right way, and also to solve the other things which are to be done (with db fix only execute conversions once if not done yet, correct check of utf8mb4 conversion status by the input filter) in the right way, so this one here maybe needs the 3.5.0-blocker label, too?
The reason why I did not do all in this PR but will do the other stuff when this one here is done is that otherwise testing all in one would be too complicated.
So this one here has easy test from my point of view.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9303.