[#9440] - Fix for issue #9423 : Change collation of languages.lang_code column
- Fixed in Code Base
- 15 Mar 2016
- Medium
- Build: staging
- # 9440
- Diff
- richard67:lang-code-collation
- Success continuous-integration/travis-ci/pr The Travis CI build passed Details
- Pending JTracker/HumanTestResults Human Test Results: 1 Successful 0 Failed. Details
User tests: Successful: Unsuccessful:
Pull Request for parts of issue #9423 .
Summary of Changes
This pull request changes the collation of column lang_code in the languages table #__languages from utf8mb4_unicode_ci (or utf8_unicode_ci) to utf8mb4_bin or utf8_bin to avoid errors with impossible implicit character set conversion on joining other tables' columns.
Since this is the most likely use case for joins making problems, it should solve issue #9423 mostly.
In discussions there was reported that this worked for all extensions and character set and collations combinations tested there.
Testing Instructions
To apply this PR it is not sufficient to apply the patch with patchtester only, it is in addition necessary to change collation of the lang_code column of the languages table. This can be done by forcing the utf8(mb4) conversion to run, which makes also this conversion.
The conversion run can be forced by issuing following SQL statement in phpmyadmin:
UPDATE
#__utf8_conversionSETconverted= 0;
(replace "#__" by your db prefix).
Then goto Extensions -> Manage -> Update and see that the conversion has to be done being reported as open problem.
Use the "Fix" button to run the conversion.
If I did not make any mistake in the conversion sql script, this should not cause any errors. Please check also this.
Then use following sql statement to test a join query:
SELECT c.*, a.name AS asset_name, l.title AS language_title, u.name AS user_name FROM
#__categoriesAS c LEFT JOIN#__assetsAS a ON a.id = c.asset_id LEFT JOIN#__languagesAS l ON l.lang_code = c.language LEFT JOIN#__usersAS u ON u.id = c.created_user_id;
On unpatched 3.5.0 RC 3 this will break with an sql error about invalid implicit character set or collation conversions, with this patch applied and the conversion run it will succeed.
You can try different combinations of collations by modifying character set and collation of column languagein the #__categories table and then doing the above query again.
Also test on a not utf8mb4 capable database that the database fix for doing the conversion works there, too.
| Status | New | ⇒ | Pending |
joomla-cms-bot
- | Labels |
Added:
?
|
||
Do you need to? If it works fine with just the field in the languages table changed then leave it at this.
Depends on how people join ... if they join to the language table's lang_code column, or maybe join directly to e.g. the categories language column if that makes sense for them.
But if they wanna be sure that the language exists (i.e. is installed) they always should use the languages table to join with, I think.
If you're gonna try to account for every scenario someone might be doing then the only thing you can do is revert back to the 3.4.8 state and make zero changes at all. This change is acceptable as it is.
Ok, then test please 
If I shall do it for other columns, too, like session_id or menutype, then let me know here. But as far as I could see lang_code was most urgent.
The unpopular opinion (I'm full of them).
If you're doing something where you're joining to the session table (meaning you're joining on its session_id PK), you probably should be should be storing the data in the session to begin with and if you aren't you need to refactor away from relying on that table, especially if the day ever comes when Joomla can run without that session table (I'm already icing champagne for that occasion).
The menutypes table, core already makes the big no-no of joining on the text based column instead of the table's integer PK (but this is fine since core's schema is also changing). I'd suggest that a data migration needs to happen sooner or later to use the proper PK for the join instead of the text column long term. Short term, to be quite frank I don't know what the "right" answer is but given that field doesn't have a standard text definition (it's user input), I'm apt to say make no changes from this point.
I just simplified testing instructions and made them more precise, so testers are welcome 
- Installed latest staging with sample data, patchtester and weblinks (https://github.com/joomla-extensions/weblinks/releases/download/3.4.1/pkg_weblinks_3.4.1.zip)
- Applied your patch
- Run the query to set the utf conversion table to 0
- Gone to Extensions -> Manage -> Update, a message appeared, pressed "fix"
- Fix went fine.
- Gone to phpmyadmin checked
#__languagestable and thelang_codefield usesutf8mb4_bincollation. All ok - Gone to weblinks (
language fieldisutf8_general_cicollation). All ok
Tested on Database Version 5.5.5-10.0.24-MariaDB
I just see simplified testing insructions are not visible yet on issue tracker, have updated via GitHub where they are visible.
But you already helped yourself, thanks for testing.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9440.
Now the issue tracker has the changed instructions, too.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9440.
@andrepereiradasilva Did you forget to mark test result? Or wanted to do some more tests?
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9440.
wilsonge
- | Status | Pending | ⇒ | Fixed in Code Base |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2016-03-15 23:44:06 |
| Closed_By | ⇒ | wilsonge |
I have tested this item 
successfully on 9da1c06
Tested succesfully on 2 install : one with utf8mb4 support and one with no utf8mb4 support
2 tests success!
Note: i've tested with com_weblinks, but with other extensions i've found on the JED having this illegal mix of collations, and all seems good! 
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9440.
Good to hear that.
@richard67 @mbabker @beat @andrepereiradasilva @wilsonge ... Thank You for all your time dedicated on this issue and 3.5.0 release! ;-)
Welcome.
Maybe I should change other language code columns, eg
languagein table#__categories, too?What do you think, guys?
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9440.