Different filter owner is shown in UI compared to API and database?

[Janek Schumann]

Dear Community,

I am scratching my head about something:
I am looking for all filters to find outdated onces. I tried 2 approaches:

1. SQL (table searchrequest)
2. Java API (via SearchRequestManager)

The issue I am facing:
Both methods return for filter A an author (aka owner, right?) B.
BUT: the Jira UI lists the same filter A with an owner of C! (confirmed by matching filter ID, filter name, and JQL)

Given a SearchRequest ID, how do I get the actual / current / "correct" owner of that filter?

Is SearchRequest the wrong DB table / API to look for filters? I used the the DB Schema of Jira DC 9 from Atlassian's documentation (https://dac-static.atlassian.com/server/jira/platform/attachments/jira_9.0_database_schema.pdf), since we are running Jira DC 9.12.15.


Thx,
Janek

---

for reference:
Java / Groovy:

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.search.SearchRequest
import com.atlassian.jira.issue.search.SearchRequestEntity
import com.atlassian.jira.issue.search.SearchRequestManager
import com.atlassian.jira.util.Visitor
import org.apache.log4j.Level

log.setLevel(Level.INFO);

ComponentAccessor.getComponent(SearchRequestManager).visitAll(new Visitor<SearchRequestEntity>() {
    @Override
    void visit(SearchRequestEntity searchRequestEntity) {
        if (searchRequestEntity.getId() != 16959)
            return;

        log.info("""
            Filter ID: ${searchRequestEntity.getId()}
            Author:    ${searchRequestEntity.getAuthor()}
            JQL:       ${searchRequestEntity.getRequest()}
        """);
    }
});

result:

Filter ID: 16959
Author: B
JQL: ...

MS SQL:

SELECT
  filter.ID         as filter_ID,
  filter.username   as filter_UserName,
  filter.authorname as filter_AuthorName
FROM
  jiraschema.searchrequest filter
WHERE
  filter.ID = 16959

result:

Filter ID: 16959
User: B
Author: B

UI:

[Radek Dostál · Accepted solution]

DB-wise, you should get the owner's key, are you sure it's not just the difference between the key and name?

Could use some examples what specifically you're seeing.

SearchRequest is the correct class and #getOwner() should get you the author/owner. To note, there is a cache so you could be seeing outdated/cached searchrequest whereas the db will always be the source of truth.

Visitor<SearchRequestEntity> uses #getAuthor() (=String)

SearchRequest uses #getOwner() (=ApplicationUser)

As far as I know, both are the same thing. Not sure whether "author" returns username or userkey though without digging into it some more.

Hindsight edit: author and owner are not the same, as explained in the discussion below

[Janek Schumann]

just added my examples. :)

Let's say the user key in the filter is "rdostal". (😉)
The UI shows it as "jschumann".
So it's not even remotely close.

Let me check the keys real quick to confirm. You never know. This system is 20 years in the making... and stuff went wrong with almost every upgrade since Jira 4. 😬😅

[Janek Schumann]

huh, this is interessting:
User B neither exist in the table cwd_user nor in table app_user!

could it be, that the UI is taking "the closest match" instead of the real deal?
If the user does not exist anymore, then the result is ... bad?

I wonder.

There is no user index, as far as I know, is there?
Are filters indexed in Lucene and the index is outdated? 🤔🤨

weird....

---

SQL for looking for the user keys in app_user (similar for cwd_user):

SELECT TOP 10
    app_user.*

FROM
    jiraschema.app_user
WHERE
    app_user.user_key        = 'B' OR
    app_user.lower_user_name = 'B' OR
    app_user.user_key        = 'C' OR
    app_user.lower_user_name = 'C'

result:

only C is returned. No entry for B.

[Janek Schumann]

Regarding the cache:
SQL directly on the DB and SearchRequest show the same result. Only the UI is different.

[Radek Dostál]

Uh, weird.

So db gives you user B, which does not exist, UI gives user C, which does exist, but shouldn't be the owner.

I'd say you're pulling my leg but nothing is surprising anymore.. old Jiras are kinky

And now I've confused myself. I don't know for sure the difference between author and username. If I had to guess, author would be.. who created the filter. And username would be.. current owner? Eh. I didn't realize there are two columns for this earlier.

And you've got user B for both author/username from the DB. Meaning this doesn't help, since UI still shows user C.

But you said user B does not exist in app_user. This shouldn't be the case - my understanding is that every filter must have an owner. This instance I checked is also pretty ancient, and we for sure deleted users over time.

Well now I'm beginning to think I don't get it. Can you run the last 2 queries to see if you also get 0s? If so, your previous check must have been flawed. If there are results, I guess those filters should just be straight up broken or weird, but at least we'd confirm in another way and see how many are potentially weird.

Are there any other filters like this? Would it make sense to switch the db values to user C and call it a day - mystery for another day, 10 years from now?

edit: code blocks here are straight up broken so here's a picture instead

[Janek Schumann]

ha ha ha... I found the issue:
my statement with "the user does not exist" is ... only half true!

if you look at my SQL, I am searching for 2 user names in user_key and lower_user_name both.
I expected 2 results and yet only got 1. --> "user does not exist"

BUT: at a closer look, the result is this:

ID (numeric)	user_key (nvarchar)	lower_user_name (nvarchar)
...	B	C

so the UI is correct since the DB schema tells me:
join app_user.lower_user_name with cwd_user.lower_user_name

which in turn will "correctly" return user details of C.

I read about this somewhere, that with the introduction of user rename feature in Jira ... 8? ... this information might have gotten inconsistent on an upgrade. There was a bug.
Never seen this in action. 🤔🤨

[Janek Schumann]

New check:
as admin, I took ownership of the filter.

result:

AuthorName: my admin acount user
UserName: B

--> Author ~ "who owns it"
--> UserName ~ "who created it"

thank you Atlassian for that. Absolutely logical. NOT. 😂

[Janek Schumann]

I found the culprit! Oh man... I feel stupid for not seeing it sooner!

user B and user C are in fact one and the same user!

Here is what happened:

1. the Jira instance is old. Almost 20 years old --> was migrated from Jira 4 to 9 over the years
2. the user is with us for 13 years --> user was created prior to Jira 8.2 where "JIRAUSER"-keys were introduced
3. THE USER MARRIED! --> user_key is the username prior to the marriage, lower_user_name is the username which got changed after the marriage

which means:

• table searchrequests uses user_key, not username!
• table app_user maps between the old world user_key to the current Crowd username in tabel cwd_user

jesus...
... 4 hours of my life just went puff for this. 🤣