paulgorman.org

2017-11-16 15:13:21 -0500 EST

MySQL/MariaDB: “max key length is 767 bytes”

When running a script through the mysql CLI client:

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

Working with MariaDB, I’ve bumped into this a couple of time lately. MariaDB or Debian must have recently changed a default.

MariaDB [(none)]> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

$ mariadb --version
mariadb  Ver 15.1 Distrib 10.1.26-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
$ sudo grep -r utf /etc/mysql
/etc/mysql/mariadb.conf.d/50-client.cnf:default-character-set = utf8mb4
/etc/mysql/mariadb.conf.d/50-mysql-clients.cnf:default-character-set = utf8mb4
/etc/mysql/mariadb.conf.d/50-server.cnf:# utf8 4-byte character set. See also client.cnf
/etc/mysql/mariadb.conf.d/50-server.cnf:character-set-server  = utf8mb4
/etc/mysql/mariadb.conf.d/50-server.cnf:collation-server      = utf8mb4_general_ci

Hmm. Unicode characters (code points) can be multi-byte. Apparently, something like:

CREATE TABLE IF NOT EXISTS mytable ( color VARCHAR(255) NOT NULL );

… doesn’t fly anymore. Instead, we can either do:

CREATE TABLE IF NOT EXISTS mytable ( color VARCHAR(255) CHARACTER SET utf8 NOT NULL );

… or:

CREATE TABLE IF NOT EXISTS mytable ( color VARCHAR(191) NOT NULL );

From Stack Overflow

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.

…when using utf8mb4, I need to set them to (at most) 191 as 191*4 = 764 < 767.

Damn emoticons. 😒

The internet offers some murmurings that this will no longer be an issue come MariaDB 10.2. I’m not entirely clear on the details, but search terms include “innodb_large_prefix”, “ROW_FORMAT=DYNAMIC”, and “innodb_file_format=Barracuda”.

#sql

⬅ Older Post Newer Post ➡