Are you stuck with the error message ‘SQL error 1071’ and looking for a solution? We can help you resolve this SQL error

This SQL error mainly occurs if the combined key is too long. So by adjusting the varchar value, we can resolve this error message.

Here at Bobcares, we have seen several such SQL-related errors as part of our Server Management Services for web hosts and online service providers.

Today, let us see why this SQL error message occurs and also take a look at how our Support Engineers resolve this error.

How we resolve ‘SQL error 1071’

Now let us take a look at how our Support Engineers resolve this SQL error message for our customers.

1. Recently, one of our customers came across the error message ‘#1071 – Specified key was too long; max key length 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 it is clear that the combined key is too long. So we need to either make separate keys or 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 suggested our customer reduce the single varchar length or not use a composite key.

So our customer resolved this error by reducing the varchar value to 128.

2. Another way to fix such an error is to add the below lines in /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, we can restart the SQL service.

[Need any further assistance with SQL-related errors? – We are here to help you.]

Conclusion

In short, this ‘SQL error 1071’ mainly occurs if the combined key is too long, and adjusting the varchar value must resolve this error. Today, we saw how our Support Engineers resolve this SQL error.