Zabbix-Server Upgrade 4.4 to 5.0 - Ending in MySQL Error Index column size too large.

[BrutalBirdie]

Hello there I got a little problem with the Zabbix-Server Upgrade from version 4.4 to version 5.0.

I followed the official documentation https://www.zabbix.com/documentation.../debian_ubuntu
After reading the upgrade notes for 5.0 https://www.zabbix.com/documentation...rade_notes_500 I noticed with my MariaDB version 10.1.44-MariaDB I need to do some extra step to fix row format compact to dynamic. Check - Done.

So let's start the upgrade following the official doc . . .

Let's view the logs:

1171:20200527:132653.739 Starting Zabbix Server. Zabbix 5.0.0 (revision 9665d62db0).
1171:20200527:132653.739 ****** Enabled features ******
1171:20200527:132653.739 SNMP monitoring: YES
1171:20200527:132653.739 IPMI monitoring: YES
1171:20200527:132653.739 Web monitoring: YES
1171:20200527:132653.739 VMware monitoring: YES
1171:20200527:132653.739 SMTP authentication: YES
1171:20200527:132653.739 ODBC: YES
1171:20200527:132653.739 SSH support: YES
1171:20200527:132653.739 IPv6 support: YES
1171:20200527:132653.739 TLS support: YES
1171:20200527:132653.740 ******************************
1171:20200527:132653.740 using configuration file: /etc/zabbix/zabbix_server.conf
1171:20200527:132653.745 current database version (mandatory/optional): 04050003/04050003
1171:20200527:132653.745 required mandatory version: 05000000
1171:20200527:132653.745 starting automatic database upgrade
1171:20200527:132653.746 [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))]
1171:20200527:132653.746 database upgrade failed

F*CK! This is not even the error mentioned in the doc.

Can someone assist?

[tim.mooney]

Hey Birdie-

If you check around the forums, you'll see that some other people have run into exactly the same issue, e.g. https://www.zabbix.com/forum/zabbix-...bix-3-0-to-5-0

However, the "solution" in the thread there seems suspicious to me.

Did you apply the DYNAMIC row_format to just your hosts table, as the issue linked from the upgrade notes says, or did you convert all your zabbix tables to use row_format DYNAMIC?

Also, can you connect to your mariadb (as the root user) and report the output from a few queries:

MariaDB [(none)]> show variables like 'innodb_large%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_large_prefix | ON |
+---------------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'innodb_file%';
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Barracuda |
| innodb_file_per_table | ON |
+--------------------------+-----------+
4 rows in set (0.00 sec)

I'm asking about those particular innodb settings because I'm suspicious that the error you're seeing is just a different manifestation of the same issue. In particular, there's this note about the "DYNAMIC" row format and innodb_large_prefix in the MariaDB docs:

https://mariadb.com/kb/en/innodb-dynamic-row-format/#index-prefixes-with-the-dynamic-row-format

If you look at the docs for the COMPACT row format, which is what MariaDB defaulted to before, you'll see

https://mariadb.com/kb/en/innodb-compact-row-format/#index-prefixes-with-the-compact-row-format

If the only table you upgraded to row_format=DYNAMIC was hosts, then I'm betting all your other tables are still using the older format, and they're limited to 767 bytes for an index. You could verify the format for you other tables by using something like

SHOW TABLE STATUS FROM zabbix;

and look at the "Row_format" column.

[BrutalBirdie]

tim.mooney Yea I checked all that with Barracuda etc. all the right values but it did not help.

I decided to update the MariaDB Server as well to 10.4.
Then there was a new error

1181:20200527:140724.257 Starting Zabbix Server. Zabbix 5.0.0 (revision 9665d62db0).
1181:20200527:140724.258 ****** Enabled features ******
1181:20200527:140724.258 SNMP monitoring: YES
1181:20200527:140724.258 IPMI monitoring: YES
1181:20200527:140724.258 Web monitoring: YES
1181:20200527:140724.258 VMware monitoring: YES
1181:20200527:140724.258 SMTP authentication: YES
1181:20200527:140724.258 ODBC: YES
1181:20200527:140724.258 SSH support: YES
1181:20200527:140724.258 IPv6 support: YES
1181:20200527:140724.258 TLS support: YES
1181:20200527:140724.258 ******************************
1181:20200527:140724.258 using configuration file: /etc/zabbix/zabbix_server.conf
1181:20200527:140724.264 current database version (mandatory/optional): 04050084/04050084
1181:20200527:140724.264 required mandatory version: 05000000
1181:20200527:140724.264 starting automatic database upgrade
1181:20200527:140724.303 [Z3005] query failed: [1118] Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs [alter table `config` add `saml_sso_url` varchar(2048) default '' not null]

I simply did the same for config as for the host

alter table config row_format = dynamic;

Then it all worked out just fine.

So I would say issue resolved
Thanks for the response.

[sridhar.krishnamurthy]

BrutalBirdie I am running into the exact same error you ran into before upgrading mariadb. But when I upgraded mariadb (following official doc), mariadb service isn't starting. I am upgrading from zabbix 4.4 to 5.0 on Centos 7. Can you please let me know the documentation you followed to upgrade mariadb?

[sridhar.krishnamurthy]

Please ignore my question. My upgrade from MariaDB 5.5 to 10.4 following the official doc was failing to start MariaDB. But instead I upgraded it to 10.1 following below documentation and it came up fine this time:
https://www.tecmint.com/upgrade-mari...debian-ubuntu/