name_upper already exists on Zabbix 6.4, unable to properly upgrade to Zabbix 7.0

[Nono]

Hi there o/

I follow the guideline from https://www.zabbix.com/documentation.../debian_ubuntu until the point to start zabbix-server and here the issues starts:

First error (on /var/log/zabbix/zabbix_server.log) :

4244:20241128:124848.131 starting automatic database upgrade
4244:20241128:124848.140 [Z3005] query failed: [1060] Duplicate column name 'name_upper' [alter table `hosts` add `name_upper` varchar(128) default '' not null]
4244:20241128:124848.141 database upgrade failed on patch 06030064, exiting in 10 seconds

I tried to "fix" this issue by doing :

alter table hosts drop column name_upper;

Then 2nd error :

5232:20241128:145118.660 starting automatic database upgrade                                                                              
5232:20241128:145119.257 completed 0% of database upgrade                                                                                
5232:20241128:145123.011 completed 1% of database upgrade                                                                                
5232:20241128:145123.017 [Z3005] query failed: [1060] Duplicate column name 'name_upper' [alter table `items` add `name_upper` varchar(255) default '' not null]
5232:20241128:145123.018 database upgrade failed on patch 06030069, exiting in 10 seconds

I tried to "fix" this issue by doing :

alter table items drop column name_upper;

Then third error:

5389:20241128:145437.527 starting automatic database upgrade
5389:20241128:145437.529 [Z3005] query failed: [1061] Duplicate key name 'items_9' [create index items_9 on items (hostid,name_upper)]    
5389:20241128:145437.530 database upgrade failed on patch 06030070, exiting in 10 seconds

I tried to "fix" this issue by doing :

drop index items_9 on items;

And the upgrade where possible ...

But looking for a host on the frontend, give errors which seems to indicate that the collumn is now empty/not existing :

[LIST][*]Error in query [SELECT h.hostid,h.name,h.status FROM hosts h WHERE h.flags IN (0,4) AND h.status IN (0,1) AND ((h.name_upper LIKE '%ANTH%' ESCAPE '!')) ORDER BY h.name LIMIT 10001] [Unknown column 'h.name_upper' in 'where clause'] [zabbix.php:17 → require_once() → ZBase->run() → ZBase->processRequest() → CController->run() → CControllerHostViewRefresh->doAction() → CControllerHost->getData() → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CHost->get() → CApiService->customFetch() → DBselect() → trigger_error() in include/db.inc.php:243][/LIST]
​

So i guess removing the column wasn't the best idea (I don't mind, I have a backup) but what's the solution now to fix the 1st error (and likely the other one) ?

[Nono]

