Importing native schema of zabbix-proxy-mysql in Ubuntu 20.04 causes "ERROR 1118 (42000) at line 1278: Row size too large (> 8126)"

[Valerio Bozzolan]

Steps to reproduce from a fresh Ubuntu focal 20.04 LTS server:

apt install mariadb-server zabbix-proxy-mysql

Then run:

mysql <<< "CREATE DATABASE zabbix_proxy character SET utf8 collate utf8_bin;"
zcat /usr/share/zabbix-proxy-mysql/schema.sql.gz | mysql "zabbix_proxy"

→

ERROR 1118 (42000) at line 1278: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

Well-known working workaround:

mysql <<< "SET global innodb_strict_mode='OFF'"

Weird proposed fix:

mysql <<< "CREATE DATABASE zabbix_proxy character SET utf8 collate utf8_bin;"
mysql <<< "SET global innodb_strict_mode='OFF'"
zcat /usr/share/zabbix-proxy-mysql/schema.sql.gz | mysql "zabbix_proxy"
mysql zabbix_proxy <<< "ALTER TABLE host_inventory MODIFY name varchar(128) default '' not null, modify alias varchar(128) default '' not null, modify os varchar(128) default '' not null, modify os_short varchar(128) default '' not null;";
mysql <<< "SET global innodb_strict_mode='ON'"

This weird fix is based by the fact that the Zabbix table "host_inventory" has too many columns and the VARCHARs are too small. So we import them without strict mode, we fix them (making them bigger) and then we disable strict mode again.

https://support.zabbix.com/browse/ZBX-16757

https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/#increasing-the-length-of-varchar-columns