2

I'm repeatedly getting this error from 10.0.29-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04

ERROR 1071 (42000) at line 81: Specified key was too long; max key length is 767 bytes

The line targeted will typically look like this:

name VARCHAR(255) NOT NULL UNIQUE,

Changing it to a VARCHAR(63) makes the error go away. Is this a bug in MariaDB?

Greg Dougherty
  • 3,281
  • 8
  • 35
  • 58
  • Possible duplicate of [#1071 - Specified key was too long; max key length is 767 bytes](http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes) – jmoerdyk Apr 18 '17 at 23:03
  • @jmoerdyk - That link refers to `VARCHAR(500) utf8`. The question here presumably applies to `VARCHAR(255) utf8mb4`. There is a lot of overlap in the "solutions", but I have provide a solution that is probably more specific. – Rick James Apr 19 '17 at 17:19

2 Answers2

3

To work around this error, do one of

Workaround: do one of

  • Upgrade to 5.7.7 (or later) for 3072 byte limit instead of 767
  • Change 255 to 191 on the VARCHAR (assuming your values are not too long)
  • ALTER .. CONVERT TO utf8 -- but this disallows Emoji and some Chinese
  • Use a "prefix" index (ill-advised)
  • Reconfigure (for 5.6.3 - 5.7.6) (below)

Reconfiguring 5.6.3 or 5.5.14:

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_large_prefix=1;
logout & login (to get the global values);
ALTER TABLE tbl ROW_FORMAT=DYNAMIC;  (or COMPRESSED)

(The version numbers are based on Oracle's MySQL; the MariaDB version numbers are different for this issue.)

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • 2
    I don't think there are magic changes in 5.7 in this regard, apart from modified defaults, which are easy to configure, so there is no point to switch versions just for that. The last advice should fully apply to MariaDB 10.0, and these options can be set in the config file. For MariaDB versions, the correlation is such: 10.0 and 10.1 have InnoDB 5.6, 10.2 has InnoDB 5.7. – elenst Apr 19 '17 at 19:44
  • @elenst -- Thanks, I could not quickly spot the mapping between 5.x and 10.x. And, yes, the 5.7 "defaults" essentially prevent the 767 from ever happening again. And the corresponding code in 10.2.0?? Or perhaps slightly later? – Rick James Apr 19 '17 at 21:06
  • 10.2.0 had InnoDB 5.7 which was available around the time it was released, but I would not recommend anyone to use a zero version of any branch for anything but alpha-testing. 10.2.5-rc is currently the latest available 10.2 release, it has InnoDB 5.7.14. The next 10.2.6-ga is expected to have InnoDB 5.7.18. – elenst Apr 19 '17 at 21:40
  • Correction: 10.2.0 still had 5.6, 10.2.2 had 5.7. – elenst Apr 19 '17 at 21:47
  • Problem is with MariaDB defaulting to 4 byte "characters", rather than the 2 byte characters I'm used to. UTF8 fixes it – Greg Dougherty May 04 '17 at 21:53
  • `utf8` handles up to 3 bytes. It is the 3rd bullet item in my answer. – Rick James May 04 '17 at 22:21
1

To follow up on the accepted answer - to change to UTF8, which I found the easiest solution, you can use the below line in MariaDb when creating your table:

name varchar(255) CHARACTER SET 'utf8' UNIQUE NOT NULL,

Concrete_Buddha
  • 556
  • 4
  • 16