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

[MySQL] ezcontentobject_tree_contentobject_id_path_string index column size is too large

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: 2.2.0, 2.4.2, 2.5.0-rc2
    • Fix Version/s: 2.5.0, 2.4.3
    • Component/s: Database related
    • Labels:
      None
    • Environment:

      Platform.sh with default MySQL settings

      Description

      When installing eZ Platform EE demo installation to certain hosting environments (e.g. platform.sh) the installation can be halted by a database error.

      When running:

      ./bin/console ezplatform:install ezplatform-ee-demo
      

      I experienced an error like this:

      An exception occurred while executing '                                                                  
      CREATE TABLE `ezcontentobject_tree` (                                                                    
      `contentobject_id` int(11) DEFAULT NULL,                                                               
      `contentobject_is_published` int(11) DEFAULT NULL,                                                     
      `contentobject_version` int(11) DEFAULT NULL,                                                          
      `depth` int(11) NOT NULL DEFAULT '0',                                                                  
      `is_hidden` int(11) NOT NULL DEFAULT '0',                                                              
      `is_invisible` int(11) NOT NULL DEFAULT '0',                                                           
      `main_node_id` int(11) DEFAULT NULL,                                                                   
      `modified_subnode` int(11) DEFAULT '0',                                                                
      `node_id` int(11) NOT NULL AUTO_INCREMENT,                                                             
      `parent_node_id` int(11) NOT NULL DEFAULT '0',                                                         
      `path_identification_string` longtext,                                                                 
      `path_string` varchar(255) NOT NULL DEFAULT '',                                                        
      `priority` int(11) NOT NULL DEFAULT '0',                                                               
      `remote_id` varchar(100) NOT NULL DEFAULT '',                                                          
      `sort_field` int(11) DEFAULT '1',                                                                      
      `sort_order` int(11) DEFAULT '1',                                                                      
      PRIMARY KEY (`node_id`),                                                                               
      KEY `ezcontentobject_tree_remote_id` (`remote_id`),                                                    
      KEY `ezcontentobject_tree_co_id` (`contentobject_id`),                                                 
      KEY `ezcontentobject_tree_depth` (`depth`),                                                            
      KEY `ezcontentobject_tree_p_node_id` (`parent_node_id`),                                               
      KEY `ezcontentobject_tree_path` (`path_string` (191)),                                                 
      KEY `ezcontentobject_tree_path_ident` (`path_identification_string`(50)),                              
      KEY `modified_subnode` (`modified_subnode`),                                                           
      KEY `ezcontentobject_tree_contentobject_id_path_string` (`path_string`, `contentobject_id`)            
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4':
      
      SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes. 
      

      As far as I understand this is because the automatically generated index names are too long for MySQL. The issue can be fixed by adding a specific definition in the schema: ROW_FORMAT=DYNAMIC

      The database needs to be configured correctly to support this, but in general I think these should be included in the MySQL schema by default as this worked fine when adding ROW_FORMAT=DYNAMIC to the SQL:

      ...
      KEY `modified_subnode` (`modified_subnode`),                                                           
      KEY `ezcontentobject_tree_contentobject_id_path_string` (`path_string`, `contentobject_id`)            
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC
      

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              jani.tarvainen@ez.no Jani Tarvainen
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 2 hours
                2h