Zabbix upgrade from 5.4 to 6.0 - automatic database upgrade problem

[pablix]

Hi Guys,
I need some help with database upgrade... I have tried to search for a solution but with no luck so far..

I have upgraded zabbix server to version 6.0 from 5.4 and after I restart my server did not start with errors:

root@zabbix-server:/# tail -f /var/log/zabbix/zabbix_server.log
57618:20220614:113913.217 ******************************
57618:20220614:113913.217 using configuration file: /etc/zabbix/zabbix_server.conf
57618:20220614:113913.230 character set name or collation name that is not supported by Zabbix found in 44 column(s) of database "zabbix"
57618:20220614:113913.230 only character set(s) "utf8,utf8mb3,utf8mb4" and corresponding collation(s) "utf8_bin,utf8mb3_bin,utf8mb4_bin" should be used in database
57618:20220614:113913.232 current database version (mandatory/optional): 05050090/05050090
57618:20220614:113913.232 required mandatory version: 06000000
57618:20220614:113913.232 optional patches were found
57618:20220614:113913.232 starting automatic database upgrade
57618:20220614:113913.233 [Z3005] query failed: [1118] Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs [alter table `config` add `geomaps_attribution` varchar(1024) default '' not null]
57618:20220614:113913.233 database upgrade failed

I have used script to set all tables to dynamic mode and disabled strict mode

#!/bin/bash
# source https://lxadm.com/MySQL:_changing_ROW_FORMAT_to_DYNAMIC_or_COMPRESSE D

DATABASE=zabbix

ROW_FORMAT=DYNAMIC

TABLES=$(echo SHOW TABLES | mysql -s $DATABASE)

for TABLE in $TABLES ; do
echo "ALTER TABLE $TABLE ROW_FORMAT=$ROW_FORMAT;"
echo "ALTER TABLE $TABLE ROW_FORMAT=$ROW_FORMAT" | mysql $DATABASE
done

but still after that same error "Row size too large"....
Any chance for a help ?

[pablix]

Hi Ibm,
Actually I did ran UTF8MB3 to UTF8MB4 stored_procedure before the zabbix-server upgrade.

I have revert this using modified script utf8mb3_convert.sql (I have changed utf8mb4 to 3 inside this official script) and restared server - working as expected
Thank you for that hint !

I have attached modified script to getting back from utf8mb4 to utf8mb3.

Steps I have done:


1. Modify script from utf8mb4 to utf8mb3,
2. run code from server shell to upload model:

mysql zabbix < utf8mb3_convert.sql

3. Login to mysql:

mysql -u root -p

4. mysql>

SET @ZABBIX_DATABASE = 'zabbix';

5. mysql>

CALL zbx_convert_utf8mb3();

6. mysql>

exit;

7. Restart server:

service zabbix-server resatrt

Thank you all for help !

[vladimir_lv]

Hi!
Can you check if the table schema is really DYNAMIC? Just to be sure.
SELECT row_format FROM information_schema.tables WHERE table_schema="zabbix" AND table_name="config" LIMIT 1;

[pablix]

Hi vladimir_lv!

It seems to be dynamic:

mysql> SELECT row_format FROM information_schema.tables WHERE table_schema="zabbix" AND table_name="config" LIMIT 1;
+------------+
| ROW_FORMAT |
+------------+
| Dynamic |
+------------+
1 row in set (0.00 sec)

[vladimir_lv]

What is the version of your MySQL?

[pablix]

Current version is 8.0.29.

[vladimir_lv]

Could you please provide output of "show create table config" ?

[pablix]

