-2

I want to import a sql file in my database getting this error 1071, Specified key was too long; max key length is 1000 bytes

here is my code for related table.

DROP TABLE IF EXISTS `model_has_permissions`;

CREATE TABLE `model_has_permissions` (
  `permission_id` int(10) unsigned NOT NULL,
  `model_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `model_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`permission_id`,`model_id`,`model_type`),
  KEY `model_has_permissions_model_id_model_type_index` (`model_id`,`model_type`),
  CONSTRAINT `model_has_permissions_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
M. Eriksson
  • 13,450
  • 4
  • 29
  • 40
Mustaque Ahmed
  • 125
  • 1
  • 3
  • 13
  • 1
    This question has the answer here[1072 specified key was too long.](https://stackoverflow.com/a/8747703/8240120) – farooq Sep 10 '19 at 11:41
  • 1
    `utf8mb4` uses up to _four_ bytes to store a character, so `varchar(255)` gets you up to 1,020 already. – misorude Sep 10 '19 at 11:41
  • I am reopening this because the "dup" answer does not apply. You can't use prefix indexing on the `PRIMARY KEY` without causing more problems. – Rick James Sep 10 '19 at 16:24

1 Answers1

2

Plan A: Don't use more than 191 for number of characters in model_type.

Plan B: Normalize model_type.

Plan C: Change model_type to `CHARACTER SET ascii, if appropriate.

Other options: http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

Rick James
  • 135,179
  • 13
  • 127
  • 222