[BAP-18992] v4.1.2 UTF8MB4 MySQL/MariaDB index key length overflow

[chrisleup]

StackOverflow discussion here

During install of 4.1.2, while performing database creation, script fails with

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes

during:

Oro\Bundle\DataAuditBundle\Migrations\Schema\OroDataAuditBundleInstaller
    ERROR: An exception occurred while executing 'CREATE TABLE oro_audit (id INT AUTO_INCREMENT NOT NULL, user_id INT DEFAULT NULL, organization_id INT DEFAULT NULL, impersonation_id INT DEFAULT NULL, action VARCHAR(8) DEFAULT NULL, logged_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime)', object_id VARCHAR(255) DEFAULT NULL, object_class VARCHAR(255) NOT NULL, object_name VARCHAR(255) DEFAULT NULL, version INT DEFAULT NULL, type VARCHAR(255) NOT NULL, transaction_id VARCHAR(255) NOT NULL, owner_description VARCHAR(255) DEFAULT NULL, additional_fields LONGTEXT DEFAULT NULL COMMENT '(DC2Type:array)', INDEX IDX_5FBA427CA76ED395 (user_id), INDEX idx_oro_audit_type (type), UNIQUE INDEX idx_oro_audit_version (object_id, object_class, version, type), UNIQUE INDEX idx_oro_audit_transaction (object_id, object_class, transaction_id, type), INDEX idx_oro_audit_logged_at (logged_at), INDEX idx_oro_audit_object_class (object_class), INDEX idx_oro_audit_obj_by_type (object_id, object_class, type), INDEX idx_oro_audit_owner_descr (owner_description), INDEX idx_oro_audit_organization_id (organization_id), INDEX IDX_5FBA427C26F87DB8 (impersonation_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB':

when Doctrine/DBAL is configured via oroapp/config/config.yml with:

doctrine:
    dbal:
        charset: utf8mb4
        default_table_options:
            charset: utf8mb4
            collate: utf8mb4_unicode_ci

• OroCommerce/Platform version: 4.1.2
• PHP version: 7.3.16
• Database: MySQL 5.7.29 and MariaDB 10.4.12

[mbessolov]

@chrisleup thank you for the bug report. It has been fixed in c3ceac23ae747163389b3698ea2ebadd80d2385e and will be included in the next 4.1.x release as well.

[mchojrin]

Hi:

I'm having the same issue migrating from 3.1.17 to 4.1.4.

My setup is:

• MySQL 5.7.29
• php 7.4.6

I'm running the upgrade on the VM provided with 3.1 as a rehearsal before going to prod (I manually upgraded php & nodejs).

I just run php bin/console debug:config doctrine -e prod and found I have no particular configuration for doctrine:

doctrine:
    dbal:
            config:
                driver: pdo_mysql
                host: 127.0.0.1
                port: null
                dbname: oro
                user: dbuser
                password: DBP@ssword123
                options: {  }
                charset: utf8
                logging: true
                profiling: false
                mapping_types: {  }
                default_table_options: {  }
                slaves: {  }
                shards: {  }

Any tips? Thanks!

[chrisleup]

What charset are you migrating from?

On Sun, Jun 7, 2020 at 6:12 PM Mauro Chojrin notifications@github.com wrote:

Hi:

I'm having the same issue migrating from 3.1.17 to 4.1.4.

My setup is:

• MySQL 5.7.29
• php 7.4.6

I'm running the upgrade on the VM provided with 3.1 as a rehearsal before going to prod (I manually upgraded php & nodejs).

I just run php bin/console debug:config doctrine -e prod and found I have no particular configuration for doctrine:

doctrine: dbal: config: driver: pdo_mysql host: 127.0.0.1 port: null dbname: oro user: dbuser password: DBP@ssword123 options: { } charset: utf8 logging: true profiling: false mapping_types: { } default_table_options: { } slaves: { } shards: { }

Any tips? Thanks!

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/oroinc/platform/issues/1013#issuecomment-640293971, or unsubscribe https://github.com/notifications/unsubscribe-auth/AMT32GR24S7Y7VP4HHQRI33RVQNEVANCNFSM4MQLKNDA .

[mchojrin]

Thanks @chrisleup , I'm not sure...

This is the output from show create table oro_audit:

CREATE TABLE `oro_audit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `organization_id` int(11) DEFAULT NULL,
  `impersonation_id` int(11) DEFAULT NULL,
  `action` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `logged_at` datetime DEFAULT NULL COMMENT '(DC2Type:datetime)',
  `object_id` int(11) DEFAULT NULL,
  `object_class` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `object_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `version` int(11) DEFAULT NULL,
  `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `transaction_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner_description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `additional_fields` longtext COLLATE utf8mb4_unicode_ci COMMENT '(DC2Type:array)',
  `customer_user_id` int(11) DEFAULT NULL,
  `entity_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_oro_audit_version` (`object_id`,`entity_id`,`object_class`,`version`),
  KEY `IDX_5FBA427CA76ED395` (`user_id`),
  KEY `idx_oro_audit_type` (`type`),
  KEY `idx_oro_audit_logged_at` (`logged_at`),
  KEY `idx_oro_audit_object_class` (`object_class`),
  KEY `idx_oro_audit_obj_by_type` (`object_id`,`object_class`,`type`),
  KEY `idx_oro_audit_owner_descr` (`owner_description`),
  KEY `idx_oro_audit_organization_id` (`organization_id`),
  KEY `IDX_5FBA427C26F87DB8` (`impersonation_id`),
  KEY `IDX_5FBA427CBBB3772B` (`customer_user_id`),
  KEY `idx_oro_audit_ent_by_type` (`entity_id`,`object_class`,`type`),
  CONSTRAINT `fk_oro_audit_customer_user_id` FOREIGN KEY (`customer_user_id`) REFERENCES `oro_customer_user` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_oro_audit_impersonation_id` FOREIGN KEY (`impersonation_id`) REFERENCES `oro_user_impersonation` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_oro_audit_organization_id` FOREIGN KEY (`organization_id`) REFERENCES `oro_organization` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_oro_audit_user_id` FOREIGN KEY (`user_id`) REFERENCES `oro_user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

I believe it's utf8mb4, rigth?

This is the result of show create database oro;:

CREATE DATABASEoro/*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */

[mchojrin]

I tried upgrading to MySQL 5.7.30 and I got the same result

[anyt]

@mchojrin, It looks like you have to enable back utf8mb4 encoding: https://doc.oroinc.com/backend/setup/system-requirements/database-optimization/#usage-of-the-utf8mb4-character-set-the-full-4-byte-utf-8-unicode-encoding-in-mysql Don't forget to update the config/config.yml file.

[mchojrin]

@anyt thanks. I checked those parameters in /etc/my.cnf and they are exactly as pointed in the article.

For the time being I'm going to try to do the upgrade to 4.1.1..

[anyt]

What about the config/config.yml file in an application? If you shared the full output of php bin/console debug:config doctrine -e prod, it looks like you are missing this config:

doctrine:
    dbal:
        charset: utf8mb4
        default_table_options:
            charset: utf8mb4
            collate: utf8mb4_unicode_ci

[mchojrin]

I added that but didn't have any luck I'm affraid :(

I'm going to use 4.1.1 for the time being (it's still going to be an advance from my current 3.1.17 anyway) and then go back to 4.1.4.

Right now I'm finding some issues updating my custom bundles...

Thanks