Zabbix Server 5.0.45 to 7.0.9 database upgrade failed on patch 05010000

[Zabbix_To_Go]

Hello, I'm attempting to upgrade Zabbix Server 5.0.45 (LTS) to 7.0.9 (LTS). I'm running MariaDB 10.11.6.

I've upgraded PHP to 8.2.25 because the server was previously running PHP 7.4 (Zabbix 7.0 requires PHP 8.0 or greater).

I've encountered the following Zabbix database upgrade failure upon upgrading to 7.0.9 (LTS) in /etc/zabbix/zabbix_server.log:

40459:20250204:171301.398 starting automatic database upgrade
40459:20250204:171301.402 database upgrade failed on patch 05010000, exiting in 10 seconds
40459:20250204:171311.402 Zabbix Server stopped. Zabbix 7.0.9 (revision 05b8b05eefe).
zz0.dvcsa31s2g7zz

I already have read all of the upgrade notes from 5.0 through 7.0.
I tried Executing log_bin_trust_function_creators = 1; after the upgrade to 7.0, and restarting the zabbix-server, but the results are the same.
I also tried adding AllowUnsupportedDBVersions=1 to /etc/zabbix/zabbix_server.conf, and the upgrade failed with the same error.

What am I missing here?

Thanks!

[Zabbix_To_Go]

I was able to add the "default_lang" column manually. Thanks cyber for posting a link to the actual code of the patch 05010000. I used to develop the solution. Here is a detailed summary of the steps I took to resolve the issue. For the purpose of this post my db user is 'zabbix' and hostname is 'localhost'.

I connected to the database and executed the command to show grants (permissions):

SHOW GRANTS FOR 'zabbix'@'localhost';

I executed the following command to give the user 'zabbix' the required privileges to modify table structures.
GRANT ALL PRIVILEGES ON zabbix.* TO 'zabbix'@'localhost';

At this point I restarted zabbix-server to retry the upgrade but it failed for me. Note that the upgrade might work for you if you are encountering this issue. I had to perform the remaining steps in this post to manually add "default_lang" column.

systemctl restart zabbix-server

## Selected zabbix database

USE zabbix;

## Outputted the structure of the config table to the screen to verify if the "default_lang" column exists. It did not.

DESC config;

## The GRANT statement executed a few steps earlier should include ALTER on the zabbix.* schema, which should be sufficient, but some MySQL/MariaDB configurations require explicit grants for ALTER at the global level. I executed this command below to grant ALTER at the global level to ensure I could run the following steps successfully:

GRANT ALTER ON *.* TO 'zabbix'@'localhost';

## I executed this command to ensure the changes thus far take effect without having to restart MariaDB:

FLUSH PRIVILEGES;

My zabbix server's database "config" table did not contain the "default_lang" column in the config table. My atempts to add the column failed with “Row size too large” (ERROR 1118) because the config table reached the maximum row size for InnoDB (8126 bytes per row) in MariaDB. Adding another column directly was impossible unless the size of the existing columns was reduced, so performed the next step.

Since VARCHAR columns take up fixed storage space in a row, you can convert some of VARCHAR columns to TEXT, which moves their storage out of the row, freeing up storage space. From the table schema, the columns below were eligible to convert to text:

• http_strip_domains (VARCHAR(2048))
• saml_sso_url (VARCHAR(2048))
• saml_slo_url (VARCHAR(2048))
• saml_idp_entityid (VARCHAR(1024))
• saml_sp_entityid (VARCHAR(1024))

## I converted the columns in the "config" table listed in the bullets above from VARCHAR to TEXT data type by copying and pasting the following SQL commands into the MariaDB (mysql) prompt:

ALTER TABLE config
MODIFY COLUMN http_strip_domains TEXT,
MODIFY COLUMN saml_sso_url TEXT,
MODIFY COLUMN saml_slo_url TEXT,
MODIFY COLUMN saml_idp_entityid TEXT,
MODIFY COLUMN saml_sp_entityid TEXT;

## I attempted to add the default_lang column using this command:

ALTER TABLE config
ADD COLUMN default_lang VARCHAR(32) NOT NULL DEFAULT 'en_GB';

