Issues have invalid component UUID

[andrewm-aero (Andrew Melnick)]

Hello,

I am currently seeing an odd issue where in some projects (but not all) filtering for issues with “Resolution” set to “Unresolved” or “Status” set to “Open”, or “Issues in new code” selected, results in a forever spinning loading icon with “0 of X shown [Show More]” below it. The projects in question are not new, and have existed for several months, and did not exhibit this behavior until last week. No database or Sonarqube upgrades or restores took place between first analysis and this behavior first occurring, nor were any other manual changes to the database made.

This is in SonarQube Community LTS 9.9.5 (Helm Chart 8.0.5) with PostgreSQL 13.9.

Re-analyzing the same project does not have any effect, neither does removing the data/es7 directory and restarting Sonarqube, nor running a REINDEX on the database. The issue first appeared on 9.9.1 and upgrading to 9.9.5 did not resolve the issue.

After setting up TRACE level logs, capturing and investigating with manual SQL queries, the problem seems to be that the issues.component_uuid field of the affected issues do not point to any valid components.uuid, though issues.project_uuid is set correctly.

After investigating with additional queries, the problem seems to be latently present on hundreds of projects (around a third of all projects).

The issues in question are from built-in profiles as well as third-party plugins.

1. What could have caused this to happen?
2. How do I prevent this from happening again?
3. How do I resolve these “phantom” issues (as they are causing gate failures)? From a little investigation, it seems that “components” roughly correspond to files in a project, which suggests to me that these issues should have been closed when those files were removed. Is it sufficient and safe to find any such issues and manually set them to status=CLOSED, resolution=REMOVED via a manual sql UPDATE, or just outright deleting them? Deleting and re-creating all affected projects is out of the question due to the number of projects involved.
4. If nothing else, can a bug report be filed for the inconsistency between the reported and actual issue counts?

[Lukasz_Jarocki (Łukasz Jarocki)]

Warm welcome @andrewm-aero

Thank you for this report and detailed description of it.

Before you proceed to running some SQL updates on your database I think it would be worth to investigate this case a little bit more. We haven’t had any similar reports before so in order for me to better understand the issue I would need to ask you a few questions:

1. The spinning loading icon in the frontend is likely caused by one of the API calls to server either failing or taking a lot of time. Could you open the developer tools while this is happening and let us know which API call is problematic (by this I mean it doesn’t return HTTP 2xx in a reasonable time) ?
2. How did you come to this conclusion? Was there any specific log that caught your interest? If so, could you share it?

After setting up TRACE level logs, capturing and investigating with manual SQL queries, the problem seems to be that the issues.component_uuid field of the affected issues do not point to any valid components.uuid

3. Given the above, could you run the following query? It should return the number of projects (or rather branches) that are affected by the issue you describe:
   select count(distinct project_uuid) from issues i left join components c on c.uuid = i.component_uuid where c.uuid is null

When it comes to your specific questions:

1. What could have caused this to happen?
2. How do I prevent this from happening again?

Lets hope we can find out. For now it is difficult to say.

3. How do I resolve these “phantom” issues […]

This could be some idea if we don’t find anything better. Make sure that before you test any manual updates you backup your database.

4. If nothing else, can a bug report be filed for the inconsistency between the reported and actual issue counts?

For now this bug report would be premature as we don’t have enough information and similar reports.

[andrewm-aero (Andrew Melnick)]

Thank you for your prompt response.

let us know which API call is problematic

An example of the misbehaving API call is https://sonarqube.<redacted>/api/issues/search?componentKeys=<redacted>&s=FILE_LINE&inNewCodePeriod=true&types=BUG&ps=100&facets=severities%2Ctypes%2Cresolutions&additionalFields=_all&timeZone=America%2FDenver

And yes, the endpoint returns 200 within a few ms.

with the output being

