Error on some Overviews in the Mobile site

[pevans_om]

Infos:

• Used Zammad version: 6.5.2
• Used Zammad installation type: package
• Operating system: AlmaLinux8
• Browser + version: Edge/Chrome/Firefox Desktop and Mobile

Expected behavior:

• Select an Overview should show a list of tickets

Actual behavior:

• When using the mobile site, selecting certain overviews generates an error: ‘An error occurred during the Operation’. No tickets are displayed.
• The browser debug console shows the following error:
  [GraphQL error - ActiveRecord::StatementInvalid]: PG::UndefinedObject: ERROR: collation “en-GB-x-icu” for encoding “UTF8” does not exist
  LINE 1: …s".“id”, “groups”.“name” ORDER BY “groups”.“name” COLLATE "e…
• There is no error when viewing the overview on the desktop site.
• The error occurs regardless of the web browser used.
• It consistently appears on the Overview ‘My assigned tickets’. The error persists even if the overview is empty - no tickets assigned to the agent.
• We also see the error on some other built-in overviews, as well as some of our custom overviews. Other overviews work fine, though.

Unfortunately, I am unsure when this behaviour started exactly. It was reported this week, but then some of my agents said this has been there for months… they just chose to ignore it…

Steps to reproduce the behavior:

• Open the mobile site (https://domain/mobile)
• Select a ticket overview from the list.

[mantas]

Hey @pevans_om

How did you install Postgres on your system?

Could you run following query in Postgres?

SELECT collname, collprovider, collcollate FROM pg_collation WHERE collprovider = 'i' ORDER BY collname;

Do you get a bunch of lang-code-x-icu style rows? Any en-XX-x-icu? Looks like your Postgres installation may be missing ICU support?

[pevans_om]

Hi

We’ve been running Zammad for a few years. I can’t remember how we installed postgresql. We followed a guide for deploying on AlmaLinux8, which no longer exists…

In answer to your query, we do not have any icu records in the database:

zammad=> SELECT collname, collprovider, collcollate FROM pg_collation WHERE collprovider = ‘i’ ORDER BY collname;
collname | collprovider | collcollate
----------±-------------±------------
(0 rows)

[mantas]

Hm. Could you try to reinstall Postgres from official packages? In my experience official packages do come with ICU collations.

[pevans_om]

Thanks for the guidance. We have no ‘icu’ entries in pg_collation. After some investigations I can see we’re used the default Postgresql package for AlmaLinux 8, which installs Postgresql 10. And likely does not include icu collation. So we’ll probably have to upgrade Postgresql with an updated package

[pevans_om]

I upgraded to Postgresql 16 which is the newest appstream version of Postgresql available for Alma Linux. The upgrade resolve this issue.