2

When I try to create a table:

CREATE TABLE `oro_workflow_restriction` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `workflow_step_id` int(11) DEFAULT NULL,
  `workflow_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `attribute` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `field` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `entity_class` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mode` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mode_values` longtext COLLATE utf8mb4_unicode_ci COMMENT '(DC2Type:json_array)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `oro_workflow_restriction_idx` (`workflow_name`,`workflow_step_id`,`field`,`entity_class`,`mode`),
  KEY `IDX_3D2C17E271FE882C` (`workflow_step_id`),
  KEY `IDX_3D2C17E21BBC6E3D` (`workflow_name`),
  CONSTRAINT `FK_3D2C17E21BBC6E3D` FOREIGN KEY (`workflow_name`) REFERENCES `oro_workflow_definition` (`name`) ON DELETE CASCADE,
  CONSTRAINT `FK_3D2C17E271FE882C` FOREIGN KEY (`workflow_step_id`) REFERENCES `oro_workflow_step` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I got an error about Specified key was too long; max key length is 3072 bytes.

I don't see why would I get the error. The longest key length is the unique key: 4 + 4 * 255*3 = 3071 bytes < 3072 bytes. (if my calculation is correct)

Clearly utf8mb4 is 4 bytes per character, I can have only varchar(191) at maximum. In my case it is 4 + 4 * 255*4 = 4084 bytes > 3072 bytes.

Is there a way that doesn't require to change the data type to get it working?

Jason Liu
  • 749
  • 4
  • 16
  • 34

2 Answers2

1

Two solutions

By Dropping the Unique Constraint

If you can drop the unique constraint. Enable full-text indexing for search purposes, but you won't be able to enforce unique constraints.

By Normalizing the Schema

Put the varchar(255) columns out of this table and store ids from referred tables. These will be integers. And with integers, composite keys will work just fine.

I think MySQL has done a good job limiting this to 3072 bytes. Otherwise, people would create all kinds of useless indexes that would lead to a performance bottleneck.

MontyPython
  • 2,906
  • 11
  • 37
  • 58
  • 1
    You are right. As you can see the OroCRM is using `name` as the reference of the foreign key which isn't good. Changing the keys will ends up something that we might not able to support the newer version of OroCRM. I guess I have to turn it back to `uft8` instead of `utf8mb4`. That means the system won't able to support some special characters. – Jason Liu Oct 16 '18 at 19:53
0

The charset is important.

Specifiyng VARCHAR(1000) when having column charset UTF or alike means that the effective field size is 4 times bigger, as UTF characters are in size of 4 bytes.

The allowed index field size is 3072, thus VARCHAR(1000) of UTF charset means effective size of 4 x 1000 = 4000 bytes.

buddemat
  • 4,552
  • 14
  • 29
  • 49
Daniel Péder
  • 1
  • 1