[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: |
![]() ![]() ![]() ![]() |
||||
Issue Links: |
|
Description |
Steps to reproduce:
Result: with no filter, list is complete with filter , list is INcomplete: Expected: |
Comments |
Comment by Aigars Kadikis [ 2023 May 08 ] |
Thank you for reporting this issue. 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: |
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
|
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. 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. |
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. |
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.sql 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. Unfortunately, all of the steps suceed, but the search is still not functionning. |
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. 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 ? 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 ? |
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: 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).
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 ? 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:
When I manually populate the field:
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 ------- 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] |
Comment by Nono [ 2024 Dec 03 ] |
I fixed the issue by doing :
|
Comment by Nono [ 2024 Dec 03 ] |
see workaround |