Ubuntu 22

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.

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 ?

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"

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.

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.

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 ?

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.

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 ?)

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

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='';

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

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?

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

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.

(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;

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)

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.

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?

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) ?

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;

see workaround