Fixing the SQL error 1071

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 1071for 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 assistance team support for any further clarification.

More Posts

Send Us A Message

Prevent your Website/Server from Downtimes

Get our experts to manage your server with weekly auditing and 24/7 monitoring