[debian-mysql] Bug#848603: Enable innodb_large_prefix

[Ian Gilfillan]

On 18/12/2016 23:43, Christian Hofstaedtler wrote:
> mariadb-server, the new default-mysql-server, breaks existing
> well-functioning applications that need to index columns defined as
> VARCHAR(255). This worked fine with mysql-server-5.6.
>
> This is caused by two questionable choices:
>
>    * default character set is utf8mb4 (causing index prefix lengths
>      quadruple).
>    * innodb_large_prefix is OFF.
>
> I'd suggest you enable innodb_large_prefix or revert to what
> mysql-server-5.6 did, i.e. set the character set to utf8 (which uses
> "just" 3 bytes per character).
>
> Thanks,

The default character set has been utf8mb4 in Debian MariaDB for a while 
now, so changing it back is probably not the best idea. MySQL and 
probably MariaDB will in future use this default, so going back just 
delays things. But with the current default, besides your issue, there 
could also be issues when moving from Debian to another distro (or 
installing from MariaDB's repos), even on the same version, so whatever 
happens won't be ideal for everyone.

Setting innodb_large_prefix to ON is not possible without also changing 
the InnoDB file format and setting innodb_file_format to barracuda. See 
https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_large_prefix

Can you clarify what you mean by "breaks"? The application critically 
needs to make use of indexes longer than 191 characters? Or is it 
running slowly? See https://jira.mariadb.org/browse/MDEV-8872 for a 
performance issue.

Realistically, the best fix may be for you to specify the character set 
in the configs in advance.