Error 1071 sql

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.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

The reference to --binary-mode (introduced in MySQL 5.6.3) is probably a distraction.

It doesn’t sound like you’re dealing with a mysqldump output file, there. Try the file utility.

shell> file dumpfile.sql
dumpfile.sql: ASCII text

If you don’t get the ASCII text response, you’re dealing with either something that isn’t a dump file from mysqldump at all, or you’re dealing with something that’s been compressed (with gzip or bzip2, for example), which you’d need to uncompress before piping it into mysql.

If you see SQLite 3.x database then you definitely have your answer… it’s a raw SQLite database, not a MySQL dump file.

Indeed, the first few bytes of a SQLite database are these:

53 51 4C 69 74 65 20 66  SQLite f
6F 72 6D 61 74 20 33 00  ormat 3^@

Note that the 16th octet here is 0x00, explaining the ERROR: ASCII '' appeared in the statement... message in this case. The suggestion that --binary-mode is appropriate is a false alarm.

Windows users: the ‘file’ utility is a tool from Unix, but the Windows version can be found here.

You can increase the maximum InnoDB index prefix size in MySQL 5.6 to 3072 bytes by setting innodb_large_prefix to ON along with other settings that you’ll also need in order to enable that one, discussed here:

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix

These changes should allow these indexes to be valid for InnoDB tables.

With a character set of (I assume) utf8, a VARCHAR(1024) would need 1024 x 3 = 3072 bytes for its index.

Updates:

I incorrectly showed the value of the variable as Yes when it should have been ON.

But also:

I think it needs 3072+2 – ypercube

When I thought about this, it sounded correct, because 1 byte is needed to store the size of the value when the value is 255 bytes or less, and 2 bytes are needed otherwise.

However, testing reveals that this isn’t the case, in this case — InnoDB using the COMPRESSED row format from Barracuda can actually index the full size of a VARCHAR(1024)… so either they’ve documented it strangely or the COMPRESSED row format stores the length out-of-band along with another block of metadata, so it doesn’t count in the total bytes in this format.

COMPRESSED doesn’t truncate the index into a prefix index and throw a warning until you go to VARCHAR(1025).

+-------+------+----------------------------------------------------------+
| Level | Code | Message                                                  |
+-------+------+----------------------------------------------------------+
| Error | 1071 | Specified key was too long; max key length is 3072 bytes |
+-------+------+----------------------------------------------------------+

It’s nice the way it throws a warning instead of throwing an error, but that doesn’t help us here because this still requires the explicit ROW_FORMAT declaration to trigger this behavior.

So, my initial answer is still wrong, because you have to explicitly add ROW_FORMAT=COMPRESSED to the end of the table definition. Otherwise you still get the same old error.

Actually, you get two different errors in 5.6.10. If you try to create a table with a fully-indexed VARCHAR(1024) you get this:

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

…but if you try with a fully-indexed VARCHAR(1025) you get this:

ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

That’s sloppy code but the bottom line is that my answer doesn’t actually fix this problem.

I don’t see a way to use ROW_FORMAT=COMPRESSED by default, nor does it seem like a good idea if it were possible, and I’m inclined to suggest that the answer is…

…there’s not a readily available workaround here. My other thought was «character sets» but the difference between latin1 and utf8 still isn’t sufficient to explain 1,024 vs 1000 or 767. I’ll happily get behind a better idea but at the moment, I can’t think of a version of MySQL Server that this code would work properly on.

Мы столкнулись с этой проблемой при попытке добавить индекс UNIQUE в поле VARCHAR (255), используя utf8mb4. Хотя проблема здесь уже хорошо описана, я хотел бы добавить некоторые практические советы о том, как мы это поняли и решили.

При использовании utf8mb4 символы считаются 4 байтами, тогда как под utf8 они могут составлять 3 байта. Базы данных InnoDB имеют ограничение на то, что индексы могут содержать только 767 байт. Поэтому при использовании utf8 вы можете сохранить 255 символов (767/3 = 255), но используя utf8mb4, вы можете хранить только 191 символ (767/4 = 191).

Вы абсолютно можете добавлять регулярные индексы для полей VARCHAR(255), используя utf8mb4, но случается, что размер индекса усекается с 191 символом автоматически — например unique_key здесь:

Это прекрасно, потому что регулярные индексы просто используются для быстрого поиска MySQL через ваши данные. Не нужно индексировать все поле.

Итак, почему MySQL автоматически обрезает индекс для обычных индексов, но бросает явную ошибку при попытке сделать это для уникальных индексов? Ну, для того, чтобы MySQL смог выяснить, существует ли уже существующее или обновляемое значение, ему нужно фактически индексировать все значение, а не только его часть.

В конце дня, если вы хотите иметь уникальный индекс в поле, все содержимое поля должно вписываться в индекс. Для utf8mb4 это означает сокращение длины полей VARCHAR до 191 символа или меньше. Если вам не нужна utf8mb4 для этой таблицы или поля, вы можете вернуть ее обратно в utf8 и сохранить свои длины длины.