-1

I have a new wampserver installation on a windows 10 machine and I am trying to import a previously existing database containing around 10 different tables into a new database via phpmyadmin.

When I run a 'create table' command for a specific table in the sql query editor I receive the following error:

#1071 - Specified key was too long; max key length is 1000 bytes

This is the command that I am running:

DROP TABLE IF EXISTS `subscription_items`;
CREATE TABLE IF NOT EXISTS `subscription_items` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `subscription_id` bigint(20) UNSIGNED NOT NULL,
  `stripe_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `stripe_plan` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `subscription_items_subscription_id_stripe_plan_unique` (`subscription_id`,`stripe_plan`) USING HASH,
  KEY `subscription_items_stripe_id_index` (`stripe_id`(250))
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Any ideas as to which setting could be changed to enable this error to disappear? I have tried looking in the my.ini file but cannot spot the setting to alter. Also this database worked in a previous older installation of wamp.

ADyson
  • 57,178
  • 14
  • 51
  • 63
Robert Young
  • 523
  • 1
  • 8
  • 22
  • Have you gone through https://stackoverflow.com/questions/8746207/1071-specified-key-was-too-long-max-key-length-is-1000-bytes yet? – CBroe Jan 26 '23 at 11:47
  • Why is this tagged under laravel? you are not even using laravel migrations. – RG Servers Jan 26 '23 at 11:57
  • The key in error is `subscription_items_subscription_id_stripe_plan_unique` https://dbfiddle.uk/XwekVOC_ – P.Salmon Jan 26 '23 at 12:00
  • 'The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. ' - https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html#:~:text=The%20maximum%20number%20of%20indexes,changing%20the%20source%20and%20recompiling. Clearly 255 * 4 + bigint > 100 do you need all 255? – P.Salmon Jan 26 '23 at 12:20
  • Thanks for the responses here. I removed the 'UNIQUE KEY' constraints from the affected tables in question, and the problem seemed to go away. – Robert Young Jan 26 '23 at 12:48
  • @Shadow, you marked this question as a duplicate, but the question you linked was about InnoDB. The OP in this question is about MyISAM, which also has a key length limit, but the size limit is different. – Bill Karwin Jan 26 '23 at 15:07
  • @BillKarwin the logic is the same for both myisam and innodb how the index length is calculated and what approaches you can use to avoid hitting the limit. – Shadow Jan 26 '23 at 16:45

1 Answers1

2

No setting can change this limit of MyISAM tables.

https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html says:

The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling.

Your indexes columns are 8 bytes for the bigint, plus 255 * 4 bytes per character for the string. For indexing purposes, it has to assume you might use the full length of the utf8mb4 string in some row, even if you haven't done so yet. That totals 1028 bytes.

You could fix this by using varchar(248) or shorter.

You can also fix this by using InnoDB instead of MyISAM. InnoDB supports up to 3072 bytes for an index length in recent versions of MySQL.

I recommend using InnoDB instead of MyISAM regardless.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828