[ZBX-22760] Unable to find a specific host when using filter Created: 2023 May 03  Updated: 2024 Dec 03  Resolved: 2024 Dec 03

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Frontend (F), Server (S)
Affects Version/s: 6.0.17, 6.0.31
Fix Version/s: None

Type: Incident report Priority: Minor
Reporter: Nono Assignee: Zabbix Development Team
Resolution: Fixed Votes: 2
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Ubuntu 22


Attachments: PNG File image-2023-05-03-12-40-20-537.png     PNG File image-2023-05-03-12-41-06-302.png     PNG File limit.png     File schema.sql    
Issue Links:
Duplicate

 Description   

Steps to reproduce:

  1. Have 3 hosts named something53 & something54 & something55
  2. Navigate to Configurations > Hosts
  3. See the 3 hosts on the page (without filter)
  4. Try to filter to "something5" "5" and see only something53

Result:

with no filter, list is complete


**

with filter , list is INcomplete:

Expected:
See the whole list (54 & 55 included)



 Comments   
Comment by Aigars Kadikis [ 2023 May 08 ]

Thank you for reporting this issue.
I could not reproduce the problem with 6.0.17.
What is the total amount of hosts your instance is owning? We can see that under "Reports" => "System information". Is it over 1000 hosts?

Please try to change SQL limit. Go to "Administration" => "General" => "GUI" and change "Limit for search and filter results" to a bigger number, for example, 5000 or 10000:

Let us know if the issue is there.

Comment by Nono [ 2023 May 09 ]

The limit was already set to 10000

Which doesn't surprised me, as the amount of host I was looking was 55

So, you're telling me that by creating 55 (fake) hosts, numbers from 01 to 55, you can still find the 54 & 55 ?

Is there a way to see where could be the culprit ?

Comment by Aigars Kadikis [ 2023 May 29 ]

The culprit can be a total amount of hosts. Try this DB query:

SELECT COUNT(*) FROM hosts WHERE flags IN (0,4) AND status IN (0,1);

Flags=4 means the object has been created from the functionality of the host prototype. status IN (0,1) means enabled and disabled host.

How many total records are in table?

SELECT COUNT(*) FROM hosts;

What is DB engine? If MySQL, then check "COLLATE":

show create table hosts\G

The last line should be

  • "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin" or
  • "ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin"
Comment by Nono [ 2023 May 30 ]
mysql> SELECT COUNT(*) FROM hosts WHERE flags IN (0,4) AND status IN (0,1);
+----------+
| COUNT(*) |
+----------+
|      156 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM hosts;
+----------+
| COUNT(*) |
+----------+
|      241 |
+----------+
1 row in set (0.04 sec)

mysql> show create table hosts\G
[...]
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)
Comment by Aigars Kadikis [ 2023 Jun 01 ]

Thank you for extra data. Very little amount of hosts in database.
What is the version of DB server?

SELECT VERSION();

Please confirm that these hosts exactly are also causing trouble for the search field (when searching "something5"):

something53
something54
something55

Create these hosts and try the search.

Comment by Nono [ 2023 Jun 09 ]

Hi Aigars,

Sorry for the very late reply ...

Here is the result :

mysql> SELECT VERSION();
+-------------------------+
| VERSION()               |
+-------------------------+
| 8.0.33-0ubuntu0.22.04.1 |
+-------------------------+
1 row in set (0.00 sec)

