Zabbix MySQL Configuration Issue - create.sql.gz failing

[MattCrum]

I'm following the instructions here to install Zabbix fresh on Centos7 with MySQL and it's failing at the d. Create initial database step.

https://www.zabbix.com/download?zabbix=5.0&os_distribution=centos&os_version=7&db=mysql&ws=apache

I can create the table fine, but running this command results in an error:

zcat /usr/share/doc/zabbix-server-mysql*/create.sql.gz | mysql -uzabbix -p zabbix
Enter password:
ERROR 1071 (42000) at line 348: Specified key was too long; max key length is 767 bytes

I've researched this and it appears to be an issue with UTF8 limits - a forum post suggested doing this:

SET @@global.innodb_large_prefix = 1;

This got a bit further, but now we're presented with the following error instead:

ERROR 1709 (HY000) at line 348: Index column size too large. The maximum column size is 767 bytes.

Rather than mess around with it any more, I thought it would be best to raise the issue here - any thoughts on how to get this fixed?

MYSQL: mysql  Ver 14.14 Distrib 5.6.48, for Linux (x86_64) using  EditLine wrapper
CENTOS: Linux version 3.10.0-1127.8.2.el7.x86_64 ([email protected]) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-39) (GCC) ) #1 SMP Tue May 12 16:57:42 UTC 2020

[tim.mooney]

You may want to report this on the Zabbix bug tracker (support.zabbix.com) and be clear that this is for a fresh install.

Others on the forums have run into the same issue, with various versions of MariaDB and MySQL.

Based on the research I've done for MariaDB, I think that innodb_large_prefix may not be enough, if your installation is defaulting to row_format = COMPACT.

If you connect to your mysql server and do

SHOW TABLE STATUS FROM zabbix;

what does the Row_format say for your columns?

[MattCrum]

Thanks for the response tim.mooney - I ended up getting it working with a more recent version of MariaDB vs a MySQL package; it looks lke there are numerous issues with using MySQL versions prior to 5.7 (before the limits were increased).

I installed the default version of MariaDB using yum and everything worked fine:

yum install mariadb
...
mysql Ver 15.1 Distrib 10.4.13-MariaDB, for Linux (x86_64) using readline 5.1