## I received the following error below:

ERROR 1060 (42S21): Duplicate column name 'default_lang'

## Now that the default_lang column exists, I checked to make sure all the fields had the correct values using the command:

DESC config;

## The newly created "default_lang" column showed a length of (5) for some reason (it should be 32), and a default setting of "en_US" (it should be en_GB), so I changed them by executing the block of two commands below to avoid any problems:

ALTER TABLE config
MODIFY COLUMN default_lang VARCHAR(32) NOT NULL DEFAULT 'en_GB';

## I then executed the DESC config; command and confirmed the default_lang had a character length of 32 and was set to a default of "en_GB":

DESC config;
  ...
  | default_lang                 | varchar(32)         | NO   |     | en_GB

I entered the following command because this setting was listed in on the upgrade notes when upgrading from Zabbix 5.0 to 7.0:

SET GLOBAL log_bin_trust_function_creators = 1;

The command below confirms the setting is enabled:

 SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
1 row in set (0.001 sec)

## I use the command below to retry the upgrade and tail the zabbix server log at once:

systemctl start zabbix-server && tail -f /var/log/zabbix/zabbix_server.log
 57852:20250205:125940.168 IPv6 support:              YES
 57852:20250205:125940.168 TLS support:               YES
 57852:20250205:125940.168 ******************************
 57852:20250205:125940.168 using configuration file: /etc/zabbix/zabbix_server.conf
 57852:20250205:125940.174 current database version (mandatory/optional): 06030057/06030057
 57852:20250205:125940.174 required mandatory version: 07000000
 57852:20250205:125940.174 mandatory patches were found
 57852:20250205:125940.175 starting automatic database upgrade
 57852:20250205:125940.188 database upgrade failed on patch 06030058, exiting in 10 seconds
 57852:20250205:125950.188 Zabbix Server stopped. Zabbix 7.0.9 (revision 05b8b05eefe)

As you can see the upgrade proceeds past patch 05010000 now but stopped at patch 06030058. Problem solved but now I have to resolve the next error! I'll make a forum post about the next error shortly. I hope this helps anyone having this error!

[cyber]

https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/src/libs/zbxdbupgrade/dbupgrade_5010.c#27

It tries to add field "default_lang" to a table "config". Something with rights to modify DB?

[Zabbix_To_Go]

I was able to add the "default_lang" column manually. Thanks cyber for posting a link to the actual code of the patch 05010000. I used to develop the solution. Here is a detailed summary of the steps I took to resolve the issue. For the purpose of this post my db user is 'zabbix' and hostname is 'localhost'.

I connected to the database and executed the command to show grants (permissions):

SHOW GRANTS FOR 'zabbix'@'localhost';

I executed the following command to give the user 'zabbix' the required privileges to modify table structures.
GRANT ALL PRIVILEGES ON zabbix.* TO 'zabbix'@'localhost';

At this point I restarted zabbix-server to retry the upgrade but it failed for me. Note that the upgrade might work for you if you are encountering this issue. I had to perform the remaining steps in this post to manually add "default_lang" column.

systemctl restart zabbix-server

## Selected zabbix database

USE zabbix;

## Outputted the structure of the config table to the screen to verify if the "default_lang" column exists. It did not.

DESC config;

## The GRANT statement executed a few steps earlier should include ALTER on the zabbix.* schema, which should be sufficient, but some MySQL/MariaDB configurations require explicit grants for ALTER at the global level. I executed this command below to grant ALTER at the global level to ensure I could run the following steps successfully:

GRANT ALTER ON *.* TO 'zabbix'@'localhost';

## I executed this command to ensure the changes thus far take effect without having to restart MariaDB:

FLUSH PRIVILEGES;

My zabbix server's database "config" table did not contain the "default_lang" column in the config table. My atempts to add the column failed with “Row size too large” (ERROR 1118) because the config table reached the maximum row size for InnoDB (8126 bytes per row) in MariaDB. Adding another column directly was impossible unless the size of the existing columns was reduced, so performed the next step.

