Kea and the 767 bytes limit in MySQL

There are reports of problems showing up when MySQL is used with Kea under certain conditons.

After preparing the MySQL database and running:

$ kea-admin db-init mysql

One of the following errors pops up:
ERROR 1071 (42000) at line 805: Specified key was too long; max key length is 767 bytes
or
ERROR 1709 (HY000) at line 805: Index column size too large. The maximum column size is 767 bytes.

These both refer to the same CREATE TABLE statement.

CREATE TABLE IF NOT EXISTS dhcp4_server (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    tag VARCHAR(256) NOT NULL,
    description TEXT,
    modification_ts TIMESTAMP NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY dhcp4_server_tag_UNIQUE (tag),
    KEY key_dhcp4_server_modification_ts (modification_ts)
) ENGINE=InnoDB;

Specifically the tag column in its role as a unique key is the cause. It is VARCHAR(256). If a character set uses three or more bytes per character, it is enough to go past the limit. 256 * 3 == 768 which is greater than 767 == (256 * 3 - 1) which is the limit for indexes and keys. See third bullet point on InnoDB limits: https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html.

The most probable cause is that an unappropriate character set is used. The primary recommendation is to try to move to what was once the default character set and collation mentioned in the v5.6 charset docs (https://dev.mysql.com/doc/refman/5.6/en/charset.html) which clearly specify the default MySQL server character set and collation are latin1 and latin1_swedish_ci. This continues to be the default in all MariaDB versions: https://mariadb.com/kb/en/setting-character-sets-and-collations/: in MariaDB, the default character set is latin1, and the default collation is latin1_swedish_ci (however this may differ in some distros [...]). These are the settings that we at ISC have been testing with for a long time.

You can explicitly set the collation for a database with:

CREATE DATABASE keatest
    CHARACTER SET latin1
    COLLATE latin1_swedish_ci;

If that doesn't work or is not an option for you, here is something that has worked in some cases. Set these InnoDB configuration flags:

$ mysql -u root
USE keatest;

SET GLOBAL innodb_file_format = Barracuda;
SET GLOBAL innodb_file_per_table = ON;
SET GLOBAL innodb_large_prefix = ON;
# And for post-5.7 versions:
SET GLOBAL innodb_default_row_format = dynamic;

Or if setting them in the configuration files (e.g. my.cnf) is more convenient:

[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = ON
innodb_large_prefix = ON
# And for post-5.7 versions:
innodb_default_row_format = dynamic

The contents of this article apply to any MySQL distribution using the InnoDB engine. That includes legacy MySQL and MariaDB.