{"action":"created","issue":{"url":"https://api.github.com/repos/joomla/joomla-cms/issues/9423","repository_url":"https://api.github.com/repos/joomla/joomla-cms","labels_url":"https://api.github.com/repos/joomla/joomla-cms/issues/9423/labels{/name}","comments_url":"https://api.github.com/repos/joomla/joomla-cms/issues/9423/comments","events_url":"https://api.github.com/repos/joomla/joomla-cms/issues/9423/events","html_url":"https://github.com/joomla/joomla-cms/issues/9423","id":140772033,"number":9423,"title":"B/C break for all third party extensions due to MySQL collation change!!!","user":{"login":"joomlajoe","id":17835460,"avatar_url":"https://avatars.githubusercontent.com/u/17835460?v=3","gravatar_id":"","url":"https://api.github.com/users/joomlajoe","html_url":"https://github.com/joomlajoe","followers_url":"https://api.github.com/users/joomlajoe/followers","following_url":"https://api.github.com/users/joomlajoe/following{/other_user}","gists_url":"https://api.github.com/users/joomlajoe/gists{/gist_id}","starred_url":"https://api.github.com/users/joomlajoe/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/joomlajoe/subscriptions","organizations_url":"https://api.github.com/users/joomlajoe/orgs","repos_url":"https://api.github.com/users/joomlajoe/repos","events_url":"https://api.github.com/users/joomlajoe/events{/privacy}","received_events_url":"https://api.github.com/users/joomlajoe/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":17,"created_at":"2016-03-14T19:16:53Z","updated_at":"2016-03-15T12:40:03Z","closed_at":null,"body":"#### Steps to reproduce the issue\r\nAll third party extensions using MySQL JOIN with core tables such as #__sessions, #__menu, etc on primary key fields won't work anymore because the collation change from utf8_general_ci to utf8_unicode_ci\r\n\r\nUse a query like this one:\r\n\r\nSELECT * FROM #__mycustomtable AS s LEFT JOIN #__menu AS m ON s.menutype= m.menutype\r\n\r\nSELECT * FROM #__mycustomtable AS s LEFT JOIN #__session AS m ON s.session_id= m.session_id\r\n\r\n#### Expected result\r\nInstead of having a successful query based on the JOINED 'menutype' field it will fail because the table used by the third party extension '#__mycustomtable' is still based on utf8_general_ci\r\n\r\n\r\n#### Actual result\r\nIllegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' SQL=SELECT ....\r\n\r\n\r\n#### System information (as much as possible)\r\n\r\n\r\n\r\n#### Additional comments\r\nDevelopers should be at least warned that all their extensions based on such queries will be broken and have time to update all tables of their extensions accordingly. At this point March 16 as the release date of Joomla 3.5 is not acceptable, you are going to break a lot of third party extensions in a subtle way. 3 of my extensions were broke because of this issue.\r\n@spignataro @dunglas @beat "},"comment":{"url":"https://api.github.com/repos/joomla/joomla-cms/issues/comments/196801566","html_url":"https://github.com/joomla/joomla-cms/issues/9423#issuecomment-196801566","issue_url":"https://api.github.com/repos/joomla/joomla-cms/issues/9423","id":196801566,"user":{"login":"beat","id":47402,"avatar_url":"https://avatars.githubusercontent.com/u/47402?v=3","gravatar_id":"","url":"https://api.github.com/users/beat","html_url":"https://github.com/beat","followers_url":"https://api.github.com/users/beat/followers","following_url":"https://api.github.com/users/beat/following{/other_user}","gists_url":"https://api.github.com/users/beat/gists{/gist_id}","starred_url":"https://api.github.com/users/beat/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/beat/subscriptions","organizations_url":"https://api.github.com/users/beat/orgs","repos_url":"https://api.github.com/users/beat/repos","events_url":"https://api.github.com/users/beat/events{/privacy}","received_events_url":"https://api.github.com/users/beat/received_events","type":"User","site_admin":false},"created_at":"2016-03-15T12:40:03Z","updated_at":"2016-03-15T12:40:03Z","body":"I haven't seen any B/C issues in Community Builder yet as we join usually on primary keys which are always integers. However as this B/C issue seems confirmed, it is imho a major issue.\r\n\r\nJust a few stupid questions to try to address this issue constructively:\r\n\r\n1. Do we still need 4-bytes UTF-8 in database with proper filtering implemented at PHP level for older MySQL versions ?\r\n\r\nIf yes:\r\n2. Would using general_ci fix the issue (not sure about that one) ?\r\n\r\nIf no: Maybe we can postpone the collation change to upcoming Joomla 4 while leaving all the other great stuff in 3.5 in a RC4 (do not worry about B/C towards RC2/3) ?"}}