Since VARCHAR columns take up fixed storage space in a row, you can convert some of VARCHAR columns to TEXT, which moves their storage out of the row, freeing up storage space. From the table schema, the columns below were eligible to convert to text:

• http_strip_domains (VARCHAR(2048))
• saml_sso_url (VARCHAR(2048))
• saml_slo_url (VARCHAR(2048))
• saml_idp_entityid (VARCHAR(1024))
• saml_sp_entityid (VARCHAR(1024))

## I converted the columns in the "config" table listed in the bullets above from VARCHAR to TEXT data type by copying and pasting the following SQL commands into the MariaDB (mysql) prompt:

ALTER TABLE config
MODIFY COLUMN http_strip_domains TEXT,
MODIFY COLUMN saml_sso_url TEXT,
MODIFY COLUMN saml_slo_url TEXT,
MODIFY COLUMN saml_idp_entityid TEXT,
MODIFY COLUMN saml_sp_entityid TEXT;

## I attempted to add the default_lang column using this command:

ALTER TABLE config
ADD COLUMN default_lang VARCHAR(32) NOT NULL DEFAULT 'en_GB';

## I received the following error below:

ERROR 1060 (42S21): Duplicate column name 'default_lang'

## Now that the default_lang column exists, I checked to make sure all the fields had the correct values using the command:

DESC config;

## The newly created "default_lang" column showed a length of (5) for some reason (it should be 32), and a default setting of "en_US" (it should be en_GB), so I changed them by executing the block of two commands below to avoid any problems:

ALTER TABLE config
MODIFY COLUMN default_lang VARCHAR(32) NOT NULL DEFAULT 'en_GB';

## I then executed the DESC config; command and confirmed the default_lang had a character length of 32 and was set to a default of "en_GB":

DESC config;
  ...
  | default_lang                 | varchar(32)         | NO   |     | en_GB

I entered the following command because this setting was listed in on the upgrade notes when upgrading from Zabbix 5.0 to 7.0:

SET GLOBAL log_bin_trust_function_creators = 1;

The command below confirms the setting is enabled:

 SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
1 row in set (0.001 sec)

## I use the command below to retry the upgrade and tail the zabbix server log at once:

systemctl start zabbix-server && tail -f /var/log/zabbix/zabbix_server.log
 57852:20250205:125940.168 IPv6 support:              YES
 57852:20250205:125940.168 TLS support:               YES
 57852:20250205:125940.168 ******************************
 57852:20250205:125940.168 using configuration file: /etc/zabbix/zabbix_server.conf
 57852:20250205:125940.174 current database version (mandatory/optional): 06030057/06030057
 57852:20250205:125940.174 required mandatory version: 07000000
 57852:20250205:125940.174 mandatory patches were found
 57852:20250205:125940.175 starting automatic database upgrade
 57852:20250205:125940.188 database upgrade failed on patch 06030058, exiting in 10 seconds
 57852:20250205:125950.188 Zabbix Server stopped. Zabbix 7.0.9 (revision 05b8b05eefe)

As you can see the upgrade proceeds past patch 05010000 now but stopped at patch 06030058. Problem solved but now I have to resolve the next error! I'll make a forum post about the next error shortly. I hope this helps anyone having this error!

[Coritah]

Did you ever get this solved?

Sorry for my bad guide :-) but hope it helps

I had the same error and i found that it was a DB problem because of it was created in an old version of MariaDB/my sql

I ran the command: tail /var/log/zabbix/zabbix_server.log
Result showed the problem table in the DB:

7662:20250716:220829.087 [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 `media_type` add `provider` integer default '0' not null]
7662:20250716:220829.087 database upgrade failed on patch 06030058, exiting in 10 seconds
7662:20250716:220839.088 Zabbix Server stopped. Zabbix 7.0.16 (revision e43512b75d0).

log in to the Mysql
mysql -u root
Run the command
SHOW TABLE STATUS FROM zabbix LIKE 'media_type' \G
If it shows the line "Row_format: Compact" This is your problem

To fix it :
mysql -u root
use zabbix
alter table hosts row_format = dynamic;

Link to sites commenting on this error:

https://www.zabbix.com/forum/zabbix-help/461140-db-upgrade-error-6-0-6-2

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