Regarrding the test with "something" it's even worst that with the other host.
I can't find anything with the following filter:
some
something
something5
nor
something53 or something54 or something55 !!
I can only see them when I do not have any filter. (or actually, using only "s" show me other host containing (not starting with) a "s" on their name, but without the "something" one.

Comment by Nono [ 2023 Jun 13 ]

Is going worst and worst as I can't find anywhere the new hosts I'm creating ... This make zabbix hardly usable when it turns to problem & monitoring.
Any idea how to fix this issue ?

Comment by Aigars Kadikis [ 2023 Jun 16 ]

You can backup the data and create a new/secondary database. Then take a schema from a stock Zabbix 6.0 installation.

Create a light backup of your production database:

mysqldump \
--flush-logs \
--single-transaction \
--no-tablespaces \
--set-gtid-purged=OFF \
--ignore-table=history \
--ignore-table=history_log \
--ignore-table=history_str \
--ignore-table=history_text \
--ignore-table=history_uint \
--ignore-table=trends \
--ignore-table=trends_uint \
nameOfZabbixDB > data.sql

By using official instruction, create new DB in same DB engine:

create database newDB character set utf8mb4 collate utf8mb4_bin;

Since user already exists, allow to access new database:

grant all privileges on newDB.* to zabbix@localhost; 
set global log_bin_trust_function_creators = 1;

Insert stock schema.sqlfor 6.0 and put back your data

cat schema.sql | mysql newDB
cat data.sql | mysql newDB

If the second step fails, there is a problem (data inconsistency) with the relational database.

Open MySQL client again and set back:

set global log_bin_trust_function_creators = 0; 

Open /etc/zabbix/web/zabbix.conf.php and temporarily set the frontend to be attached to this copy of the database.

Check if the search works.

Comment by Nono [ 2023 Jun 16 ]

Hi,

First of all, thanks for the fast answer.
Second, MANY thanks for the cristal clear steps to do that. You just save me a lot of hassle by explainig everything perfectly !

Unfortunately, all of the steps suceed, but the search is still not functionning.
Also, a "quicker" way to check is that my new hosts aren't "findable" on the "Monitoring > Problems" on the "Host" field. (I guess it's all related ?)

Comment by Nono [ 2023 Jun 22 ]

I just updated to 6.0.18 and it's still not functionning

Comment by Nono [ 2023 Jun 27 ]

I think the culprit is the field "name_upper" on the "hosts" table.
All newer hosts have an empty "name_upper" and as soon as we add a name there, the host is findable again
this sounds like a bug, as it's not populated either by creating (manually) a new host, nor using the "import" method.

Workaround :

 update hosts set name_upper=upper(name) where name_upper='';
Comment by Nono [ 2023 Jul 26 ]

Hi Aigars,

Could you confirm or deny the solution I proposed ?
So far, I can (as workaround) execute the sql command, but it would be nice to get confirmation or a fix for this.

Best Regards,

Nono

Comment by Aigars Kadikis [ 2023 Aug 04 ]

Maybe I made a mistake earlier in the instruction. Instead of doing:

cat schema.sql | mysql newDB
cat data.sql | mysql newDB

According to instructions https://www.zabbix.com/download?zabbix=6.0&os_distribution=ubuntu&os_version=22.04&components=server_frontend_agent&db=mysql&ws=nginx , need to do:

cat schema.sql | mysql newDB --default-character-set=utf8mb4
cat data.sql | mysql newDB --default-character-set=utf8mb4

Could you try again and reconfirm whether the problem is there or gone?

Comment by Nono [ 2023 Aug 17 ]

Hi @Aigars,

The problem remains the same. Did you look into the workaround I provided ?
It doesn't seems to be related to the default caracter set

Comment by Aigars Kadikis [ 2023 Oct 02 ]

Hello Nono ,

I did not look for a workaround because I could not reproduce the issue. If the issue is still relevant, could you:
1) Export the hosts from the instance which is having a problem
2) Kickstart Zabbix appliance https://cdn.zabbix.com/zabbix/appliances/stable/6.0/6.0.22/zabbix_appliance-6.0.22-ovf.tar.gz via VirtualBox. The password for user 'root' is 'zabbix'.
3) Import hosts. Try reproducing the issue.

The appliance runs with Alma Linux 8.8 and MySQL 8.0.32, PHP 7.4.33. I still think the issue is at MySQL level - An unusual configuration to your DB engine.