| config | CREATE TABLE `config` (
`configid` bigint unsigned NOT NULL,
`work_period` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '1-5,09:00-18:00',
`alert_usrgrpid` bigint unsigned DEFAULT NULL,
`default_theme` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'blue-theme',
`authentication_type` int NOT NULL DEFAULT '0',
`ldap_host` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`ldap_port` int NOT NULL DEFAULT '389',
`ldap_base_dn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`ldap_bind_dn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`ldap_bind_password` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`ldap_search_attribute` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`discovery_groupid` bigint unsigned NOT NULL,
`max_in_table` int NOT NULL DEFAULT '50',
`search_limit` int NOT NULL DEFAULT '1000',
`severity_color_0` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '97AAB3',
`severity_color_1` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '7499FF',
`severity_color_2` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'FFC859',
`severity_color_3` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'FFA059',
`severity_color_4` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'E97659',
`severity_color_5` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'E45959',
`severity_name_0` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'Not classified',
`severity_name_1` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'Information',
`severity_name_2` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'Warning',
`severity_name_3` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'Average',
`severity_name_4` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'High',
`severity_name_5` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'Disaster',
`ok_period` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '5m',
`blink_period` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '2m',
`problem_unack_color` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'CC0000',
`problem_ack_color` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'CC0000',
`ok_unack_color` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '009900',
`ok_ack_color` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '009900',
`problem_unack_style` int NOT NULL DEFAULT '1',
`problem_ack_style` int NOT NULL DEFAULT '1',
`ok_unack_style` int NOT NULL DEFAULT '1',
`ok_ack_style` int NOT NULL DEFAULT '1',
`snmptrap_logging` int NOT NULL DEFAULT '1',
`server_check_interval` int NOT NULL DEFAULT '10',
`hk_events_mode` int NOT NULL DEFAULT '1',
`hk_events_trigger` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '365d',
`hk_events_internal` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '1d',
`hk_events_discovery` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '1d',
`hk_events_autoreg` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '1d',
`hk_services_mode` int NOT NULL DEFAULT '1',
`hk_services` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '365d',
`hk_audit_mode` int NOT NULL DEFAULT '1',
`hk_audit` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '365d',
`hk_sessions_mode` int NOT NULL DEFAULT '1',
`hk_sessions` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '365d',
`hk_history_mode` int NOT NULL DEFAULT '1',
`hk_history_global` int NOT NULL DEFAULT '0',
`hk_history` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '90d',
`hk_trends_mode` int NOT NULL DEFAULT '1',
`hk_trends_global` int NOT NULL DEFAULT '0',
`hk_trends` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '365d',
`default_inventory_mode` int NOT NULL DEFAULT '-1',
`custom_color` int NOT NULL DEFAULT '0',
`http_auth_enabled` int NOT NULL DEFAULT '0',
`http_login_form` int NOT NULL DEFAULT '0',
`http_strip_domains` varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`http_case_sensitive` int NOT NULL DEFAULT '1',
`ldap_configured` int NOT NULL DEFAULT '0',
`ldap_case_sensitive` int NOT NULL DEFAULT '1',
`db_extension` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`autoreg_tls_accept` int NOT NULL DEFAULT '1',
`compression_status` int NOT NULL DEFAULT '0',
`compress_older` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '7d',
`instanceid` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`saml_auth_enabled` int NOT NULL DEFAULT '0',
`saml_idp_entityid` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`saml_sso_url` varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`saml_slo_url` varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`saml_username_attribute` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`saml_sp_entityid` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`saml_nameid_format` varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`saml_sign_messages` int NOT NULL DEFAULT '0',
`saml_sign_assertions` int NOT NULL DEFAULT '0',
`saml_sign_authn_requests` int NOT NULL DEFAULT '0',
`saml_sign_logout_requests` int NOT NULL DEFAULT '0',
`saml_sign_logout_responses` int NOT NULL DEFAULT '0',
`saml_encrypt_nameid` int NOT NULL DEFAULT '0',
`saml_encrypt_assertions` int NOT NULL DEFAULT '0',
`saml_case_sensitive` int NOT NULL DEFAULT '0',
`default_lang` varchar(5) COLLATE utf8mb4_bin NOT NULL DEFAULT 'en_US',
`default_timezone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'system',
`login_attempts` int NOT NULL DEFAULT '5',
`login_block` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '30s',
`show_technical_errors` int NOT NULL DEFAULT '0',
`validate_uri_schemes` int NOT NULL DEFAULT '1',
`uri_valid_schemes` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'http,https,ftp,file,mailto,tel,ssh',
`x_frame_options` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'SAMEORIGIN',
`iframe_sandboxing_enabled` int NOT NULL DEFAULT '1',
`iframe_sandboxing_exceptions` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`max_overview_table_size` int NOT NULL DEFAULT '50',
`history_period` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '24h',
`period_default` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '1h',
`max_period` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '2y',
`socket_timeout` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '3s',
`connect_timeout` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '3s',
`media_type_test_timeout` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '65s',
`script_timeout` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '60s',
`item_test_timeout` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '60s',
`session_key` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`report_test_timeout` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '60s',
`dbversion_status` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`hk_events_service` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '1d',
`passwd_min_length` int NOT NULL DEFAULT '8',
`passwd_check_rules` int NOT NULL DEFAULT '8',
`auditlog_enabled` int NOT NULL DEFAULT '1',
`ha_failover_delay` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '1m',
`geomaps_tile_provider` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`geomaps_tile_url` varchar(1024) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`geomaps_max_zoom` int NOT NULL DEFAULT '0',
PRIMARY KEY (`configid`),
KEY `config_1` (`alert_usrgrpid`),
KEY `config_2` (`discovery_groupid`),
CONSTRAINT `c_config_1` FOREIGN KEY (`alert_usrgrpid`) REFERENCES `usrgrp` (`usrgrpid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `c_config_2` FOREIGN KEY (`discovery_groupid`) REFERENCES `hstgrp` (`groupid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC |

[vladimir_lv]

One more query:
SELECT * FROM information_schema.INNODB_TABLESPACES where name='zabbix/config';

[pablix]

Hi!
Sorry for late response.

[lbm]

I have the exact same issue, also from zabbix 5.4 > to 6.0. And also on MySQL 8.0.29.

[lbm]

Did you ran the UTF8MB3 to UTF8MB4 stored_procedure before the zabbix-server upgrade/or before zabbix-restart after the upgrade ? If so, I think its kinda the same issue I had here. https://www.zabbix.com/forum/zabbix-...tes#post446386 , because I do not get this issue, when I run the UTF8MB4 stored procedure, AFTER zabbix has done all the altering tables.

[pablix]

Hi Ibm,
Actually I did ran UTF8MB3 to UTF8MB4 stored_procedure before the zabbix-server upgrade.

I have revert this using modified script utf8mb3_convert.sql (I have changed utf8mb4 to 3 inside this official script) and restared server - working as expected
Thank you for that hint !

I have attached modified script to getting back from utf8mb4 to utf8mb3.

Steps I have done:


1. Modify script from utf8mb4 to utf8mb3,
2. run code from server shell to upload model:

mysql zabbix < utf8mb3_convert.sql

3. Login to mysql:

mysql -u root -p

4. mysql>

SET @ZABBIX_DATABASE = 'zabbix';

5. mysql>

CALL zbx_convert_utf8mb3();

6. mysql>

exit;

7. Restart server:

service zabbix-server resatrt

Thank you all for help !

[hvdkooij]

Oddly enough I get similar error on a fresh install.
- Ubuntu 22.04
- zabbix, apache, .... all installed using apt

The conversion tool does not convert anything. I did use UTF8MB4 to create all things SQL. So I guess I have to redo this with UTF8 instead of UTF8MB4.

[ced117]

Hello,

Thank you for the utf8mb3_convert.sql file, that one helped me alot.