Cannot create MySQL database on Debian 9
MariaDB 10.1 is now the default mysql server in Debian 9 "Stretch". With the latest version of master (commit fe777af0), I was unable to create the Kea MySQL database: the creation of the dhcp4_server table failed because the key was too long.
Debian:kea(master):2099% cat /etc/debian_version
9.8
Debian:kea(master):2100% git log -1
commit fe777af0876b01d8fa4e907a74763a8773926109
Author: Francis Dupont <fdupont@isc.org>
Date: Thu Feb 21 14:40:19 2019 +0100
[master] Added ChangeLog entry fro DHCPv6 CB MySQL schema updates
Debian:kea(master):2101% sudo mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 37
Server version: 10.1.37-MariaDB-0+deb9u1 Debian 9.6
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> drop database kea;
Query OK, 16 rows affected (0.11 sec)
MariaDB [(none)]> create database kea;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> quit
Bye
Debian:kea(master):2102% mysql --version
mysql Ver 15.1 Distrib 10.1.37-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Debian:kea(master):2103% $HOME/kea-install/sbin/kea-admin lease-init mysql -u kea -p Kea/1234 -n kea
Checking if there is a database initialized already. Please ignore errors.
Initializing database using script /home/stephen/kea-install/share/kea/scripts/mysql/dhcpdb_create.mysql
ERROR 1071 (42000) at line 805: Specified key was too long; max key length is 767 bytes
mysql returned status code 1
Debian:kea(master):2104% mysql -u kea -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 40
Server version: 10.1.37-MariaDB-0+deb9u1 Debian 9.6
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> connect kea;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Connection id: 41
Current database: kea
MariaDB [kea]> select * from dhcp4_server;
ERROR 1146 (42S02): Table 'kea.dhcp4_server' doesn't exist
MariaDB [kea]> 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;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
MariaDB [kea]>
The last "CREATE" is the statement in dhcpdb_create.mysql that caused the error.