{"action":"created","issue":{"url":"https://api.github.com/repos/joomla/joomla-cms/issues/9221","repository_url":"https://api.github.com/repos/joomla/joomla-cms","labels_url":"https://api.github.com/repos/joomla/joomla-cms/issues/9221/labels{/name}","comments_url":"https://api.github.com/repos/joomla/joomla-cms/issues/9221/comments","events_url":"https://api.github.com/repos/joomla/joomla-cms/issues/9221/events","html_url":"https://github.com/joomla/joomla-cms/pull/9221","id":136687984,"number":9221,"title":"Correct utf8mb4 conversion","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/Language%20Change","name":"Language Change","color":"eb6420"},{"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":249,"created_at":"2016-02-26T12:44:04Z","updated_at":"2016-02-28T11:12:54Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/joomla/joomla-cms/pulls/9221","html_url":"https://github.com/joomla/joomla-cms/pull/9221","diff_url":"https://github.com/joomla/joomla-cms/pull/9221.diff","patch_url":"https://github.com/joomla/joomla-cms/pull/9221.patch"},"body":"Pull Request for Issue #9156 .\r\n\r\n#### Summary of Changes\r\n\r\nThis PR corrects the errors related to utf8mb4 conversions when updating a pre-3.5 to 3.5 (right now current staging plus this patch).\r\n\r\nPre-3.5 means e.g. 3.4.8, not a beta or latest staging. There is no update path from Beta to Beta.\r\n\r\nSo this PR is not meant to be tested with patch tester on latest staging.\r\n\r\nIn addition to making the necessary conversion once for either utf8 or utf8mb4, this PR also allows to later convert again from utf8 to utf8mb4 after having migrated the database to a newer server.\r\n\r\nIf something is to be converted is shown in the \"Extensions -> Manage -> Database\" view in the backend, and if so, using the \"Fix\" button will do the conversion.\r\n\r\nNote: The conversion handled by this PR does **not** change the database's default collation and character set. This is on the dastabase admin to decide.\r\n\r\nAlso only the Joomla! Core tables are converted, not those of (Joomla! or 3rd party) extensions like e.g. patchtester or webinstaller.\r\n\r\n#### Testing Instructions\r\n\r\n**Preconditions**\r\n\r\nIf you use PHP 7, make sure that opcache is not used (switch off in your php.ini).\r\n\r\nOpcache could confuse this test when using old cached compiled PHP.\r\n\r\nThen **2 general methods for testing:**\r\n\r\n**Either**\r\n\r\ndo a Joomla! Update from a **clean 3.4.8** to a \"3.5.0 latest staging plus this PR applied\":\r\n\r\nYou can find an **update container** for use with the **Extension Installer** here:\r\n[http://test5.richard-fath.de/Joomla_3.5.0-beta2-Beta-Update_Package_test2.zip](http://test5.richard-fath.de/Joomla_3.5.0-beta2-Beta-Update_Package_test2.zip \"http://test5.richard-fath.de/Joomla_3.5.0-beta2-Beta-Update_Package_test2.zip\").\r\n\r\nYou also can use the **Joomla! Update component** with following **custom URL** (note it is http, not use https):\r\n[http://test5.richard-fath.de/list_test5.xml](http://test5.richard-fath.de/list_test5.xml \"http://test5.richard-fath.de/list_test5.xml\").\r\n\r\nWith both Joomla! Update or Extension Installer, the update should end with success.\r\n\r\nThen goto Extenions -> Manage -> Database.\r\n\r\nThere should be reported at least that the Joomla! Core database tables have not be converted to UTF-8 (in case of your database and client not support utf8mb4) or the same for conversion to \"UTF-8 Multibyte (utf8mb4). It means that the conversion has not been performed yet.\r\n\r\nThere might be more database problems reported depending on the update method and the previous status of the database.\r\n\r\nThen click the Fix button.\r\n\r\nResult: Either all ok now, or or some of the database problems remain to be fixed, or some other database problems are shown, which then also should be able to be fixed, i.e. clicking the \"Fix\" button might be required several times but not an endless number of times, and at the end all is ok and also the message about conversion is not shown anymore.\r\n\r\nAt the end check in mysql database e.g. with myphpadmin if all Joomla! core tables have default collation utf8mb4_general_ci, and if binary collated columns, e.g. alias columns, have utf8mb4_bin collation on a database with utf8mb4 support, and that on both kinds of databases alias columns fo tables like e.g. menu or categories go into indexes idx_alias only with a lenght of 100.\r\n\r\nThe default collation of the complete database and tables of extensions will not be changed, only character set and collation of Joomla! Core tables and their binary collated columns.\r\n\r\n**Or**\r\n\r\ndo as @wilsonge described in his issue #9156, i.e.\r\n\r\n- Install a **new, clean 3.4.8** into an **empty database**,\r\n\r\n- zip a backup of the 3.4.8 Joomla! files and export a backup of your database to be able to start test from start again.\r\n\r\n- download the branch of this patch **here** [https://github.com/richard67/joomla-cms/archive/correct-utf8mb4-conversion.zip](https://github.com/richard67/joomla-cms/archive/correct-utf8mb4-conversion.zip)\r\n\r\n- unzip it into the 3.4.8 installation, so all the files are updated now by the ones from this PR,\r\n\r\n- login at the backend and check for database problems (Extenions -> Manage -> Database).\r\n\r\n- The rest is like mentioned before after \"Then goto Extenions -> Manage -> Database\", except of following:\r\n\r\n- The database schema view shows - beside what is mentioned above for the other test method - at the top an error about a table \"#__utf8_conversion\" missing. This cannot be avoided for this udpate method. Using the \"Fix\" button will solve that. After this then at lest the problem about conversion for UTF-8 (or UTF8MB4) is shown, and maybe other database problems, too. This is like with the other test method above, and using the \"Fix\" button if necessary a few times will solve this ate the end.\r\n\r\n- **Important if you tested before with this method**: To start a new test, not just restore the old database and unzip the updated branch of this PR, this is not enough. You have to first fall back also on the file system by replacing the joomla folder by the clean 3.4.8 before then unzipping again my branch. Otherwise it can happen that when I have to rename or remove some file, then the old file is still present on the file system. After having done so, fall back to the old data using the database backup.\r\n\r\n**With both methods**, please test on database with and without utf8mb4 support if you have both.\r\n\r\nTesters please report the used update methods and your mysql server version and the client library version (mysql, mysqli or pdo driver) with your test result. Thanks in advance."},"comment":{"url":"https://api.github.com/repos/joomla/joomla-cms/issues/comments/189841450","html_url":"https://github.com/joomla/joomla-cms/pull/9221#issuecomment-189841450","issue_url":"https://api.github.com/repos/joomla/joomla-cms/issues/9221","id":189841450,"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-02-28T11:12:54Z","updated_at":"2016-02-28T11:12:54Z","body":"@anibalsanchez Also agree, but currently it is suggested by the db schema manager view as soon as he/she opens that. So maybe still something to do wit a new PR after this one is merged?"}}