[Summary view] [Print] [Text view]

   1  --
   2  -- Step 1: Convert all tables to utf8mb4 character set with utf8mb4_unicode_ci collation
   3  -- except of #__finder_xxx tables, those are handled with 4.0.0-2018-07-29.sql at update.
   4  --
   5  
   6  ALTER TABLE `#__action_logs` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   7  ALTER TABLE `#__action_logs_extensions` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   8  ALTER TABLE `#__action_logs_users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   9  ALTER TABLE `#__action_log_config` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  10  ALTER TABLE `#__assets` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  11  ALTER TABLE `#__associations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  12  ALTER TABLE `#__banners` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  13  ALTER TABLE `#__banner_clients` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  14  ALTER TABLE `#__banner_tracks` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  15  ALTER TABLE `#__categories` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  16  ALTER TABLE `#__contact_details` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  17  ALTER TABLE `#__content` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  18  ALTER TABLE `#__content_frontpage` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  19  ALTER TABLE `#__content_rating` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  20  ALTER TABLE `#__content_types` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  21  ALTER TABLE `#__contentitem_tag_map` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  22  ALTER TABLE `#__extensions` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  23  ALTER TABLE `#__fields` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  24  ALTER TABLE `#__fields_categories` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  25  ALTER TABLE `#__fields_groups` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  26  ALTER TABLE `#__fields_values` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  27  ALTER TABLE `#__history` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  28  ALTER TABLE `#__languages` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  29  ALTER TABLE `#__menu` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  30  ALTER TABLE `#__menu_types` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  31  ALTER TABLE `#__messages` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  32  ALTER TABLE `#__messages_cfg` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  33  ALTER TABLE `#__modules` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  34  ALTER TABLE `#__modules_menu` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  35  ALTER TABLE `#__newsfeeds` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  36  ALTER TABLE `#__overrider` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  37  ALTER TABLE `#__postinstall_messages` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  38  ALTER TABLE `#__privacy_consents` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  39  ALTER TABLE `#__privacy_requests` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  40  ALTER TABLE `#__redirect_links` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  41  ALTER TABLE `#__schemas` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  42  ALTER TABLE `#__session` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  43  ALTER TABLE `#__tags` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  44  ALTER TABLE `#__template_overrides` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  45  ALTER TABLE `#__template_styles` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  46  ALTER TABLE `#__ucm_base` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  47  ALTER TABLE `#__ucm_content` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  48  ALTER TABLE `#__updates` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  49  ALTER TABLE `#__update_sites` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  50  ALTER TABLE `#__update_sites_extensions` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  51  ALTER TABLE `#__usergroups` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  52  ALTER TABLE `#__users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  53  ALTER TABLE `#__user_keys` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  54  ALTER TABLE `#__user_notes` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  55  ALTER TABLE `#__user_profiles` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  56  ALTER TABLE `#__user_usergroup_map` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  57  ALTER TABLE `#__utf8_conversion` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  58  ALTER TABLE `#__viewlevels` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  59  
  60  --
  61  -- Step 2: Set collation to utf8mb4_bin for formerly utf8_bin collated columns
  62  -- and for the lang_code column of the languages table
  63  --
  64  
  65  ALTER TABLE `#__banners` MODIFY `alias` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
  66  ALTER TABLE `#__categories` MODIFY `alias` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
  67  ALTER TABLE `#__contact_details` MODIFY `alias` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
  68  ALTER TABLE `#__content` MODIFY `alias` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
  69  ALTER TABLE `#__languages` MODIFY `lang_code` char(7) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;
  70  ALTER TABLE `#__menu` MODIFY `alias` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'The SEF alias of the menu item.';
  71  ALTER TABLE `#__newsfeeds` MODIFY `alias` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
  72  ALTER TABLE `#__tags` MODIFY `alias` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
  73  ALTER TABLE `#__ucm_content` MODIFY `core_alias` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
  74  
  75  --
  76  -- Step 3: Set default character set and collation for all tables
  77  --
  78  
  79  ALTER TABLE `#__action_logs` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  80  ALTER TABLE `#__action_logs_extensions` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  81  ALTER TABLE `#__action_logs_users` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  82  ALTER TABLE `#__action_log_config` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  83  ALTER TABLE `#__assets` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  84  ALTER TABLE `#__associations` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  85  ALTER TABLE `#__banners` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  86  ALTER TABLE `#__banner_clients` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  87  ALTER TABLE `#__banner_tracks` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  88  ALTER TABLE `#__categories` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  89  ALTER TABLE `#__contact_details` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  90  ALTER TABLE `#__content` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  91  ALTER TABLE `#__content_frontpage` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  92  ALTER TABLE `#__content_rating` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  93  ALTER TABLE `#__content_types` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  94  ALTER TABLE `#__contentitem_tag_map` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  95  ALTER TABLE `#__extensions` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  96  ALTER TABLE `#__fields` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  97  ALTER TABLE `#__fields_categories` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  98  ALTER TABLE `#__fields_groups` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  99  ALTER TABLE `#__fields_values` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 100  ALTER TABLE `#__history` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 101  ALTER TABLE `#__languages` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 102  ALTER TABLE `#__menu` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 103  ALTER TABLE `#__menu_types` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 104  ALTER TABLE `#__messages` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 105  ALTER TABLE `#__messages_cfg` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 106  ALTER TABLE `#__modules` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 107  ALTER TABLE `#__modules_menu` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 108  ALTER TABLE `#__newsfeeds` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 109  ALTER TABLE `#__overrider` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 110  ALTER TABLE `#__postinstall_messages` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 111  ALTER TABLE `#__privacy_consents` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 112  ALTER TABLE `#__privacy_requests` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 113  ALTER TABLE `#__redirect_links` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 114  ALTER TABLE `#__schemas` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 115  ALTER TABLE `#__session` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 116  ALTER TABLE `#__tags` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 117  ALTER TABLE `#__template_overrides` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 118  ALTER TABLE `#__template_styles` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 119  ALTER TABLE `#__ucm_base` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 120  ALTER TABLE `#__ucm_content` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 121  ALTER TABLE `#__updates` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 122  ALTER TABLE `#__update_sites` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 123  ALTER TABLE `#__update_sites_extensions` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 124  ALTER TABLE `#__usergroups` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 125  ALTER TABLE `#__users` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 126  ALTER TABLE `#__user_keys` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 127  ALTER TABLE `#__user_notes` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 128  ALTER TABLE `#__user_profiles` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 129  ALTER TABLE `#__user_usergroup_map` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 130  ALTER TABLE `#__utf8_conversion` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 131  ALTER TABLE `#__viewlevels` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;