Syntax error or access violation: 1071 Specified key was too long when running setup:upgrade

Question

I get the following syntax error when running setup:upgrade after the update of a specific module through composer:

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes, query was: CREATE TABLE sendcloud_shipping_methods ( id int(11) NOT NULL AUTO_INCREMENT COMMENT "Primary key", store_view_id int(11) NOT NULL COMMENT "Store view ID", external_id varchar(300) NOT NULL COMMENT "External id", delivery_zone_id varchar(64) NOT NULL COMMENT "Delivery zone id", data longtext NULL COMMENT "Shipping method data", country varchar(100) NOT NULL COMMENT "Shipping method country", internal_title varchar(300) NOT NULL COMMENT "Shipping method internal title", external_title varchar(300) NOT NULL COMMENT "Shipping method external title", shipping_product varchar(300) NOT NULL COMMENT "Shipping method shipping product", rates_enabled tinyint(2) NOT NULL COMMENT "Shipping method rates enabled", delivery_method varchar(100) NOT NULL COMMENT "Shipping delivery method", CONSTRAINT PRIMARY KEY (id), CONSTRAINT SENDCLOUD_SHIPPING_METHODS_EXTERNAL_ID UNIQUE KEY (external_id), FULLTEXT INDEX SENDCLOUD_SHIPPING_METHODS_EXTERNAL_TITLE_INTERNAL_TITLE (external_title,internal_title) ) ENGINE=innodb DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_general_ci COMMENT="SendCloud Shipping Methods"

The hosting support told me to upgrade MySQL to version 5.7 to solve this problem. Isn't there another solution to this problem? Upgrading the DB would take a lot of time where the MySQL version meets the Magento (2.3.5) requirements.

Magento 2.3.5

PHP 7.1

MySQL 5.6 (collation utf8_general_ci)

Answer 1

The limit for UTF8 is 767/3 = 255 characters, for UTF8m see reference here

With this in mind, I'd ensure that these title fields are less that 255 together. How about changing from varchar(300) to varchar(100)?

one it would resolve your issue. More importantly, it is not great practice to have long fields in primary keys, it will only make your mysql server work harder..

Of course, do bear in mind, you may lose some data (your titles may get truncated). So, this solution is a shortcut for your upgrade to run. After, you may want to reimport your data using additional/custom fields.