Usually, the SQL error occurs if the combined key is too long. You can resolve the SQL error by adjusting the varchar value.
As a part of our server management services, we use to fix AWS the SQL error 1071 for the web hosts and online service providers. For further detail information, you can contact our technical team support for any assistance.
How to resolve the SQL error 1071
If the specified key is too long then you will come across a pop-up message error, where the Key length max is 767 bytes’ while running the below query.
CREATE TABLE wp_locations (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`place` VARCHAR(255) NOT NULL,
`name` VARCHAR(255) NOT NULL,
CONSTRAINT `place_name` UNIQUE (`city`, `name`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Here the error is apparent where the combined key is too long. so required to separate the keys or either you have to reduce the column lengths.
Generally, MySQL always reserves the max amount for a UTF8 field which is 4 bytes so with 255 + 255 with the DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; this query is over the 767 max key length limit.
So we always suggest our clients reduce the length of single varchar or avoid the composite key.
Either you can resolve the error by adding
/etc/my.conf.d directory named umb4-support.cnf
[mysqld]
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=true
After that, restart the SQL service.
Conclusion
By this topic, you will get an apparent view to fixing the error 1071 which is mainly caused to the combined key which is too long. You can take our technical team support for any further clarification.