My schema was somehow faulty so what I did to solve the issue (not sure it's normal, but at least, it works at my end now):

# mysqldump zabbix > full_db.sql
# mysqldump --single-transaction --no-create-info --skip-triggers zabbix > full_zabbix_data_only.sql
mysql> drop database zabbix;
mysql> create database zabbix character set utf8mb4 collate utf8mb4_bin;
mysql> grant all privileges on zabbix.* to zabbix@localhost;
mysql> set global log_bin_trust_function_creators = 1;
mysql> quit;
# zgrep -v "^INSERT" /usr/share/zabbix-sql-scripts/mysql/server.sql.gz > zabbix_schema_only.sql
# cat zabbix_schema_only.sql | mysql zabbix
# cat full_zabbix_data_only.sql | mysql zabbix
mysql> set global log_bin_trust_function_creators = 0;
mysql> quit;​

And follow again the upgrade process which went well this time.

[cyber]

There is something else... it should not complain about such things while upgrading from 6.4 to 7...
Can you share a bit more of that server log, where is also visible those schema version numbers (required/optional etc)

[Nono]

Hi Cyber,

After deleting the "items_9", here is the server log:

5539:20241128:150958.987 current database version (mandatory/optional): 06030070/06030070
  5539:20241128:150958.987 required mandatory version: 07000000
  5539:20241128:150958.987 mandatory patches were found
  5539:20241128:150958.988 starting automatic database upgrade
  5539:20241128:151015.759 slow query: 16.770357 sec, "update items set name_upper=upper(name)"
  5539:20241128:151022.683 slow query: 6.923218 sec, "commit;"
  5539:20241128:151022.683 completed 0% of database upgrade
  5539:20241128:151024.708 completed 1% of database upgrade
  5539:20241128:151028.920 slow query: 4.211074 sec, "alter table `widget_field` add constraint c_widget_field_9 foreign key (`value_userid`) references `users` (`userid`) on delete cascade"
  5539:20241128:151032.370 completed 2% of database upgrade zz0.dqll8jc9h5zz

Once the upgrade done :

  5539:20241128:151623.980 completed 86% of database upgrade
  5539:20241128:151629.573 completed 87% of database upgrade
  5539:20241128:151634.113 slow query: 3.490907 sec, "update dbversion set mandatory=6050268,optional=6050268"
  5539:20241128:151634.771 completed 88% of database upgrade
  5539:20241128:151636.666 completed 89% of database upgrade
  5539:20241128:151640.277 completed 90% of database upgrade
  5539:20241128:151649.457 slow query: 5.825989 sec, "alter table `proxy_group_rtdata` add constraint c_proxy_group_rtdata_1 foreign key (`proxy_groupid`) references `proxy_group` (`proxy_groupid`) on delete cascade"
  5539:20241128:151649.482 completed 91% of database upgrade
  5539:20241128:151651.536 completed 92% of database upgrade
  5539:20241128:151654.531 completed 93% of database upgrade
  5539:20241128:151656.509 completed 94% of database upgrade
  5539:20241128:151656.718 completed 95% of database upgrade
  5539:20241128:151657.529 completed 96% of database upgrade
  5539:20241128:151659.665 completed 97% of database upgrade
  5539:20241128:151700.206 completed 98% of database upgrade
  5539:20241128:151701.012 completed 99% of database upgrade
  5539:20241128:151704.308 completed 100% of database upgrade
  5539:20241128:151706.397 database upgrade fully completed
  5539:20241128:151707.253 database could be upgraded to use primary keys in history tables
  5892:20241128:151708.061 starting HA manager
  5892:20241128:151708.382 HA manager started in active mode zz0.p0z03g0brimzz

And after a restart of the service :

  5539:20241128:152512.123 Zabbix Server stopped. Zabbix 7.0.6 (revision c1d7a081969).
  6104:20241128:152512.143 Starting Zabbix Server. Zabbix 7.0.6 (revision c1d7a081969).
  6104:20241128:152512.143 ****** Enabled features ******
  6104:20241128:152512.143 SNMP monitoring:           YES
  6104:20241128:152512.143 IPMI monitoring:           YES
  6104:20241128:152512.143 Web monitoring:            YES
  6104:20241128:152512.143 VMware monitoring:         YES
  6104:20241128:152512.143 SMTP authentication:       YES
  6104:20241128:152512.143 ODBC:                      YES
  6104:20241128:152512.143 SSH support:               YES
  6104:20241128:152512.143 IPv6 support:              YES
  6104:20241128:152512.143 TLS support:               YES
  6104:20241128:152512.143 ******************************
  6104:20241128:152512.143 using configuration file: /etc/zabbix/zabbix_server.conf
  6104:20241128:152512.163 current database version (mandatory/optional): 07000000/07000015
  6104:20241128:152512.181 required mandatory version: 07000000 zz0.42shk60qz0tzz

I hope this helps ?
Otherwise, could you please let me know how to check/See the schema version ?

[cyber]

This log shows that everything is properly done... it did not break on column creation as in your initial post...

Just some slow queries (that is normal) and suggestion to upgrade to primary keys in history... https://www.zabbix.com/documentation...b_primary_keys

[Nono]

Wait ..
This log I provided was AFTER I removed the 2 colums + the item ...

BEFORE the upgrade it shows this :

  1681:20241202:155428.324 Starting Zabbix Server. Zabbix 7.0.6 (revision c1d7a081969).
  1681:20241202:155428.329 ****** Enabled features ******
  1681:20241202:155428.329 SNMP monitoring:           YES
  1681:20241202:155428.329 IPMI monitoring:           YES
  1681:20241202:155428.329 Web monitoring:            YES
  1681:20241202:155428.329 VMware monitoring:         YES
  1681:20241202:155428.329 SMTP authentication:       YES
  1681:20241202:155428.329 ODBC:                      YES
  1681:20241202:155428.329 SSH support:               YES
  1681:20241202:155428.329 IPv6 support:              YES
  1681:20241202:155428.329 TLS support:               YES
  1681:20241202:155428.330 ******************************
  1681:20241202:155428.330 using configuration file: /etc/zabbix/zabbix_server.conf
  1681:20241202:155428.510 current database version (mandatory/optional): 06030063/06030063
  1681:20241202:155428.510 required mandatory version: 07000000
  1681:20241202:155428.510 mandatory patches were found
  1681:20241202:155428.523 starting automatic database upgrade
  1681:20241202:155428.613 [Z3005] query failed: [1060] Duplicate column name 'name_upper' [alter table `hosts` add `name_upper` varchar(128) default '' not null]
  1681:20241202:155428.613 database upgrade failed on patch 06030064, exiting in 10 seconds
  1681:20241202:155438.614 Zabbix Server stopped. Zabbix 7.0.6 (revision c1d7a081969).

So at this point : I basically just apt update my package to the 7.x version (from 6.4).

[Nono]

My schema was somehow faulty so what I did to solve the issue (not sure it's normal, but at least, it works at my end now):

# mysqldump zabbix > full_db.sql
# mysqldump --single-transaction --no-create-info --skip-triggers zabbix > full_zabbix_data_only.sql
mysql> drop database zabbix;
mysql> create database zabbix character set utf8mb4 collate utf8mb4_bin;
mysql> grant all privileges on zabbix.* to zabbix@localhost;
mysql> set global log_bin_trust_function_creators = 1;
mysql> quit;
# zgrep -v "^INSERT" /usr/share/zabbix-sql-scripts/mysql/server.sql.gz > zabbix_schema_only.sql
# cat zabbix_schema_only.sql | mysql zabbix
# cat full_zabbix_data_only.sql | mysql zabbix
mysql> set global log_bin_trust_function_creators = 0;
mysql> quit;​

And follow again the upgrade process which went well this time.