Uploaded image for project: 'eZ Publish / Platform'
  1. eZ Publish / Platform
  2. EZP-29110

Changing tables charset to utf8mb4 result in "Specified key is too long" errors

    Details

      Description

      Performing the steps described in https://github.com/ezsystems/ezpublish-kernel/blob/5f2a94517267298fba58e066420107d112721bd3/doc/upgrade/7.2.md#mysqlmariadb-database-tables-character-set-change (also described in patch EZPESU-2018-003-KERNEL5.4) on DBMS MySQL 5.6 or lower (MySQL 5.7 is not affected) result in the following warnings on some of the tables (for example ezbasket):

      1 row(s) affected, 1 warning(s): 1071 Specified key was too long; max key length is 767 bytes Records: 1  Duplicates: 0  Warnings: 1
      

      The warnings can become errors in some cases (this probably depends on configuration).
      The reason for this is that the following script: https://github.com/ezsystems/ezpublish-kernel/blob/5f2a94517267298fba58e066420107d112721bd3/data/update/mysql/dbupdate-7.1.0-to-7.2.0.sql doesn't cover all the indexes that need to be shortened.

      Steps to reproduce

      1. Prepare new eZ Publish/eZ Platform installation with a clean database. Use MySQL 5.6 or lower.
      2. Perform the steps described in https://github.com/ezsystems/ezpublish-kernel/blob/5f2a94517267298fba58e066420107d112721bd3/doc/upgrade/7.2.md#mysqlmariadb-database-tables-character-set-change
      3. The warning described above will appear for some of the tables when executing the following query:

        ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
        

        Issue Links

          Activity

          Show
          Jacek Foremski (Inactive) added a comment - Documentation PR: https://github.com/ezsystems/developer-documentation/pull/287
          Hide
          Jacek Foremski (Inactive) added a comment -

          Documentation PR Merged.

          Show
          Jacek Foremski (Inactive) added a comment - Documentation PR Merged.
          Hide
          Gunnstein Lye added a comment -

          It seems this is still not complete. Further "key is too long" errors have been reported on slack. We'll have to review all tables again...

          Show
          Gunnstein Lye added a comment - It seems this is still not complete. Further "key is too long" errors have been reported on slack. We'll have to review all tables again...
          Hide
          Gunnstein Lye added a comment -

          Reported omissions:

          ALTER TABLE `ezcontentobject_tree` DROP KEY `ezcontentobject_tree_path_ident`;
          ALTER TABLE `ezcontentobject_tree` ADD KEY `ezcontentobject_tree_path_ident` (`path_identification_string` (50));
          ALTER TABLE `ezcontentobject_tree` DROP KEY `ezcontentobject_tree_contentobject_id_path_string`;
          ALTER TABLE `ezcontentobject_tree` ADD KEY `ezcontentobject_tree_contentobject_id_path_string` (`path_string` (191), `contentobject_id`);
          

          Show
          Gunnstein Lye added a comment - Reported omissions: ALTER TABLE `ezcontentobject_tree` DROP KEY `ezcontentobject_tree_path_ident`; ALTER TABLE `ezcontentobject_tree` ADD KEY `ezcontentobject_tree_path_ident` (`path_identification_string` (50)); ALTER TABLE `ezcontentobject_tree` DROP KEY `ezcontentobject_tree_contentobject_id_path_string`; ALTER TABLE `ezcontentobject_tree` ADD KEY `ezcontentobject_tree_contentobject_id_path_string` (`path_string` (191), `contentobject_id`);
          Hide
          Gunnstein Lye added a comment -

          The omissions above seem invalid, they're referring to indexes that no longer exist by those names or already have the given limit. Possibly the remains of an older, (partially?) upgraded site.

          Show
          Gunnstein Lye added a comment - The omissions above seem invalid, they're referring to indexes that no longer exist by those names or already have the given limit. Possibly the remains of an older, (partially?) upgraded site.

            People

            • Assignee:
              Unassigned
              Reporter:
              Jacek Foremski (Inactive)
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: