Upgrading your database from ezplatform 2.1 to 2.2. Below you'll find some sql queries to execute and a shell script to convert your table to use utf8mb4. More Information for the upgrade steps : https://doc.ezplatform.com/en/2.2/releases/updating_ez_platform/#4-update-database

01_ezplatform_db_upgrade_v2.1_to_v2.2.sql

--
-- Avoid creating eznotification table again in enterprise edition, this table is already exist in 2.1. This could break the script and cause some key error when the script is executing once again
-- For Community edition see below "add_eznotification.sql"
--

SET default_storage_engine=InnoDB;
BEGIN;
-- Set storage engine schema version number
UPDATE ezsite_data SET value='7.2.0' WHERE name='ezpublish-version';

--
-- EZP-28950: MySQL UTF8 doesn't support 4-byte chars
-- This shortens indexes so that 4-byte content can fit.
-- After running these, convert the table character set, see doc/upgrade/7.2.md
--

ALTER TABLE `ezbasket` DROP KEY `ezbasket_session_id`;
ALTER TABLE `ezbasket` ADD KEY `ezbasket_session_id` (`session_id` (191));

ALTER TABLE `ezcollab_group` DROP KEY `ezcollab_group_path`;
ALTER TABLE `ezcollab_group` ADD KEY `ezcollab_group_path` (`path_string` (191));

ALTER TABLE `ezcontent_language` DROP KEY `ezcontent_language_name`;
ALTER TABLE `ezcontent_language` ADD KEY `ezcontent_language_name` (`name` (191));

ALTER TABLE `ezcontentobject_attribute` DROP KEY `sort_key_string`;
ALTER TABLE `ezcontentobject_attribute` ADD KEY `sort_key_string` (`sort_key_string` (191));

ALTER TABLE `ezcontentobject_name` DROP KEY `ezcontentobject_name_name`;
ALTER TABLE `ezcontentobject_name` ADD KEY `ezcontentobject_name_name` (`name` (191));

ALTER TABLE `ezcontentobject_trash` DROP KEY `ezcobj_trash_path`;
ALTER TABLE `ezcontentobject_trash` ADD KEY `ezcobj_trash_path` (`path_string` (191));

ALTER TABLE `ezcontentobject_tree` DROP KEY `ezcontentobject_tree_path`;
ALTER TABLE `ezcontentobject_tree` ADD KEY `ezcontentobject_tree_path` (`path_string` (191));

ALTER TABLE `ezimagefile` DROP KEY `ezimagefile_file`;
ALTER TABLE `ezimagefile` ADD KEY `ezimagefile_file` (`filepath` (191));

ALTER TABLE `ezkeyword` DROP KEY `ezkeyword_keyword`;
ALTER TABLE `ezkeyword` ADD KEY `ezkeyword_keyword` (`keyword` (191));

ALTER TABLE `ezorder_status` DROP KEY `ezorder_status_name`;
ALTER TABLE `ezorder_status` ADD KEY `ezorder_status_name` (`name` (191));

ALTER TABLE `ezpolicy_limitation_value` DROP KEY `ezpolicy_limitation_value_val`;
ALTER TABLE `ezpolicy_limitation_value` ADD KEY `ezpolicy_limitation_value_val` (`value` (191));

ALTER TABLE `ezprest_authcode` DROP PRIMARY KEY;
ALTER TABLE `ezprest_authcode` ADD PRIMARY KEY (`id` (191));

ALTER TABLE `ezprest_authcode` DROP KEY `authcode_client_id`;
ALTER TABLE `ezprest_authcode` ADD KEY `authcode_client_id` (`client_id` (191));

ALTER TABLE `ezprest_clients` DROP KEY `client_id_unique`;
ALTER TABLE `ezprest_clients` ADD UNIQUE KEY `client_id_unique` (`client_id` (191),`version`);

ALTER TABLE `ezprest_token` DROP PRIMARY KEY;
ALTER TABLE `ezprest_token` ADD PRIMARY KEY (`id` (191));

ALTER TABLE `ezprest_token` DROP KEY `token_client_id`;
ALTER TABLE `ezprest_token` ADD KEY `token_client_id` (`client_id` (191));

ALTER TABLE `ezsearch_object_word_link` DROP KEY `ezsearch_object_word_link_identifier`;
ALTER TABLE `ezsearch_object_word_link` ADD KEY `ezsearch_object_word_link_identifier` (`identifier` (191));

ALTER TABLE `ezsearch_search_phrase` DROP KEY `ezsearch_search_phrase_phrase`;
ALTER TABLE `ezsearch_search_phrase` ADD UNIQUE KEY `ezsearch_search_phrase_phrase` (`phrase` (191));

ALTER TABLE `ezurl` DROP KEY `ezurl_url`;
ALTER TABLE `ezurl` ADD KEY `ezurl_url` (`url` (191));

ALTER TABLE `ezurlalias` DROP KEY `ezurlalias_desturl`;
ALTER TABLE `ezurlalias` ADD KEY `ezurlalias_desturl` (`destination_url` (191));

ALTER TABLE `ezurlalias` DROP KEY `ezurlalias_source_url`;
ALTER TABLE `ezurlalias` ADD KEY `ezurlalias_source_url` (`source_url` (191));

--
-- EZP-29146: As a developer, I want a API to manage bookmarks
--

ALTER TABLE `ezcontentbrowsebookmark`
ADD INDEX `ezcontentbrowsebookmark_user_location` (`node_id`, `user_id`);

ALTER TABLE `ezcontentbrowsebookmark`
ADD INDEX `ezcontentbrowsebookmark_location` (`node_id`);

ALTER TABLE `ezcontentbrowsebookmark`
ADD CONSTRAINT `ezcontentbrowsebookmark_location_fk`
  FOREIGN KEY (`node_id`)
  REFERENCES `ezcontentobject_tree` (`node_id`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;

ALTER TABLE `ezcontentbrowsebookmark`
ADD CONSTRAINT `ezcontentbrowsebookmark_user_fk`
  FOREIGN KEY (`user_id`)
  REFERENCES `ezuser` (`contentobject_id`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;



COMMIT;

-- If the queries below fail, it means database is already updated

CREATE INDEX `ezcontentobject_tree_contentobject_id_path_string` ON `ezcontentobject_tree` (`path_string`, `contentobject_id`);
CREATE INDEX `ezcontentobject_section` ON `ezcontentobject` (`section_id`);

add_eznotification.sql

--
-- EZEE-2081: Move NotificationBundle into AdminUI. 
-- Only for open source version upgrade 
--

CREATE TABLE `eznotification` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `owner_id` int(11) NOT NULL DEFAULT 0,
  `is_pending` tinyint(1) NOT NULL DEFAULT '1',
  `type` varchar(128) NOT NULL DEFAULT '',
  `created` int(11) NOT NULL DEFAULT 0,
  `data` blob,
  PRIMARY KEY (`id`),
  KEY `eznotification_owner` (`owner_id`),
  KEY `eznotification_owner_is_pending` (`owner_id`, `is_pending`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

alter_db_collation.sql

-- Replace with your database name 
ALTER DATABASE <DATABASE_NAME> CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

config.yml

In app/config/config.yml, set the following:

doctrine:
    dbal:
        connections:
            default:
                charset: utf8mb4

Also make the corresponding change in app/config/dfs/dfs.yml.

convert_all_tables_to_utf8mb4.sh

#!/bin/bash

# Input data
USER="USERNAME"
PASSWORD="PASSWORD"
DB_NAME="DATABASE_NAME"
# END of Input data

CHARACTER_SET="utf8mb4" # your default character set
COLLATE="utf8mb4_general_ci" # your default collation

tables=`mysql -u $USER -p$PASSWORD -e "SELECT tbl.TABLE_NAME FROM information_schema.TABLES tbl WHERE tbl.TABLE_SCHEMA = '$DB_NAME' AND tbl.TABLE_TYPE='BASE TABLE'"`

for tableName in $tables; do
    if [[ "$tableName" != "TABLE_NAME" ]] ; then
        mysql -u $USER -p$PASSWORD -e "ALTER TABLE $DB_NAME.$tableName DEFAULT CHARACTER SET $CHARACTER_SET COLLATE $COLLATE;"
        echo "$tableName - done"
    fi
done

page_builder.sql

--
-- Page Builder
--

DROP TABLE IF EXISTS `ezpage_attributes`;
CREATE TABLE `ezpage_attributes` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `value` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `ezpage_blocks`;
CREATE TABLE `ezpage_blocks` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(255) NOT NULL DEFAULT '',
  `view` varchar(255) NOT NULL DEFAULT '',
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `ezpage_blocks_design`;
CREATE TABLE `ezpage_blocks_design` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `block_id` INT(11) NOT NULL,
  `style` TEXT DEFAULT NULL,
  `compiled` TEXT DEFAULT NULL,
  `class` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `ezpage_blocks_visibility`;
CREATE TABLE `ezpage_blocks_visibility` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `block_id` INT(11) NOT NULL,
  `since` INT(11) DEFAULT NULL,
  `till` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `ezpage_map_attributes_blocks`;
CREATE TABLE `ezpage_map_attributes_blocks` (
  `attribute_id` int(11) NOT NULL,
  `block_id` int(11) NOT NULL,
  PRIMARY KEY (`attribute_id`,`block_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `ezpage_map_blocks_zones`;
CREATE TABLE `ezpage_map_blocks_zones` (
  `block_id` int(11) NOT NULL,
  `zone_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `ezpage_map_zones_pages`;
CREATE TABLE `ezpage_map_zones_pages` (
  `zone_id` int(11) NOT NULL,
  `page_id` int(11) NOT NULL,
  PRIMARY KEY (`zone_id`,`page_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `ezpage_pages`;
CREATE TABLE `ezpage_pages` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `version_no` int(11) unsigned NOT NULL,
  `content_id` int(11) NOT NULL,
  `language_code` varchar(255) NOT NULL DEFAULT '',
  `layout` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `ezpage_zones`;
CREATE TABLE `ezpage_zones` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Hi, the page builder sql, ezpage_map_blocks_zones table doesn't have the primary key or an index on the block_id.

I'm not sure if the other way is also necessary but we at least know that without that one, a site can get offline under high load.

explain SELECT a.id, a.name, a.value, mab.block_id, mbz.zone_id 
FROM `ezpage_attributes` a 
LEFT JOIN `ezpage_map_attributes_blocks` mab 
    ON mab.attribute_id = a.id 
LEFT JOIN `ezpage_map_blocks_zones` mbz 
    ON mab.block_id = mbz.block_id 
LEFT JOIN `ezpage_map_zones_pages` mzp 
    ON mbz.zone_id = mzp.zone_id WHERE mzp.page_id = 'some_valid_page_id_here';

+------+-------------+-------+--------+---------------+---------+---------+----------------------------+-------+-------------------------------------------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref                        | rows  | Extra                                           |
+------+-------------+-------+--------+---------------+---------+---------+----------------------------+-------+-------------------------------------------------+
|    1 | SIMPLE      | a     | ALL    | PRIMARY       | NULL    | NULL    | NULL                       | 40000 |                                                 |
|    1 | SIMPLE      | mab   | ref    | PRIMARY       | PRIMARY | 4       | adacprod.a.id              |     1 | Using where; Using index                        |
|    1 | SIMPLE      | mbz   | ALL    | NULL          | NULL    | NULL    | NULL                       | 50000 | Using where; Using join buffer (flat, BNL join) |
|    1 | SIMPLE      | mzp   | eq_ref | PRIMARY       | PRIMARY | 8       | adacprod.mbz.zone_id,const |     1 | Using index                                     |
+------+-------------+-------+--------+---------------+---------+---------+----------------------------+-------+-------------------------------------------------+

One solution is adding the primary key to the table

ALTER TABLE `ezpage_map_blocks_zones` ADD PRIMARY KEY (`block_id`, `zone_id`);

or if the index on the other way is also needed for some other possible query

ALTER TABLE `ezplatform`.`ezpage_map_blocks_zones`
ADD INDEX `block_id_idx` (`block_id` ASC, `zone_id` ASC),
ADD INDEX `zone_id_idx` (`zone_id` ASC, `block_id` ASC);