Column sorts no longer working 4.4.6->5.0.2/5.0.4 upgrade

I’m getting reports from some users that column sorts no longer work for their search results. I went to someones machine & tried clearing their local browser cache & that didn’t fix it. If a user clicks a column header in a reult set you can see the arrow change color (up,down,nothing) but the data doesn’t change.

Is there something I may have overlooked?

Load the page up in Firefox, hit F12, go to the Network tab, reload the page and look for 404s. It sounds like some JavaScript isn’t being loaded.

1 Like

Yup. Looks like there is an issue. I get two warnings and an error.
Warnings:
-This page uses the non standard property “zoom”. Consider using calc() in the relevant property values, or using “transform” along with “transform-origin: 0 0”.
-The script from “http://service-desk.qvii.net/NoAuth/SkipQuotes/js/SkipQuotes.js” was loaded even though its MIME type (“text/html”) is not a valid JavaScript MIME type.
Error:
Uncaught ReferenceError: doOnLoad is not defined
http://XXX.XXX.net/Search/Results.html?Order=ASC|DESC|ASC|ASC|ASC&OrderBy=id|Created&SavedSearchId=RT::System-1-SavedSearch-3&sc=b94e69e1:69

We did switch servers as part of this upgrade (Nginx over Apache). Is it possible there is something that got missed in that set up? I wasn’t the admin who performed the upgrade, and that admin is no longer at the company to ask.

May have found what is doing it… I found a /opt/rt5/var/nginx/rt.server.conf file that is included in our nginx config. It has the following lines:

 gzip             on;
    gzip_min_length  500;
    gzip_proxied     any;
    gzip_types
        text/plain text/css
        application/x-javascript
        application/javascript
    ;
    gzip_disable     "MSIE [1-6]\.";

(among others)
Do I need to modify this file?

I’m not sure, I’ve not used nginx much. Try getting just the file that’s failing in another browser window. Maybe exclude that one path in the nginx gzip config?

I’ll see what I can find. Getting tugged in a different direction now. Will update if I manage to work something out.

Any chance you figured it out? I’m experiencing the same problem after updating to 5.0.4 - clicking the columns changes the arrow direction, but not the results order. Even in things like manual searches and the “10 newest unowned tickets” dashboard things aren’t sorted properly.

Sadly, no. I’m running into other issues as well. I discovered my database upgrades didn’t complete despite not seeing the ‘make upgrade-database’ no failing (that I can remember).

Are you running your install on nginx or Apache?

This is normal, you’re just telling NGINX to compress plain text which is fine & normal.

As long as you aren’t running gzip module on already compressed content (jpgs pngs etc) which would be wasteful and out of spec, which you aren’t doing.

May have found what is doing it… I found a /opt/rt5/var/nginx/rt.server.conf file that is included in our nginx config. It has the following lines:

 gzip             on;
    gzip_min_length  500;
    gzip_proxied     any;
    gzip_types
        text/plain text/css
        application/x-javascript
        application/javascript
    ;
    gzip_disable     "MSIE [1-6]\.";

(among others)
Do I need to modify this file?

I’m using Apache with mod_fcgid. I don’t see any errors on the configuration page like you did - the RT upgrade history looks like it did the database upgrades properly.

So either I have a different problem with the same symptoms, or neither of us are on the right track for what’s causing it yet.

For even more weirdness, sorting by MULTIPLE columns seems to work, but sorting by a single column does not.

eg: Working, sort by Created then Owner.Name, tickets show up in order created: /Search/Results.html?SearchType=Ticket&Order=DESC%7CASC&OrderBy=Created%7COwner.Name

NOT working, tickets show up unsorted (or oldest first): /Results.html?SearchType=Ticket&Order=DESC&OrderBy=Created

Even stranger, SOME single field sorts do work. Replacing Created with Owner or Requestors.Name both sort by those fields. Created, LastUpdated, or Priority do not work and just show tickets in order by ID (presumably the order they are returned by the database?).

Do you see the same strange behavior?

I tried the same queries on https://try.requesttracker.io and they all work properly there, so it’s either an issue with my install or a bug that only happens under certain conditions.

I’m seeing it randomly working for me. I just tried running a search & it appears to sort properly. The only thing I can think of I did was to clear my Mason cache via the UI while looking into a different problem. Not sure if that did it or not as I manually had cleared my cache several times prior.

Where can you clear it from the UI? I’ve tried deleting the obj files in mason cache which didn’t help but I can’t find any option in the UI to clear caches.