{"total":1,"p":1,"ps":100,"paging":{"pageIndex":1,"pageSize":100,"total":1},"effortTotal":5,"issues":[],"components":[],"rules":[],"users":[{"login":"<redacted>","name":"<redacted>","avatar":"<redacted>","active":true}],"languages":[{"key":"neutral","name":"Neutral"},{"key":"cxx","name":"CXX"},{"key":"py","name":"Python"},{"key":"perl","name":"Perl"},{"key":"go","name":"Go"},{"key":"kotlin","name":"Kotlin"},{"key":"terraform","name":"Terraform"},{"key":"cloudformation","name":"CloudFormation"},{"key":"kubernetes","name":"Kubernetes"},{"key":"docker","name":"Docker"},{"key":"js","name":"JavaScript"},{"key":"ts","name":"TypeScript"},{"key":"css","name":"CSS"},{"key":"ruby","name":"Ruby"},{"key":"scala","name":"Scala"},{"key":"cs","name":"C#"},{"key":"java","name":"Java"},{"key":"web","name":"HTML"},{"key":"jsp","name":"JSP"},{"key":"flex","name":"Flex"},{"key":"xml","name":"XML"},{"key":"php","name":"PHP"},{"key":"text","name":"Text"},{"key":"secrets","name":"Secrets"},{"key":"vbnet","name":"VB.NET"},{"key":"yaml","name":"YAML"},{"key":"json","name":"JSON"}],"facets":[{"property":"severities","values":[{"val":"MAJOR","count":1},{"val":"INFO","count":0},{"val":"MINOR","count":0},{"val":"CRITICAL","count":0},{"val":"BLOCKER","count":0}]},{"property":"resolutions","values":[{"val":"","count":1},{"val":"FALSE-POSITIVE","count":0},{"val":"WONTFIX","count":0},{"val":"FIXED","count":0},{"val":"REMOVED","count":0}]},{"property":"types","values":[{"val":"CODE_SMELL","count":69},{"val":"BUG","count":1},{"val":"VULNERABILITY","count":0}]}]}

This is what is shown in the UI, in perpetuity after the API call succeeds.

I suspect that all of the issue searches are actually misbehaving for the affected projects, since the problem is the mismatch between reported and actual issue count, and the projects in question have thousands of issues, but I can’t prove this as I haven’t sat around clicking the “shore more” button without filters to see if the counts match up.

How did you come to this conclusion?

I first executed the query that was output by the TRACE logs, which returned zero rows.

I then executed nearly the same query that you posted below (just with a where not exists ( select ... ) to do the anti-join), which also returned zero rows.

Then, I executed

select distinct i.component_uuid
from issues i
inner join projects p on p.uuid=i.project_uuid
where p.kee='<redated project key from above>'
;

To find all of the component_uuids in that project, then executed select c.kee from components c where c.uuid='...' on each one to santiy check that my queries were doing what I thought they were, and some of them indeed returned zero rows.

I then returned to the anti-join query, but limited just the first few projects

select p.kee, i.kee, i.component_uuid, c.uuid
from issues i
inner join projects p on p.uuid=i.project_uuid
left join components c on c.uuid=i.component_uuid
where
    c.uuid is null
    and p.uuid in ( select uuid from projects limit 5 )
;

And this indeed returned rows with all nulls in the final column.

While I know my way around a join or two, this smells to me like some sort of mis-behaving optimization, and is beyond the edge of my SQL knowledge.

Make sure that before you test any manual updates you backup your database.

Definitely, no cowboy coding is going to take place here. I was more so hoping for a weak thumbs up/down as to whether or not this would even be worth trying or if there was something else known that it would break.

[Lukasz_Jarocki (Łukasz Jarocki)]

Hi,

Thanks for providing the details of your investigation. Unfortunately, I was not able to come up with any plausible explanation for this problem. To my best knowledge we never had anyone reporting missing component_uuids on issues table.

It seems to me that you have a few options:

• if you have some recent backup of a database from before this problem started appearing you could manually copy component_uuids from the backup to current database (I mean to issues table). This could be some work to do but it seems like the safest option if the issues that have missing component uuid are not new.
• You can try what you suggested to close these issues manually with the manual queries on the database, although I believe there can still be some side effects. The fact is we should never have null values in the component_uuid column so the SonarQube often works under assumption that there will be a value there. You might not entirely fix the issue.
• If the number of the rows affected in the issues table is low (or rather acceptable by you) then I think the best course of action would be to simply drop the rows in the issues table that don’t have component_uuid. After this action it would be recommended to keep an eye on issues table in the next days to quickly react and narrow down the issue in case more rows would be affected again. Dropping of course is not great but on the next analysis the issues should appear in SonarQube again.

For us the learning from this is that it would be beneficial to have NOT NULL constraint on the component_uuid column in the database to prevent these kind of issues from happening. We will discuss internally whether we can introduce it.