I have tried twice now to upgrade our Zabbix server with no success, each time I get mariadb errors like:
[Z3005] query failed: [1709] Index column size too large. The maximum column size is 767 bytes [create index items_1 on items (hostid,key_(1021))]
The server is a self-contained Ubuntu 18.04 LXD container running Zabbix 4 and using the mariadb that shipped with Ubuntu 18.04. I still have a working snapshot of this Zabbix server.
I want to upgrade the server to Ubuntu 22.04 and Zabbix 6 LTS so that it is still supported.
Here is a thread of someone with a similar setup who had the same problem:
The first reply refers the user to the Zabbix 5 release notes, which in turn directs the user to https://support.zabbix.com/browse/ZBX-17690 . That page recommends running
alter table hosts row_format = dynamic;
but that didn't fix my problem.
That page and others mention disabling the innodb strict mode. I've tried that too but it also didn't fix my mariadb problems.
I can't remember why I picked mariadb when I installed zabbix. It seems zabbix prefers mysql. Might it be a good idea to switch from mariadb to mysql as part of this upgrade? If it makes future upgrades easier then it'll be worth it.
It seems the underlying issue is that I need to change the row format to dynamic but I've not found a query to do that for all existing tables, only how to show the affected tables.
It looks like the easiest way for me to upgrade would be to backup my zabbix db and then re-import it after upgrading mariadb but I haven't found any detailed instructions on this process. I have noticed some guides use the mysql command to backup mariadb whilst other use the mariabackup program. I tried mariabackup but it produced several .frm and .ibd files instead of a .sql file so I don't know how to restore them all so it looks like using the mysql command would be the way to go?
Thanks
[Z3005] query failed: [1709] Index column size too large. The maximum column size is 767 bytes [create index items_1 on items (hostid,key_(1021))]
The server is a self-contained Ubuntu 18.04 LXD container running Zabbix 4 and using the mariadb that shipped with Ubuntu 18.04. I still have a working snapshot of this Zabbix server.
I want to upgrade the server to Ubuntu 22.04 and Zabbix 6 LTS so that it is still supported.
Here is a thread of someone with a similar setup who had the same problem:
The first reply refers the user to the Zabbix 5 release notes, which in turn directs the user to https://support.zabbix.com/browse/ZBX-17690 . That page recommends running
alter table hosts row_format = dynamic;
but that didn't fix my problem.
That page and others mention disabling the innodb strict mode. I've tried that too but it also didn't fix my mariadb problems.
I can't remember why I picked mariadb when I installed zabbix. It seems zabbix prefers mysql. Might it be a good idea to switch from mariadb to mysql as part of this upgrade? If it makes future upgrades easier then it'll be worth it.
It seems the underlying issue is that I need to change the row format to dynamic but I've not found a query to do that for all existing tables, only how to show the affected tables.
It looks like the easiest way for me to upgrade would be to backup my zabbix db and then re-import it after upgrading mariadb but I haven't found any detailed instructions on this process. I have noticed some guides use the mysql command to backup mariadb whilst other use the mariabackup program. I tried mariabackup but it produced several .frm and .ibd files instead of a .sql file so I don't know how to restore them all so it looks like using the mysql command would be the way to go?
Thanks
Comment