I think it’s a new addition to 5.0.x. Log in as root or someone with administrative rights. Go to ‘Admin’ → ‘Tools’ → ‘System Configuration’. Scrolling down, you should find this:

Ah, I must have scrolled right past that. Thanks! Didn’t seem to make any difference though.

Even more weirdness…logged in as the RT root user sort seems to work perfectly everywhere, dashboard sorts properly, clicking any column sorts it. But under my regular (privileged), user account it’s completely broken.

I guess that narrows it down to permissions, account settings or caching.

I’ve tried different browsers on different machines, so it can’t be local caching of old javascript files or anything like that.

Narrowing it down a bit more, I’m pretty sure it’s a permissions issue.

My regular privileged users have the ShowTicket permission assigned by groups at the queue level, since they don’t need to see all the queues just specific ones.

If I give a user the ShowTicket permission at the global level, or give it to everyone, sorting works perfectly. As soon as I take that away, it stops working.

But doing that shows them all the tickets in all the queues, which I don’t want to do. They do have ShowTicket for specific queues, but it seems that isn’t good enough for the new sorting system.

If you go to Admin->Global->Group Rights and give Privileged group the View ticket summaries, does your sort start working?

I would say yes, that does fix it. I spoke to someone who had to rewrite some of their widgets to get around the issue. I used a group for our Service Department and added the View ticket summaries". Once she disabled her changes she says sorting is working.

I think you may have found it. If that is the case, I wonder what introduced the bug in the first place.

I think I’m making some progress here, and it looks like a genuine bug to me.

Just to be sure, I cloned the VM that RT runs in and removed all plugins and all overrides from /opt/rt5/local/html. No change.
Brand new searches created with the query builder don’t sort properly, so it’s not something wrong with a widget or dashboard.

That leaves just that permission.

Next I turned on statement logging, and looked over the SQL logs for clicking on “10 newest unowned tickets” with and without the global ShowTicket permission. Those lead to very different queries.

With ShowTicket permission:

[3477] [Thu Jun  1 18:40:32 2023] [debug]: gemsley - SQL(0.000259s): SELECT main.*, COUNT(main.id) OVER() AS search_builder_count_all FROM Tickets main JOIN Queues Queues_1  ON ( Queues_1.id = main.Queue )  WHERE (main.IsMerged IS NULL) AND (main.Status != ?) AND (main.Type = ?) AND (main.Owner = ? AND  (  ( Queues_1.Lifecycle = ? AND main.Status IN (?, ?, ?) )  OR  ( Queues_1.Lifecycle = ? AND main.Status IN (?, ?, ?) )  ) )  ORDER BY main.Created DESC  LIMIT ?;  [ bound values: 'deleted' 'ticket' '6' 'approvals' 'new' 'open' 'stalled' 'default' 'new' 'open' 'stalled' '50' ] (/opt/rt5/sbin/../lib/RT/Interface/Web.pm:1423)

Without ShowTicket permission:

[3461] [Thu Jun  1 18:32:29 2023] [debug]: gemsley - SQL(0.000187s): SELECT main.*, COUNT(main.id) OVER() AS search_builder_count_all FROM (SELECT DISTINCT main.* FROM Tickets main LEFT JOIN Groups Groups_2  ON ( Groups_2.Domain = ? ) AND ( Groups_2.Instance = main.id ) JOIN Queues Queues_1  ON ( Queues_1.id = main.Queue ) LEFT JOIN CachedGroupMembers CachedGroupMembers_3  ON ( CachedGroupMembers_3.Disabled = ? ) AND ( CachedGroupMembers_3.MemberId IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ) AND ( CachedGroupMembers_3.GroupId = Groups_2.id )  WHERE ( ( main.Queue IN (?, ?, ?, ?, ?, ?) OR  ( CachedGroupMembers_3.MemberId IS NOT NULL AND LOWER(Groups_2.Name) IN (?, ?) )  OR  ( main.Owner = ? )  ) ) AND (main.IsMerged IS NULL) AND (main.Status != ?) AND (main.Type = ?) AND (main.Owner = ? AND  (  ( Queues_1.Lifecycle = ? AND main.Status IN (?, ?, ?) )  OR  ( Queues_1.Lifecycle = ? AND main.Status IN (?, ?, ?) )  ) )  ORDER BY main.Created DESC ) main LIMIT ?;  [ bound values: 'RT::Ticket-Role' '0' '38' '837' '977' '950' '137720' '918' '48484' '903' '928' '976' '815' '952' '961' '962' '959' '963' '960' '958' '964' '965' '967' '966' '913' '927' '29' '1280' '802' '984' '128118' '796' '978' '981' '1016' '979' '51375' '1021' '16333' '1084' '83712' '45876' '126694' '83909' '26870' '18429' '137263' '992' '22500' '19948' '39078' '113663' '1415' '22466' '120659' '120715' '120658' '980' '122616' '985' '973' '949' '801' '1083' '987' '58468' '1' '15' '16' '18' '19' '20' 'requestor' 'cc' '38' 'deleted' 'ticket' '6' 'default' 'new' 'open' 'stalled' 'approvals' 'new' 'open' 'stalled' '10' ] (/opt/rt5/sbin/../lib/RT/Interface/Web.pm:1423)

