{"action":"created","issue":{"url":"https://api.github.com/repos/joomla/joomla-cms/issues/8472","labels_url":"https://api.github.com/repos/joomla/joomla-cms/issues/8472/labels{/name}","comments_url":"https://api.github.com/repos/joomla/joomla-cms/issues/8472/comments","events_url":"https://api.github.com/repos/joomla/joomla-cms/issues/8472/events","html_url":"https://github.com/joomla/joomla-cms/pull/8472","id":117456237,"number":8472,"title":"Fixed failure on updating database not supporting UTF8MB4","user":{"login":"roland-d","id":359377,"avatar_url":"https://avatars.githubusercontent.com/u/359377?v=3","gravatar_id":"","url":"https://api.github.com/users/roland-d","html_url":"https://github.com/roland-d","followers_url":"https://api.github.com/users/roland-d/followers","following_url":"https://api.github.com/users/roland-d/following{/other_user}","gists_url":"https://api.github.com/users/roland-d/gists{/gist_id}","starred_url":"https://api.github.com/users/roland-d/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/roland-d/subscriptions","organizations_url":"https://api.github.com/users/roland-d/orgs","repos_url":"https://api.github.com/users/roland-d/repos","events_url":"https://api.github.com/users/roland-d/events{/privacy}","received_events_url":"https://api.github.com/users/roland-d/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"},{"url":"https://api.github.com/repos/joomla/joomla-cms/labels/RTC","name":"RTC","color":"009800"}],"state":"open","locked":false,"assignee":null,"milestone":{"url":"https://api.github.com/repos/joomla/joomla-cms/milestones/7","html_url":"https://github.com/joomla/joomla-cms/milestones/Joomla!%203.5.0","labels_url":"https://api.github.com/repos/joomla/joomla-cms/milestones/7/labels","id":944889,"number":7,"title":"Joomla! 3.5.0","description":"","creator":{"login":"rdeutz","id":467356,"avatar_url":"https://avatars.githubusercontent.com/u/467356?v=3","gravatar_id":"","url":"https://api.github.com/users/rdeutz","html_url":"https://github.com/rdeutz","followers_url":"https://api.github.com/users/rdeutz/followers","following_url":"https://api.github.com/users/rdeutz/following{/other_user}","gists_url":"https://api.github.com/users/rdeutz/gists{/gist_id}","starred_url":"https://api.github.com/users/rdeutz/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rdeutz/subscriptions","organizations_url":"https://api.github.com/users/rdeutz/orgs","repos_url":"https://api.github.com/users/rdeutz/repos","events_url":"https://api.github.com/users/rdeutz/events{/privacy}","received_events_url":"https://api.github.com/users/rdeutz/received_events","type":"User","site_admin":false},"open_issues":3,"closed_issues":409,"state":"open","created_at":"2015-01-21T08:12:44Z","updated_at":"2015-11-25T21:18:03Z","due_on":"2015-12-07T23:00:00Z","closed_at":null},"comments":156,"created_at":"2015-11-17T21:36:51Z","updated_at":"2015-11-26T01:51:16Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/joomla/joomla-cms/pulls/8472","html_url":"https://github.com/joomla/joomla-cms/pull/8472","diff_url":"https://github.com/joomla/joomla-cms/pull/8472.diff","patch_url":"https://github.com/joomla/joomla-cms/pull/8472.patch"},"body":"## Problem ##\r\nUsers running a MySQL database that does not support UTF8MB4 will get failures during an update to Joomla 3.5.0 because the queries to update the tables to UTF8MB4 are executed.\r\n\r\n## Fix ##\r\nThe fix is to modify the SQL queries that are run on databases not supporting UTF8MB4.\r\n\r\n## Testing ##\r\n1. Setup a Joomla 3.4.5 site with a MySQL database version that does not support UTF8MB4. For example mysqlnd lower than 5.0.9 or libmysql lower than 5.5.3.\r\n2. Download the Joomla 3.5.0 beta 1 package\r\n3. Change the files in the package with the files in this pull request\r\n4. Update the Joomla version to 3.5.0 beta 1\r\n5. The update should run without problems\r\n\r\n## Disclaimer ##\r\nYou may still see database problems on the database tab due to different field definitions. This is not handled in this fix but will be dealt with in another fix."},"comment":{"url":"https://api.github.com/repos/joomla/joomla-cms/issues/comments/159778083","html_url":"https://github.com/joomla/joomla-cms/pull/8472#issuecomment-159778083","issue_url":"https://api.github.com/repos/joomla/joomla-cms/issues/8472","id":159778083,"user":{"login":"zjw","id":1707418,"avatar_url":"https://avatars.githubusercontent.com/u/1707418?v=3","gravatar_id":"","url":"https://api.github.com/users/zjw","html_url":"https://github.com/zjw","followers_url":"https://api.github.com/users/zjw/followers","following_url":"https://api.github.com/users/zjw/following{/other_user}","gists_url":"https://api.github.com/users/zjw/gists{/gist_id}","starred_url":"https://api.github.com/users/zjw/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zjw/subscriptions","organizations_url":"https://api.github.com/users/zjw/orgs","repos_url":"https://api.github.com/users/zjw/repos","events_url":"https://api.github.com/users/zjw/events{/privacy}","received_events_url":"https://api.github.com/users/zjw/received_events","type":"User","site_admin":false},"created_at":"2015-11-26T01:51:16Z","updated_at":"2015-11-26T01:51:16Z","body":"A few days ago, @richard67 [wrote](#issuecomment-158757901):\r\n> Depending on how it works in Joomla (characters or bytes counted for limits\r\n> e.g. of fields) this might cause a data loss for old installations even if not\r\n> being migrated. On the other hand, having database generation with different\r\n> lengths in bytes (191 or 255) would maybe not be possible to handle in the\r\n> right way.\r\n>\r\n> So I'd like this to be discussed among us here.\r\n\r\nYes, there are instances where columns are being downsized from (usually) `varchar(255)` to `varchar(191)`, and there is the possibility of data loss in those instances.  The relevant changes occur in `administrator/components/com_admin/sql/updates/mysql/3.5.0-2015-07-01.sql` (and some of them are repeated in `administrator/components/com_admin/sql/utf8mb4/mysql.sql` because of the `utf8mb4_bin` conversions).  Here are my thoughts on those changes.\r\n\r\n--------------------------------------\r\n```\r\nALTER TABLE `#__banners` MODIFY `alias` varchar(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';\r\nALTER TABLE `#__contact_details` MODIFY `alias` varchar(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';\r\nALTER TABLE `#__content` MODIFY `alias` varchar(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';\r\nALTER TABLE `#__newsfeeds` MODIFY `alias` varchar(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';\r\n```\r\n`alias` in these instances is not used as part of a key.  Because these columns are not part of any key, there is no reason that they need to be resized.  I recommend that they be left unaltered, thus avoiding the data loss issue completely.\r\n\r\n--------------------------------------------------\r\n```\r\nALTER TABLE `#__categories` MODIFY `alias` varchar(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';\r\nALTER TABLE `#__tags` MODIFY `alias` varchar(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';\r\nALTER TABLE `#__ucm_content` MODIFY `core_alias` varchar(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';\r\n```\r\n`alias` is a key in these instances, but it is not unique.  `#__ucm_content.core_alias` is a key, but it, too, is not unique.  There is no real need to alter these column types, either.  Instead, just alter the key sizes.  For example,\r\n```\r\nALTER TABLE `#__categories` DROP KEY `idx_alias`, ADD KEY `idx_alias` (`alias`(191));\r\n```\r\nChances are good that people are not using aliases longer than 191.  But if they are, it's unlikely that they are using aliases where the first 191 characters are the same (and so look-ups using the key will still be quick).  But again, if they are using multiple aliases where the first 191 characters *are* the same, the lookup might be slowed by having to search for a match.  But it shouldn't really be a big deal, and a slower search would be preferable to truncating their aliases, which could possibly leave duplicate aliases.\r\n\r\n----------------------------------------------------\r\n```\r\nALTER TABLE `#__session` MODIFY `session_id` varchar(191) NOT NULL DEFAULT '';\r\nALTER TABLE `#__user_keys` MODIFY `series` varchar(191) NOT NULL;\r\n```\r\nThe `#__session.session_id` is a primary key. (It originally was `varchar(200)`.)\r\n`#__user_keys.series` is a primary key.\r\n\r\nWhen the column is part of a unique key (and primary keys are unique), then the issue is less clear.  You could leave the column size unchanged and just shorten the key, but that still requires that keys be unique within the first 191 characters, so I can't imagine keeping the characters beyond that is helpful.\r\n\r\nI think `session_id`s are, in practice, relatively short (< 30), so I see no problem with resizing that column.  For that matter, it's never going to contain utf8mb4 characters, so another alternative is to not convert it.\r\n\r\nI really don't know anything about the `series` column.  I'm guessing that it is not based on user input.  It probably(?) is unharmed by shortening it.  But maybe it doesn't need converting, either.\r\n\r\n---------------------------------------------------------\r\n```\r\nALTER TABLE `#__menu` MODIFY `alias` varchar(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The SEF alias of the menu item.';\r\nALTER TABLE `#__menu` DROP KEY `idx_client_id_parent_id_alias_language`, ADD UNIQUE KEY `idx_client_id_parent_id_alias_language` (`client_id`,`parent_id`,`alias`(191),`language`);\r\n```\r\n`alias` is part of a unique key, and that key is modified as well, as shown above.\r\n\r\nBased on what I've already said, I doubt it would be a problem to leave the `alias` column as `varchar(255)`.  That avoids loosing data.  However, users will be required to make the first 191 characters unique (uniqueness beyond 191 doesn't count).\r\n\r\nEither way this is handled, the key must be shortened if the column is to hold utf8mb4 characters.  And shortening the unique key means there is the possibility that duplicates are found when looking at only the first 191 characters.  If that happens the `ALTER TABLE` will fail.  That sort of failure has not really been addressed.  It probably should be caught so that it does not abort later queries that follow.  But the risk is small because -- who uses 191 character aliases?\r\n\r\n-----------------------------------------------------------\r\nAlso of note is:\r\n```\r\nALTER TABLE `#__redirect_links` DROP KEY `idx_link_old`, ADD UNIQUE KEY `idx_link_old` (`old_url`(191));\r\n```\r\n`old_url` is used as a unique key, but its type remains unchanged:  `varchar(255)`.  So there is no possible data loss.  But the issue mentioned above applies here, too.  If shortening a unique key results in duplicates found when looking at only the first 191 characters, the `ALTER TABLE` query will fail, possibly aborting later queries.  I would think it better to gracefully catch the error (or detect the problem beforehand) and leave the column as utf8.  Also note that `old_url` might seem like it (being an \"URL\") would not contain utf8mb4 characters, but I don't think that is guaranteed.  The url is first passed through `rawurldecode()` before it is stored."}}