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.
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:
After that, we can restart the SQL service.