SQL error 1071 - Fix it Now

Remote Linux Server Administrator
SQL Issues and Solutions
196

Are you facing the error message ‘SQL error 1071’ and looking for a solution?

The fix to this sql error is here.

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

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to resolve SQL related errors.

In this context, we will look into how to fix this SQL error message.

How to fix SQL error 1071 ?

Apply the tips below to resolve this SQL error.

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 urgent assistance with SQL related errors? – We are here to help you. ]

Conclusion

This article covers methods to resolve SQL error 1071 which generally happens if the combined key is too long, and adjusting the varchar value must resolve this error.

MySQL always reserves the max amount for a UTF8 field which is 4 bytes so with 255 + 255 with your DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

you are over the 767 max key length limit.

You can only reduce the single varchar length or don't use a composite key.

To fix SQL error 1071:

You can also add these lines to a new conifg file in /etc/my.conf.d directory named umb4-support.cnf:

[mysqld]
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=true

Then restart the maria db service.