wesupport

Need help?

Our experts have had an average response time of 13.14 minutes in February 2024 to fix urgent issues.

We will keep your servers stable, secure, and fast at all times for one fixed price.

SQL error 1071 – Quick solution

by | Mar 15, 2021

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.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = "owonCMyG5nEQ0aD71QM";

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Categories

Tags