Database Error - zabbix database version does not match current requirements

[echanobe]

Good day,

I really need your help, advise, tips on how I can migrate my mysql dump from my standalone server to another host with docker containers. I already have the mysqp dump file

**** From the Single VM Server ****
Mysql Server version: 8.0.28-0ubuntServer version: 8.0.28-0ubuntu0.20.04.3 (Ubuntu)u0.20.04.3 (Ubuntu)
zabbix_server (Zabbix) 5.2.7
zabbix_agentd (daemon) (Zabbix) 5.2.7

What I want is to migrate it to another host using docker containers with the latest versions of it.



Here, trying to restor the mysql dump from zabbix server with 5.2.7



from the GUI, it shows this ...




Now from the logs, I see these error messages ... Could you please help what I can do to fix the restoration process? I don't have an in dept knowledge with sql database so please guide me on how to do it ... I tried several things but that doesn't work ...

root@worker01:/data# docker container logs -f zabbix-server-mysql
.
.
.
.
.

Starting Zabbix Server. Zabbix 6.2.1 (revision 2c38297).
Press Ctrl+C to exit.

6:20220819:021541.071 Starting Zabbix Server. Zabbix 6.2.1 (revision 2c38297).
6:20220819:021541.071 ****** Enabled features ******
6:20220819:021541.071 SNMP monitoring: YES
6:20220819:021541.071 IPMI monitoring: YES
6:20220819:021541.071 Web monitoring: YES
6:20220819:021541.071 VMware monitoring: YES
6:20220819:021541.071 SMTP authentication: YES
6:20220819:021541.071 ODBC: YES
6:20220819:021541.071 SSH support: YES
6:20220819:021541.072 IPv6 support: YES
6:20220819:021541.072 TLS support: YES
6:20220819:021541.072 ******************************
6:20220819:021541.072 using configuration file: /etc/zabbix/zabbix_server.conf
6:20220819:021541.118 current database version (mandatory/optional): 05030003/05030003
6:20220819:021541.119 required mandatory version: 06020000
6:20220819:021541.119 starting automatic database upgrade
6:20220819:021541.122 [Z3005] query failed: [1060] Duplicate column name 'available' [alter table `interface` add `available` integer default '0' not null]
6:20220819:021541.123 database upgrade failed
** Preparing Zabbix server
** Using MYSQL_USER variable from ENV
** Using MYSQL_PASSWORD variable from ENV
** Using MYSQL_ROOT_PASSWORD variable from ENV
********************
* DB_SERVER_HOST: mysql-server
* DB_SERVER_PORT: 3306
* DB_SERVER_DBNAME: zabbix
********************
** Creating 'zabbix' user in MySQL database
** Database 'zabbix' already exists. Please be careful with database COLLATE!
** Table 'zabbix.dbversion' already exists.
** Preparing Zabbix server configuration file
** Updating '/etc/zabbix/zabbix_server.conf' parameter "ListenIP": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "ListenPort": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "ListenBacklog": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "SourceIP": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "LogType": 'console'...updated
** Updating '/etc/zabbix/zabbix_server.conf' parameter "LogFile": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "LogFileSize": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "PidFile": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "DebugLevel": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "DBHost": 'mysql-server'...updated
** Updating '/etc/zabbix/zabbix_server.conf' parameter "DBName": 'zabbix'...updated
** Updating '/etc/zabbix/zabbix_server.conf' parameter "DBSchema": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "DBPort": '3306'...updated
** Updating '/etc/zabbix/zabbix_server.conf' parameter "VaultDBPath": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "VaultURL": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "DBUser": 'zabbix'...updated
** Updating '/etc/zabbix/zabbix_server.conf' parameter "DBPassword": '****'. Enable DEBUG_MODE to view value ...updated
** Updating '/etc/zabbix/zabbix_server.conf' parameter "AllowUnsupportedDBVersions": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartReportWriters": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "WebServiceURL": 'http://zabbix-web-service:10053/report'...updated
** Updating '/etc/zabbix/zabbix_server.conf' parameter "HistoryStorageURL": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "HistoryStorageTypes": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "HistoryStorageDateIndex": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "DBSocket": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StatsAllowedIP": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartPollers": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartIPMIPollers": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartPollersUnreachable": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartTrappers": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartPingers": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartDiscoverers": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartHistoryPollers": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartHTTPPollers": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartODBCPollers": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartPreprocessors": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartTimers": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartEscalators": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartAlerters": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartTimers": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartEscalators": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartLLDProcessors": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "JavaGateway": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "JavaGatewayPort": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartJavaPollers": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartVMwareCollectors": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "VMwareFrequency": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "VMwarePerfFrequency": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "VMwareCacheSize": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "VMwareTimeout": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "SNMPTrapperFile": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartSNMPTrapper": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "HousekeepingFrequency": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "MaxHousekeeperDelete": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "ServiceManagerSyncFrequency": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "SenderFrequency": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "CacheSize": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "CacheUpdateFrequency": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartDBSyncers": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "HistoryCacheSize": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "HistoryIndexCacheSize": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "TrendCacheSize": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "TrendFunctionCacheSize": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "ValueCacheSize": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "Timeout": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "TrapperTimeout": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "UnreachablePeriod": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "UnavailableDelay": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "UnreachableDelay": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "AlertScriptsPath": '/usr/lib/zabbix/alertscripts'...updated
** Updating '/etc/zabbix/zabbix_server.conf' parameter "ExternalScripts": '/usr/lib/zabbix/externalscripts'...updated
** Updating '/etc/zabbix/zabbix_server.conf' parameter "FpingLocation": '/usr/sbin/fping'...updated
** Updating '/etc/zabbix/zabbix_server.conf' parameter "Fping6Location": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "SSHKeyLocation": '/var/lib/zabbix/ssh_keys'...updated
** Updating '/etc/zabbix/zabbix_server.conf' parameter "LogSlowQueries": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "StartProxyPollers": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "ProxyConfigFrequency": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "ProxyDataFrequency": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "SSLCertLocation": '/var/lib/zabbix/ssl/certs/'...updated
** Updating '/etc/zabbix/zabbix_server.conf' parameter "SSLKeyLocation": '/var/lib/zabbix/ssl/keys/'...updated
** Updating '/etc/zabbix/zabbix_server.conf' parameter "SSLCALocation": '/var/lib/zabbix/ssl/ssl_ca/'...updated
** Updating '/etc/zabbix/zabbix_server.conf' parameter "LoadModulePath": '/var/lib/zabbix/modules/'...updated
** Updating '/etc/zabbix/zabbix_server.conf' parameter "TLSCAFile": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "TLSCRLFile": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "TLSCertFile": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "TLSCipherAll": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "TLSCipherAll13": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "TLSCipherCert": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "TLSCipherCert13": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "TLSCipherPSK": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "TLSCipherPSK13": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "TLSKeyFile": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "TLSPSKIdentity": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "TLSPSKFile": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "ServiceManagerSyncFrequency": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "HANodeName": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "NodeAddress": ''...removed
** Updating '/etc/zabbix/zabbix_server.conf' parameter "User": 'zabbix'...updated
Starting Zabbix Server. Zabbix 6.2.1 (revision 2c38297).
Press Ctrl+C to exit.

