Differences

This shows you the differences between two versions of the page.

--- mysql:error_1071 [2016/07/01 22:33] – peter
+++ mysql:error_1071 [2020/07/15 09:30] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
 ====== MySQL - Error #1071 - Specified key was too long; max key length is 767 bytes ======
-This error is received because 1 byte equals 1 character only if you use the **latin-1** character set.  If you use **utf8**, each character will be considered 3 bytes when defining your key column.  If you use **utf8mb4**, each character will be considered to be 4 bytes when defining your key column.  Thus, you need to multiply your key field's character limit by, 1, 3, or 4 (depending on which character set is being used) to determine the number of bytes the key field is trying to allow.  If you are using **uft8mb4**, you can only define a maximum of 191 characters for a native, InnoDB, primary key field. Just don't breach 767 bytes.
+The max key length depends on the character set you use.  If you use **latin1** then the largest column you can index is **varchar(767)** with 1 byte equalling 1 character.  If you use **utf8**, each character will be considered 3 bytes when defining your key column.  If you use **utf8mb4**, each character will be considered to be 4 bytes when defining your key column.
-MySQL assumes worst case for the number of bytes per character in the string.  For the MySQL 'utf8' encoding, that's 3 bytes per character since that encoding doesn't allow characters beyond U+FFFF.  For the MySQL 'utf8mb4' encoding, it's 4 bytes per character, since that's what MySQL calls actual UTF-8.
+  * MySQL '**utf8**' encoding doesn't allow characters beyond U+FFFF.
+  * MySQL '**utf8mb4**' encoding allows characters beyond U+FFFF through U+FFFFFF.
-Looking at an example:
+Thus, you need to multiply your key field's character limit by, 1, 3, or 4 (depending on which character set is being used) to determine the number of bytes the key field will actually try to use.
-Assuming that **utf8mb4** is being used, this means that 4 bytes are needed for every character.  Attempting to have a key column be 255 characters long will fail with this error because those 255 characters actually takes up 4 times that amount of bytes, i.e. 255 * 4 = 1020.  However as mentioned, the maximum length supported by InnoDB is 767, therefore you would need to change this from:
+  * For **uft8** a maximum of 255 characters can therefore be used for an InnoDB key field because 767/3≈255.
+  * For **uft8mb4** a maximum of 191 characters can therefore be used for an InnoDB key field because 767/4≈191.
+There is also a separate 3072 byte limit per index.  The 767 byte limit is per column, so you can include multiple columns (each 767 bytes or smaller) up to 3072 total bytes per index, but no column longer than 767 bytes.  (MyISAM is a little different.  It has a 1000 byte index length limit, but no separate column length limit within that).
+One workaround for these limits is to only index part of a field, for example, here only 191 characters from column1 is part of the index:
 <code mysql>
-ALTER TABLE `mytable` ADD UNIQUE ( column1(255));
+ALTER TABLE `mytable` ADD UNIQUE ( column1(191));
 </code>
-to
+But what if you want to index more than 767 bytes of a column in InnoDB?
+In that case you should consider using **innodb_large_prefix**, which was introduced in MySQL 5.5.14 and allows you to include columns up to 3072 bytes long in InnoDB indexes.  It does not affect the index limit, which is still 3072 bytes as quoted in the manual:
+<code>
+The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes.
+This limit applies to the length of the combined index key in a multi-column index.
+</code>
+===== Using innodb_large_prefix =====
+Here are a few pre-requisites for using **innodb_large_prefix**:
+  * At the database level you have to use **innodb_file_format=BARRACUDA**
+  * At the table level you have to use **ROW_FORMAT=DYNAMIC** or **ROW_FORMAT=COMPRESSED**
+The default file format is still Antelope for backwards compatibility, and the default row format is COMPACT.
+You can set both **innodb_file_format** and **innodb_large_prefix** dynamically, but you should also set them in **my.cnf** so they survive a restart.
+Here’s an example.  If I try to create this table with **innodb_large_prefix** disabled I get an error:
 <code mysql>
-ALTER TABLE `mytable` ADD UNIQUE ( column1(191));
+mysql> create table if not exists utf8_test (
+    ->   day date not null,
+    ->   product_id int not null,
+    ->   dimension1 varchar(500) character set utf8 collate utf8_bin not null,
+    ->   dimension2 varchar(500) character set utf8 collate utf8_bin not null,
+    ->   unique index unique_index (day, product_id, dimension1, dimension2)
+    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
+</code>
+If I enable **innodb_large_prefix** I can create the table successfully:
+<code mysql>
+mysql> set global innodb_file_format = BARRACUDA;
+Query OK, 0 rows affected (0.00 sec)
+mysql> set global innodb_large_prefix = ON;
+Query OK, 0 rows affected (0.00 sec)
+mysql> create table if not exists utf8_test (
+    ->   day date not null,
+    ->   product_id int not null,
+    ->   dimension1 varchar(500) character set utf8 collate utf8_bin not null,
+    ->   dimension2 varchar(500) character set utf8 collate utf8_bin not null,
+    ->   unique index unique_index (day, product_id, dimension1, dimension2)
+    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
+Query OK, 0 rows affected (0.02 sec)
+</code>
+The examples are similar for **latin1**, but I can use columns three times as long since it’s a single-byte character set.
+<code mysql>
+mysql> create table if not exists latin1_test (
+    ->   day date not null,
+    ->   product_id int not null,
+    ->   dimension1 varchar(1500) not null,
+    ->   dimension2 varchar(1500) not null,
+    ->   unique index unique_index (day, product_id, dimension1, dimension2)
+    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
+mysql> set global innodb_file_format = BARRACUDA;
+Query OK, 0 rows affected (0.00 sec)
+mysql> set global innodb_large_prefix = ON;
+Query OK, 0 rows affected (0.00 sec)
+mysql> create table if not exists latin1_test (
+    ->   day date not null,
+    ->   product_id int not null,
+    ->   dimension1 varchar(1500) not null,
+    ->   dimension2 varchar(1500) not null,
+    ->   unique index unique_index (day, product_id, dimension1, dimension2)
+    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
+Query OK, 0 rows affected (0.02 sec)
+</code>
+And here’s what happens if I try to create an index longer than 3072 bytes:
+<code mysql>
+mysql> create table if not exists long_index_test (
+    ->   day date not null,
+    ->   product_id int not null,
+    ->   dimension1 varchar(1500) not null,
+    ->   dimension2 varchar(1500) not null,
+    ->   dimension3 varchar(1500) not null,
+    ->   unique index unique_index (day, product_id, dimension1, dimension2, dimension3)
+    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
+ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
 </code>