Mysql ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

Question

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?

Answer 1

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.

Answer 2

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.