7:20220819:021643.201 Starting Zabbix Server. Zabbix 6.2.1 (revision 2c38297).
7:20220819:021643.202 ****** Enabled features ******
7:20220819:021643.202 SNMP monitoring: YES
7:20220819:021643.202 IPMI monitoring: YES
7:20220819:021643.202 Web monitoring: YES
7:20220819:021643.202 VMware monitoring: YES
7:20220819:021643.202 SMTP authentication: YES
7:20220819:021643.202 ODBC: YES
7:20220819:021643.202 SSH support: YES
7:20220819:021643.202 IPv6 support: YES
7:20220819:021643.202 TLS support: YES
7:20220819:021643.202 ******************************
7:20220819:021643.202 using configuration file: /etc/zabbix/zabbix_server.conf
7:20220819:021643.237 current database version (mandatory/optional): 05030003/05030003
7:20220819:021643.237 required mandatory version: 06020000
7:20220819:021643.237 starting automatic database upgrade
7:20220819:021643.238 [Z3005] query failed: [1060] Duplicate column name 'available' [alter table `interface` add `available` integer default '0' not null]
7:20220819:021643.239 database upgrade failed
root@worker01:/data#


Many Thanks

[echanobe]

I resolved this by just restoring the dump to docker container with version 5.2.7 then upgrade it to the latest.

