[solved]Analysis error after upgrade

database
sonarqube

(Gerald) #1

Hi,

I’ve recently upgraded both Postgre and SonarQube.

  • Postgre is now 10.5 (was 9.6)
  • SonarQube is now 7.3 (was 7.2.1) (Community Edition)

At first it looked all good, but when our biggest and most important project was up to be analyzed, the background task for that failed (and keeps failing). I enabled the trace log and tried to figure out what’s wrong. In the CE log I found an exception:

    org.sonar.db.RowNotFoundException: Component with uuid 'AV8lhoe9pnaYwVjSy7j-' not found
	    at org.sonar.server.issue.notification.NewIssuesNotification.lambda$setComponentsStatistics$2(NewIssuesNotification.java:154)
    	at java.util.Optional.orElseThrow(Optional.java:290)
	    at org.sonar.server.issue.notification.NewIssuesNotification.setComponentsStatistics(NewIssuesNotification.java:154)
    ...

I think this is the relevant SQL statement that causes the error:
2018.08.21 08:00:11 TRACE ce[AWVbEA-wRzUqQ1jOCbMO][sql] time=0ms | sql=select p.id, p.organization_uuid as organizationUuid, p.uuid as uuid, p.uuid_path as uuidPath, p.project_uuid as projectUuid, p.module_uuid as moduleUuid, p.module_uuid_path as moduleUuidPath, p.main_branch_project_uuid as mainBranchProjectUuid, p.kee as kee, p.deprecated_kee as deprecatedKey, p.name as name, p.long_name as longName, p.description as description, p.tags as tagsString, p.qualifier as qualifier, p.scope as scope, p.language as language, p.root_uuid as rootUuid, p.path as path, p.enabled as enabled, p.copy_component_uuid as copyComponentUuid, p.private as isPrivate, p.created_at as createdAt from projects p where p.uuid in ( ? , ? , ? , ? , ? ) | params=AV8lhoe9pnaYwVjSy7j-, AV8lhofVpnaYwVjSy7r-, AV8lhofhpnaYwVjSy7v_, AV8lhofppnaYwVjSy7z-, AV8lhofxpnaYwVjSy73-

The issue is that any uuid with a trailing dash is not found. It is there in the database. I can select the according row by the following statement:
select id, uuid from projects where uuid like 'AV8lhoe9pnaYwVjSy7j-%'
but the statement
select id, uuid from projects where uuid='AV8lhoe9pnaYwVjSy7j-'
returns an empty result. I copied the value, I don’t see any mismatch.

This is why analyzing the project in Sonarqube fails. Is there something I have overlooked? Some setting in Postgre I have to set? Something I should have paid attention to when upgrading Postgre along with Sonarqube? The character encoding is still UTF8 and I can’t find any clues in neither the PostGre nor the SonarQube documentation.
Somebody got a similar problem and perhaps even a solution?

Thanks and cheers,
Gerald


(Sébastien Lesaint) #4

Hello,

What is the actual value of the UUID column returned by the first statement? Could there be a trailing empty space? Does the statement return only one row?


(Gerald) #5

Thanks for the reply, Sébastien.
The exact value is “AV8lhoe9pnaYwVjSy7j-”. I’ve double checked if there is any trailing white space. Also tried various forms of escaping and also noting the dash as unicode number.

 select id, uuid from projects where uuid like 'AV8lhoe9pnaYwVjSy7j-_%'

returns an empty result (this would not be the case when there were some trailing characters)

 select id, uuid from projects where uuid like 'AV8lhoe9pnaYwVjSy7j_'

returns several rows (the actually intended row is one of them)

 select id, uuid from projects where uuid like 'AV8lhoe9pnaYwVjSy7j-%'

returns only the intended row, no additional rows.

I have observed the same behavior with any other row that has a trailing ‘-’ in the uuid. uuids with a dash in the middle are not affected.
I’ve tried to reproduce the same behavior on a different database on the same postgre instance, i.e. created a varchar field and filled it with values with a trailing dash. This can be selected with the = operator without running into this problem.


(Sébastien Lesaint) #6

Nice detailed investigation, Gerald.

I’m quite puzzled by the situation and I think it comes down to something specific to Postgres (and not SonarQube).

I’m not an expert of Postgres in particular, but I got this totally wild idea: have you rebuilt your indices since you upgraded to Postgre 10?

Except for some specificity of equals operator on varchar with “-” character with Postgre 10 (of which I have found no trace of so far), the only explanation that comes to my mind for this difference in behavior of “=” compared to “like” is that the former is going to use the index on column UUID while the later isn’t and will do a full sequential scan of the table.

If the index is corrupted for any reason, then the result may differ.


(Gerald) #7

That would explain why it works on the other database with newly created rows, in a table with no index to top it off. And I must have had bad luck with my selection of samples to test against.

Indeed I just reindexed the Sonarqube database just now and retried. You have nailed it down!

Thank you very much! I’m quite happy it’s just that instead of some issue with SQ on Postgre 10, because I’d hate to downgrade :smiley: