Filters stop working after 5.4 to 6.0.13 upgrade

[surgeon_2022]

Hi everyone,

Need a help to solve the issue.
I upgrades zbx 5.4 to 6.0.13 and I did it in following steps

1. Got backup without history and trends but schema on 5.4
2. Fresh install of 6.0.13
3. Backup restore on Mariadb no issue or error
4. Migrated Mariadb to postgresql + timescaledb with no issue or error

zbx worked for about 1 day after each step 3 and 4. No changes like adding or modifying hosts, templates etc. were done during this time

Later on I found that newly added items like hosts, templates etc are not displayed if "Name" filter is used e.g for Configuration > Hosts, Configuration> templates. etc.
The filter shows empty even after updating names of existing hosts, however I was able to find altered host using old name

In database I found that there is a filed called "name_upper" e.g in table "hosts" and that field is not being populated while new item creating or not being updated after name change.
Manually altering the filed helps to get filter works.

What I've did wrong? is there any way to fix it without reinstalling all that stuff.

Regards,

[cyber]

What I've did wrong? is there any way to fix it without reinstalling all that stuff.

Are you sure you used correct DB schema... ? "name_upper" comes with 6.2. Your 6.0 should not have it (mine definitely does not... )

Check versions... if your db and server are 6.2, then maybe you get away with frontend update? Just random thought...

[surgeon_2022]

Thank you for your reply

Are you sure you used correct DB schema... ? "name_upper" comes with 6.2. Your 6.0 should not have it (mine definitely does not... )

Check versions... if your db and server are 6.2, then maybe you get away with frontend update? Just random thought...

It is definitely 6.0.13


psql (PostgreSQL) 15.2 (Debian 15.2-1.pgdg110+1)
TimescaleDB version 2.9.3

You can find creating name_upper column in schema.sql at least for postgresql script in zabbix src archive "zabbix-6.0.13/database/postgresql/schema.sql"

[cyber]

Aargh... DB schema changes in minor version... you are correct... its not there in 6.0.9 and appears somewhere later...(EDIT: in 6.0.11... )

[surgeon_2022]

Aargh... DB schema changes in minor version... you are correct... its not there in 6.0.9 and appears somewhere later...(EDIT: in 6.0.11... )

Not sure if I did right thing. I applied schema script "zcat /usr/share/zabbix-sql-scripts/postgresql/server.sql.gz | sudo -u zabbix psql zabbix" on top of existing database. I've got a lot of errors that some tables, records etc are exists, however name_upper filed started being populated/changed for new items. So far no issue or wired logs. Let see how it goes

[cyber]

So lets start from beginning... you also changed platform(s).. Whatever they way you switched from mysql to PG, doesn't really matter, I think. You had originally v5.4.. so that should have been also the schema version before you started up new server. Because starting up new server will do DB upgrade tasks for you. like adding those needed fields in tables etc... I think you may have stepped on a nail here with applying that new schema ...

[surgeon_2022]

So lets start from beginning... you also changed platform(s).. Whatever they way you switched from mysql to PG, doesn't really matter, I think. You had originally v5.4.. so that should have been also the schema version before you started up new server. Because starting up new server will do DB upgrade tasks for you. like adding those needed fields in tables etc... I think you may have stepped on a nail here with applying that new schema ...

I repeated all steps in the lab and tested creating new itmes. I found that the issue started taking place right after mariadb to psql migration. Migration steps includes schema creation and I think something went wrong at that time. I have not got any errors though during migration process.

[cyber]

So
dump from 5.4
migrate from mariadb to PG (includes schema creation)
start up 6.0.13 which now uses PG?
Correct order of things?

I think you should use schema from 5.4 during migration as your db dump is of that version... When you first start up your shiny new 6.0.13, then it does all the necessary DB modifications...