[iulian.dominte]

Hi,

I have a similar issue with upgrading the database from 05050020 to mandatory version: 06000000.
I'm using MariaDB and and trying to upgrade from zabbix 5.4 to 6.0 (I'm not a Database guru)

First I had this issue:

Unable to start Zabbix server due to unsupported MariaDB database version (10.03.34).
45427:20220821:002312.038 Must be at least (10.05.00).

##-- upgraded MariaDB to 10.08.00

using configuration file: /etc/zabbix/zabbix_server.conf
2058:20220821:002937.761 current database version (mandatory/optional): 05050020/05050020
2058:20220821:002937.761 required mandatory version: 06000000
2058:20220821:002937.761 optional patches were found
2058:20220821:002937.761 starting automatic database upgrade
2058:20220821:002937.773 [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 `hk_events_service` varchar(32) default '1d' not null]
2058:20220821:002937.773 database upgrade failed

##-- set those in mariadb server.cnf file

innodb_strict_mode='OFF'
innodb_file_per_table=ON
innodb_buffer_pool_size=512MB
innodb_file_format=Barracuda
innodb_large_prefix=1

Upgrading process started and stopped again.

33133:20220821:005642.825 completed 59% of database upgrade
33133:20220821:005642.838 completed 60% of database upgrade
33133:20220821:005642.847 [Z3005] query failed: [1709] Index column size too large. The maximum column size is 767 bytes [create index items_1 on items (hostid,key_(764))]

database upgrade failed

What should I be doing next to resolve the issue ? Did anybody here had this issue?

Regards,
Iulian

[tim.mooney]

I do not recommend using innodb_strict_mode=OFF.

Have you read the Zabbix upgrade "Known Issues" documentation, especially the section related to a database that was originally created with MariaDB 10.2.x or earlier? https://www.zabbix.com/documentation...2.1-and-before

You can also search in these forums for posts related to ROW_FORMAT. I've posted about it multiple times

[pimpogu]

Hi,

I have a similar issue with upgrading the database from 05050020 to mandatory version: 06000000.
I'm using MariaDB and and trying to upgrade from zabbix 5.4 to 6.0 (I'm not a Database guru)

First I had this issue:

Unable to start Zabbix server due to unsupported MariaDB database version (10.03.34).
45427:20220821:002312.038 Must be at least (10.05.00).

##-- upgraded MariaDB to 10.08.00

using configuration file: /etc/zabbix/zabbix_server.conf
2058:20220821:002937.761 current database version (mandatory/optional): 05050020/05050020
2058:20220821:002937.761 required mandatory version: 06000000
2058:20220821:002937.761 optional patches were found
2058:20220821:002937.761 starting automatic database upgrade
2058:20220821:002937.773 [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 `hk_events_service` varchar(32) default '1d' not null]
2058:20220821:002937.773 database upgrade failed

##-- set those in mariadb server.cnf file

innodb_strict_mode='OFF'
innodb_file_per_table=ON
innodb_buffer_pool_size=512MB
innodb_file_format=Barracuda
innodb_large_prefix=1

Upgrading process started and stopped again.

33133:20220821:005642.825 completed 59% of database upgrade
33133:20220821:005642.838 completed 60% of database upgrade
33133:20220821:005642.847 [Z3005] query failed: [1709] Index column size too large. The maximum column size is 767 bytes [create index items_1 on items (hostid,key_(764))]

database upgrade failed

What should I be doing next to resolve the issue ? Did anybody here had this issue?

Regards,
Iulian

Hello, same problem here... [Z3005] query failed: [1709] Index column size too large. The maximum column size is 767 bytes [create index items_1 on items (hostid,key_(764))]

news?