You can also import the backup in the appliance like you did before.

Comment by Nono [ 2023 Oct 04 ]

(only) importing the host on the appliance seems to work (at least, I can find it).
But, I ran the following on my "faulty" DB and the character-set seems okay :

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

What would you then suggest to "fix" my DB ?
somthing like this (untested) ?

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Comment by Nono [ 2023 Oct 16 ]

Hi Aigars,

I've now ran the commands below (to basically changed my character set & collation) from :

CHARSET=utf8mb3 COLLATE=utf8mb3_bin

to

CHARSET=utf8mb4 COLLATE=utf8mb4_bin

with the following :

ALTER DATABASE DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_bin;

ALTER TABLE acknowledges CONVERT TO CHARACTER SET DEFAULT;
ALTER TABLE actions CONVERT TO CHARACTER SET DEFAULT;
[...]
ALTER TABLE widget CONVERT TO CHARACTER SET DEFAULT;
ALTER TABLE widget_field CONVERT TO CHARACTER SET DEFAULT;

The problem remains the same ... So it doesn't seems to be related to the collation (especially as _bin is case sensitive, right ?)

EDIT: I've also follow this procedure : https://www.zabbix.com/documentation/6.0/en/manual/appendix/install/db_charset_coll without success (the procedure succeed, but the problem is still present)

Comment by Witek Woicki [ 2024 Feb 19 ]

I have the same issue. New hosts do not get the name_upper field populated and they can't be found when using any kind of search.

Example:

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| hostid | proxy_hostid | host        | status | lastaccess | ipmi_authtype | ipmi_privilege | ipmi_username | ipmi_password | maintenanceid | maintenance_status | maintenance_type | maintenance_from | name        | flags | templateid | description | tls_connect | tls_accept | tls_issuer | tls_subject | tls_psk_identity | tls_psk | proxy_address | auto_compress | discover | custom_interfaces | uuid | name_upper |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|  10824 |         NULL | test_host_1 |      0 |          0 |            -1 |              2 |               |               |          NULL |                  0 |                0 |                0 | test_host_1 |     0 |       NULL |             |           1 |          1 |            |             |                  |         |               |             1 |        0 |                 0 |      |            |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

 

When I manually populate the field:

update hosts set name_upper=upper(name) where name_upper='';

the host immediately appears in the GUI search results.

 

 

 

Comment by Aigars Kadikis [ 2024 Jul 05 ]

This patch exists on version 6.4 by default:

update items set name_upper=upper(name); 

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

There are at least 3 users for whom a search on 6.0 does not satisfy, and after applying this DB update command, it makes the search functionality work according to needs.

Could we backport to 6.0?

Comment by Nono [ 2024 Nov 29 ]

I'm facing an (likely related?) issue when I wanted to migrate to 7.0
I explain it on the following forum entry https://www.zabbix.com/forum/zabbix-troubleshooting-and-problems/495024-name_upper-already-exists-on-zabbix-6-4-unable-to-properly-upgrade-to-zabbix-7-0 , which I'll copy/paste here for convinience :

-------

Hi follow the guideline from https://www.zabbix.com/documentation/current/en/manual/installation/upgrade/packages/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) ?

Comment by Nono [ 2024 Dec 03 ]

I fixed the issue by doing :

  1. mysqldump zabbix > full_db.sql
  2. 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;
  3. zgrep -v "^INSERT" /usr/share/zabbix-sql-scripts/mysql/server.sql.gz > zabbix_schema_only.sql
  4. cat zabbix_schema_only.sql | mysql zabbix
  5. cat full_zabbix_data_only.sql | mysql zabbix
    mysql> set global log_bin_trust_function_creators = 0;
    mysql> quit;
Comment by Nono [ 2024 Dec 03 ]

see workaround

Generated at Wed Jul 09 11:30:41 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.