{"action":"created","issue":{"url":"https://api.github.com/repos/joomla/joomla-cms/issues/9440","repository_url":"https://api.github.com/repos/joomla/joomla-cms","labels_url":"https://api.github.com/repos/joomla/joomla-cms/issues/9440/labels{/name}","comments_url":"https://api.github.com/repos/joomla/joomla-cms/issues/9440/comments","events_url":"https://api.github.com/repos/joomla/joomla-cms/issues/9440/events","html_url":"https://github.com/joomla/joomla-cms/pull/9440","id":141078153,"number":9440,"title":"Fix for issue #9423 : Change collation of languages.lang_code column","user":{"login":"richard67","id":7413183,"avatar_url":"https://avatars.githubusercontent.com/u/7413183?v=3","gravatar_id":"","url":"https://api.github.com/users/richard67","html_url":"https://github.com/richard67","followers_url":"https://api.github.com/users/richard67/followers","following_url":"https://api.github.com/users/richard67/following{/other_user}","gists_url":"https://api.github.com/users/richard67/gists{/gist_id}","starred_url":"https://api.github.com/users/richard67/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/richard67/subscriptions","organizations_url":"https://api.github.com/users/richard67/orgs","repos_url":"https://api.github.com/users/richard67/repos","events_url":"https://api.github.com/users/richard67/events{/privacy}","received_events_url":"https://api.github.com/users/richard67/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/joomla/joomla-cms/labels/PR-staging","name":"PR-staging","color":"c7def8"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":14,"created_at":"2016-03-15T19:40:02Z","updated_at":"2016-03-15T21:45:03Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/joomla/joomla-cms/pulls/9440","html_url":"https://github.com/joomla/joomla-cms/pull/9440","diff_url":"https://github.com/joomla/joomla-cms/pull/9440.diff","patch_url":"https://github.com/joomla/joomla-cms/pull/9440.patch"},"body":"Pull Request for parts of issue #9423 .\r\n\r\n#### Summary of Changes\r\n\r\nThis 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.\r\n\r\nSince this is the most likely use case for joins making problems, it should solve issue #9423 mostly.\r\n\r\nIn discussions there was reported that this worked for all extensions and character set and collations combinations tested there.\r\n\r\n#### Testing Instructions\r\n\r\nTo 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.\r\n\r\nThe conversion run can be forced by issuing following SQL statement in phpmyadmin:\r\n\r\n> UPDATE `#__utf8_conversion` SET `converted`= 0;\r\n\r\n(replace \"\\#__\" by your db prefix).\r\n\r\nThen goto Extensions -> Manage -> Update and see that the conversion has to be done being reported as open problem.\r\n\r\nUse the \"Fix\" button to run the conversion.\r\n\r\nIf I did not make any mistake in the conversion sql script, this should not cause any errors. Please check also this.\r\n\r\nThen use following sql statement to test a join query:\r\n\r\n> SELECT c.*, a.name AS asset_name, l.title AS language_title, u.name AS user_name FROM `#__categories` AS c LEFT JOIN `#__assets` AS a ON a.id = c.asset_id LEFT JOIN `#__languages` AS l ON l.lang_code = c.language LEFT JOIN `#__users` AS u ON u.id = c.created_user_id;\r\n\r\nOn 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.\r\n\r\nYou can try different combinations of collations by modifying character set and collation of column `language`in the `#__categories` table and then doing the above query again.\r\n\r\nAlso test on a not utf8mb4 capable database that the database fix for doing the conversion works there, too."},"comment":{"url":"https://api.github.com/repos/joomla/joomla-cms/issues/comments/197037578","html_url":"https://github.com/joomla/joomla-cms/pull/9440#issuecomment-197037578","issue_url":"https://api.github.com/repos/joomla/joomla-cms/issues/9440","id":197037578,"user":{"login":"richard67","id":7413183,"avatar_url":"https://avatars.githubusercontent.com/u/7413183?v=3","gravatar_id":"","url":"https://api.github.com/users/richard67","html_url":"https://github.com/richard67","followers_url":"https://api.github.com/users/richard67/followers","following_url":"https://api.github.com/users/richard67/following{/other_user}","gists_url":"https://api.github.com/users/richard67/gists{/gist_id}","starred_url":"https://api.github.com/users/richard67/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/richard67/subscriptions","organizations_url":"https://api.github.com/users/richard67/orgs","repos_url":"https://api.github.com/users/richard67/repos","events_url":"https://api.github.com/users/richard67/events{/privacy}","received_events_url":"https://api.github.com/users/richard67/received_events","type":"User","site_admin":false},"created_at":"2016-03-15T21:45:03Z","updated_at":"2016-03-15T21:45:03Z","body":"Now the issue tracker has the changed instructions, too.<hr /><sub>This comment was created with the <a href=\"https://github.com/joomla/jissues\">J!Tracker Application</a> at <a href=\"https://issues.joomla.org/tracker/joomla-cms/9440\">issues.joomla.org/joomla-cms/9440</a>.</sub>"}}