[debian-mysql] Bug#886756: Regression: Specified key was too long; max key length is 767 bytes

[Jonas Meurer]

Package: mariadb-server-10.1
Version: 1:10.1.29-6
Severity: serious
Tags: upstream

Control: -1 forwarded https://jira.mariadb.org/browse/MDEV-14904

Hello,

I just discovered that MariaDB 10.1 packages in Sid, Buster and Stretch
break applications that expect a maximum allowed size of 255 characters
for VARCHAR fields. E.g.:

> django.db.utils.OperationalError: (1071, 'Specified key was too long;
> max key length is 767 bytes')

This is due to the fact that MariaDB in Debian has `utf8mb4` set as
default character since version 10.0.20-2 which raises the required
amount of memory per character.

To still allow fields with > 191 < 255 characters, `innodb_large_prefix`
has to be enabled, which in turn requires the following settings:

> innodb_file_format      = Barracuda
> innodb_file_per_table   = On
> innodb_large_prefix     = On

Unfortunately even that is not enough. Additionally, the row format for
tables needs to be changed to `dynamic`.

Starting with MariaDB 10.2, `innodb_default_row_format` was introduced,
which allows to set the default row format for the whole MariaDB server.
Unfortunately, this option is not available in MariaDB 10.1 yet. In
other words, there's no way to configure MariaDB 10.1 in Debian in a way
that it works with applications that expect things like VARCHAR(255) to
be possible.

This is a severe regression. I discussed this topic with MariaDB
upstream developer Marko Mäkelä (dr-m) on IRC and he agreed that they
can backport `innodb_default_row_format` for the next upstream release
of MariaDB 10.1. I created an upstream bugreport to track this:

https://jira.mariadb.org/browse/MDEV-14904

I suggest that this fix should be backported to MariaDB 10.1 in Debian
Stable as well as it is a severe regression compared to MySQL and
earlier MariaDB versions (i.e. pre 10.0.20-2).

PS: This problem doesn't exist with MariaDB >= 10.2 or MySQL 5.7 as
both switched to `Barracudea` as default `innodb_file_format` along
with `innodb_large_prefix` and `dynamic` as default row format.

See the following bugreports and discussion threads for further details:

* https://jira.mariadb.org/browse/MDEV-9646
* https://code.djangoproject.com/ticket/18392
* https://stackoverflow.com/questions/30761867/mysql-error-the-maximum-column-size-is-767-bytes

Cheers
 jonas

-- System Information:
Debian Release: 9.3
  APT prefers stable-updates
  APT policy: (500, 'stable-updates'), (500, 'stable')
Architecture: amd64 (x86_64)
Foreign Architectures: i386

Kernel: Linux 4.9.0-5-amd64 (SMP w/4 CPU cores)
Locale: LANG=de_DE.UTF-8, LC_CTYPE=de_DE.UTF-8 (charmap=UTF-8) (ignored: LC_ALL set to en_US.UTF-8), LANGUAGE=de_DE.UTF-8 (charmap=UTF-8) (ignored: LC_ALL set to en_US.UTF-8)
Shell: /bin/sh linked to /bin/dash
Init: systemd (via /run/systemd/system)

Versions of packages mariadb-server-10.1 depends on:
ii  adduser                   3.115
ii  debconf [debconf-2.0]     1.5.61
pn  galera-3                  <none>
ii  gawk                      1:4.1.4+dfsg-1
ii  init-system-helpers       1.48
ii  iproute2                  4.9.0-1+deb9u1
ii  libaio1                   0.3.110-3
ii  libc6                     2.24-11+deb9u1
ii  libdbi-perl               1.636-1+b1
ii  libpam0g                  1.1.8-3.6
ii  libstdc++6                6.3.0-18
ii  libsystemd0               232-25+deb9u1
ii  lsb-base                  9.20161125
ii  lsof                      4.89+dfsg-0.1
ii  mariadb-client-10.1       10.1.26-0+deb9u1
ii  mariadb-common            10.1.26-0+deb9u1
pn  mariadb-server-core-10.1  <none>
ii  passwd                    1:4.4-4.1
ii  perl                      5.24.1-3+deb9u2
ii  psmisc                    22.21-2.1+b2
ii  rsync                     3.1.2-1+deb9u1
ii  socat                     1.7.3.1-2+deb9u1
ii  zlib1g                    1:1.2.8.dfsg-5

Versions of packages mariadb-server-10.1 recommends:
pn  libhtml-template-perl  <none>

Versions of packages mariadb-server-10.1 suggests:
ii  bsd-mailx [mailx]  8.1.2-0.20160123cvs-4
pn  mariadb-test       <none>
ii  netcat-openbsd     1.130-3
pn  tinyca             <none>