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 );
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