That explains some of the older complaints I saw about not having ShowTicket global permission causing slowdowns before. It uses very different queries if it has to check permissions. Those were kind of hard to read though, so I enabled mysql’s statement logging to see what the SQL server was actually running.

With ShowTicket:

SELECT main.*, COUNT(main.id) OVER() AS search_builder_count_all FROM Tickets main JOIN Queues Queues_1  ON ( Queues_1.id = main.Queue )  WHERE (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND (main.Owner = '6' AND  (  ( Queues_1.Lifecycle = 'default' AND main.Status IN ('new', 'open', 'stalled') )  OR  ( Queues_1.Lifecycle = 'approvals' AND main.Status IN ('new', 'open', 'stalled') )  ) )  ORDER BY main.Created DESC  LIMIT 50

Without ShowTicket:

SELECT main.*, COUNT(main.id) OVER() AS search_builder_count_all FROM (SELECT DISTINCT main.* FROM Tickets main LEFT JOIN Groups Groups_2  ON ( Groups_2.Domain = 'RT::Ticket-Role' ) AND ( Groups_2.Instance = main.id ) JOIN Queues Queues_1  ON ( Queues_1.id = main.Queue ) LEFT JOIN CachedGroupMembers CachedGroupMembers_3  ON ( CachedGroupMembers_3.Disabled = '0' ) AND ( CachedGroupMembers_3.MemberId IN ('38', '837', '977', '950', '137720', '918', '48484', '903', '928', '976', '815', '952', '961', '962', '959', '963', '960', '958', '964', '965', '967', '966', '913', '927', '29', '1280', '802', '984', '128118', '796', '978', '981', '1016', '979', '51375', '1021', '16333', '1084', '83712', '45876', '126694', '83909', '26870', '18429', '137263', '992', '22500', '19948', '39078', '113663', '1415', '22466', '120659', '120715', '120658', '980', '122616', '985', '973', '949', '801', '1083', '987', '58468') ) AND ( CachedGroupMembers_3.GroupId = Groups_2.id )  WHERE ( ( main.Queue IN ('1', '15', '16', '18', '19', '20') OR  ( CachedGroupMembers_3.MemberId IS NOT NULL AND LOWER(Groups_2.Name) IN ('cc', 'requestor') )  OR  ( main.Owner = '38' )  ) ) AND (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND (main.Owner = '6' AND  (  ( Queues_1.Lifecycle = 'default' AND main.Status IN ('new', 'open', 'stalled') )  OR  ( Queues_1.Lifecycle = 'approvals' AND main.Status IN ('new', 'open', 'stalled') )  ) )  ORDER BY main.Created DESC ) main LIMIT 50

Spot the error? I didn’t at first. But it’s putting the ORDER BY clause in the wrong spot. Instead of ) ORDER BY main.Created DESC ) main LIMIT 50, it should read ) ) main ORDER BY main.Created DESC LIMIT 50

My conclusion so far is that there is a bug in how the query is generated. Now to dive into the code and figure out exactly where that statement gets generated.

EDIT: Setting Set($UseSQLForACLChecks, 0) also fixes the sort issue, though the documentation says that will cause other bugs for wrong ticket counts. But seems to confirm that it is a bug with how the SQL statement is being structured.

EDIT2: Since it may be relevant for how the DBIx module handles things, I’m using MariaDB 10.3 on Ubuntu 20.04.6.

Well, several hours of trying to understand exactly how queries are generated has left me with little but a headache.

Hopefully someone who actually understand show SearchBuilder works can take a look and see if this is an actual bug?

Might be worth putting a ticket into the Best Practical issue tracker RT instance so that it gets a case ID and the BP developers are aware of it.