SonarLint for Eclipse (v:4.0.0.2018.10170711) fails to bind to local SonarQube

sonarlint

(Paul Wagland) #1

Template for a good bug report, formatted with Markdown:

  • Versions used:
  • SonarLint: 4.0.0.2018.10170711
  • SonarQube: 7.1
  • error observed:
    After binding: you get the following error popup:
'Update SonarLint binding data for project <project> on <server>' has encountered a problem.

Unable to update SonarLint binding data for project '<project>' on '<server>'

Details:
Unable to update SonarLint binding data for project '<project>' on '<server>'
Fail to request https://<server>/api/components/tree.protobuf?qualifiers=FIL&component=<project>&ps=500&p=1

And later, we get the error:

'Check for updates of binding data on SonarQube/SonarCloud' has encountered a problem.

An internal error occurred during: "Check for updates of binding data on SonarQube/SonarCloud".

Details:
An internal error occurred during: "Check for updates of binding data on SonarQube/SonarCloud".
No data stored for project '<project>' or invalid format. Please update the binding.

Sonarlint console output:

Starting SonarLint for Eclipse 4.0.0.201810170711
Trigger: STARTUP
Error during execution of SonarLint analysis
org.sonarsource.sonarlint.core.client.api.exceptions.StorageException: Failed to read file: /Users/<user>/work/src/main-workspace/.sonarlint/storage/<server>/projects/<project>/configuration.pb
	at org.sonarsource.sonarlint.core.container.storage.ProtobufUtil.readFile(ProtobufUtil.java:46)
	at org.sonarsource.sonarlint.core.container.storage.StorageReader.readProjectConfig(StorageReader.java:98)
	at org.sonarsource.sonarlint.core.container.storage.StorageFileExclusions.getExcludedFiles(StorageFileExclusions.java:47)
	at org.sonarsource.sonarlint.core.container.storage.StorageContainerHandler.getExcludedFiles(StorageContainerHandler.java:110)
	at org.sonarsource.sonarlint.core.ConnectedSonarLintEngineImpl.lambda$getExcludedFiles$12(ConnectedSonarLintEngineImpl.java:231)
	at org.sonarsource.sonarlint.core.ConnectedSonarLintEngineImpl.withReadLock(ConnectedSonarLintEngineImpl.java:344)
	at org.sonarsource.sonarlint.core.ConnectedSonarLintEngineImpl.withReadLock(ConnectedSonarLintEngineImpl.java:334)
	at org.sonarsource.sonarlint.core.ConnectedSonarLintEngineImpl.getExcludedFiles(ConnectedSonarLintEngineImpl.java:231)
	at org.sonarlint.eclipse.core.internal.server.Server.getServerFileExclusions(Server.java:526)
	at org.sonarlint.eclipse.core.internal.utils.FileExclusionsChecker.lambda$2(FileExclusionsChecker.java:72)
	at java.base/java.util.Optional.ifPresent(Optional.java:183)
	at org.sonarlint.eclipse.core.internal.utils.FileExclusionsChecker.filterExcludedFiles(FileExclusionsChecker.java:70)
	at org.sonarlint.eclipse.core.internal.utils.FileExclusionsChecker.isExcluded(FileExclusionsChecker.java:84)
	at org.sonarlint.eclipse.core.internal.jobs.AbstractAnalyzeProjectJob.lambda$0(AbstractAnalyzeProjectJob.java:132)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1540)
	at org.sonarlint.eclipse.core.internal.jobs.AbstractAnalyzeProjectJob.doRun(AbstractAnalyzeProjectJob.java:131)
	at org.sonarlint.eclipse.core.internal.jobs.AbstractSonarProjectJob.runInWorkspace(AbstractSonarProjectJob.java:44)
	at org.eclipse.core.internal.resources.InternalWorkspaceJob.run(InternalWorkspaceJob.java:42)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.nio.file.NoSuchFileException: /Users/<user>/work/src/main-workspace/.sonarlint/storage/<server>/projects/<project>/configuration.pb
	at java.base/sun.nio.fs.UnixException.translateToIOException(UnixException.java:92)
	at java.base/sun.nio.fs.UnixException.rethrowAsIOException(UnixException.java:111)
	at java.base/sun.nio.fs.UnixException.rethrowAsIOException(UnixException.java:116)
	at java.base/sun.nio.fs.UnixFileSystemProvider.newByteChannel(UnixFileSystemProvider.java:215)
	at java.base/java.nio.file.Files.newByteChannel(Files.java:370)
	at java.base/java.nio.file.Files.newByteChannel(Files.java:421)
	at java.base/java.nio.file.spi.FileSystemProvider.newInputStream(FileSystemProvider.java:420)
	at java.base/java.nio.file.Files.newInputStream(Files.java:155)
	at org.sonarsource.sonarlint.core.container.storage.ProtobufUtil.readFile(ProtobufUtil.java:43)
	... 18 more

Check for updates from server 'sonarqube.dev.rwd.lan'
Check for binding data updates on 'sonarqube.dev.rwd.lan' for project 'Cronacle'
Trigger: EDITOR_OPEN
Error during execution of SonarLint analysis
org.sonarsource.sonarlint.core.client.api.exceptions.StorageException: Failed to read file: /Users/<user>/work/src/main-workspace/.sonarlint/storage/<server>/projects/<project>/configuration.pb
	at org.sonarsource.sonarlint.core.container.storage.ProtobufUtil.readFile(ProtobufUtil.java:46)
	at org.sonarsource.sonarlint.core.container.storage.StorageReader.readProjectConfig(StorageReader.java:98)
	at org.sonarsource.sonarlint.core.container.storage.StorageFileExclusions.getExcludedFiles(StorageFileExclusions.java:47)
	at org.sonarsource.sonarlint.core.container.storage.StorageContainerHandler.getExcludedFiles(StorageContainerHandler.java:110)
	at org.sonarsource.sonarlint.core.ConnectedSonarLintEngineImpl.lambda$getExcludedFiles$12(ConnectedSonarLintEngineImpl.java:231)
	at org.sonarsource.sonarlint.core.ConnectedSonarLintEngineImpl.withReadLock(ConnectedSonarLintEngineImpl.java:344)
	at org.sonarsource.sonarlint.core.ConnectedSonarLintEngineImpl.withReadLock(ConnectedSonarLintEngineImpl.java:334)
	at org.sonarsource.sonarlint.core.ConnectedSonarLintEngineImpl.getExcludedFiles(ConnectedSonarLintEngineImpl.java:231)
	at org.sonarlint.eclipse.core.internal.server.Server.getServerFileExclusions(Server.java:526)
	at org.sonarlint.eclipse.core.internal.utils.FileExclusionsChecker.lambda$2(FileExclusionsChecker.java:72)
	at java.base/java.util.Optional.ifPresent(Optional.java:183)
	at org.sonarlint.eclipse.core.internal.utils.FileExclusionsChecker.filterExcludedFiles(FileExclusionsChecker.java:70)
	at org.sonarlint.eclipse.core.internal.utils.FileExclusionsChecker.isExcluded(FileExclusionsChecker.java:84)
	at org.sonarlint.eclipse.core.internal.jobs.AbstractAnalyzeProjectJob.lambda$0(AbstractAnalyzeProjectJob.java:132)
	at java.base/java.util.Arrays$ArrayList.forEach(Arrays.java:4390)
	at org.sonarlint.eclipse.core.internal.jobs.AbstractAnalyzeProjectJob.doRun(AbstractAnalyzeProjectJob.java:131)
	at org.sonarlint.eclipse.core.internal.jobs.AbstractSonarProjectJob.runInWorkspace(AbstractSonarProjectJob.java:44)
	at org.eclipse.core.internal.resources.InternalWorkspaceJob.run(InternalWorkspaceJob.java:42)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.nio.file.NoSuchFileException: /Users/<user>/work/src/main-workspace/.sonarlint/storage/<server>/projects/<project>/configuration.pb
	at java.base/sun.nio.fs.UnixException.translateToIOException(UnixException.java:92)
	at java.base/sun.nio.fs.UnixException.rethrowAsIOException(UnixException.java:111)
	at java.base/sun.nio.fs.UnixException.rethrowAsIOException(UnixException.java:116)
	at java.base/sun.nio.fs.UnixFileSystemProvider.newByteChannel(UnixFileSystemProvider.java:215)
	at java.base/java.nio.file.Files.newByteChannel(Files.java:370)
	at java.base/java.nio.file.Files.newByteChannel(Files.java:421)
	at java.base/java.nio.file.spi.FileSystemProvider.newInputStream(FileSystemProvider.java:420)
	at java.base/java.nio.file.Files.newInputStream(Files.java:155)
	at org.sonarsource.sonarlint.core.container.storage.ProtobufUtil.readFile(ProtobufUtil.java:43)
	... 18 more
  • Reproduction: Bind a project
  • No known workaround

Version 3.6.0.201806071228 of SonarLint works fine.


(Paul Wagland) #2

We upgraded to SonarQube 7.4 in the hope that this would fix the issue, however we still have exactly the same problem.

Is there any additional information that we can give to help find a resolution for this issue? I have tried both token and username/password, and they both fail in exactly the same way.


(Julien Henry) #4

Hi @pwagland

We need more details to understand the error:

Fail to request https://<server>/api/components/tree.protobuf?qualifiers=FIL&component=<project>&ps=500&p=1

Could you please look at Eclipse error logs to get the stacktrace? You can also have a look at your SonarQube server logs just in case there is a server side error (look in access.log for an HTTP 500 error).


(Paul Wagland) #5

Here are the event details from the error log:

eclipse.buildId=4.9.0.I20180906-0745
java.version=11.0.1
java.vendor=Oracle Corporation
BootLoader constants: OS=macosx, ARCH=x86_64, WS=cocoa, NL=en_IE
Framework arguments: -keyring /<homedir>/.eclipse_keyring
Command-line arguments: -os macosx -ws cocoa -arch x86_64 -keyring /<homedir>/.eclipse_keyring

org.sonarlint.eclipse.core
Error
Tue Jan 08 22:43:54 CET 2019
Unable to update SonarLint binding data for project ‘<project>’ on ‘<server>’

java.lang.IllegalStateException: Fail to request https://<server>/api/components/tree.protobuf?qualifiers=FIL&component=<project>&ps=500&p=1
	at org.sonarsource.sonarlint.core.util.ws.HttpConnector.doCall(HttpConnector.java:196)
	at org.sonarsource.sonarlint.core.util.ws.HttpConnector.get(HttpConnector.java:122)
	at org.sonarsource.sonarlint.core.util.ws.HttpConnector.call(HttpConnector.java:109)
	at org.sonarsource.sonarlint.core.container.connected.SonarLintWsClient.rawGet(SonarLintWsClient.java:116)
	at org.sonarsource.sonarlint.core.container.connected.SonarLintWsClient.get(SonarLintWsClient.java:81)
	at org.sonarsource.sonarlint.core.container.connected.SonarLintWsClient.getPaginated(SonarLintWsClient.java:188)
	at org.sonarsource.sonarlint.core.container.connected.update.ProjectFileListDownloader.get(ProjectFileListDownloader.java:42)
	at org.sonarsource.sonarlint.core.container.connected.update.perform.ProjectStorageUpdateExecutor.updateComponents(ProjectStorageUpdateExecutor.java:93)
	at org.sonarsource.sonarlint.core.container.connected.update.perform.ProjectStorageUpdateExecutor.lambda$update$0(ProjectStorageUpdateExecutor.java:72)
	at org.sonarsource.sonarlint.core.client.api.util.FileUtils.replaceDir(FileUtils.java:171)
	at org.sonarsource.sonarlint.core.container.connected.update.perform.ProjectStorageUpdateExecutor.update(ProjectStorageUpdateExecutor.java:69)
	at org.sonarsource.sonarlint.core.container.connected.ConnectedContainer.updateProject(ConnectedContainer.java:123)
	at org.sonarsource.sonarlint.core.ConnectedSonarLintEngineImpl.updateProject(ConnectedSonarLintEngineImpl.java:266)
	at org.sonarlint.eclipse.core.internal.server.Server.updateProjectStorage(Server.java:402)
	at org.sonarlint.eclipse.core.internal.jobs.ProjectStorageUpdateJob.lambda$0(ProjectStorageUpdateJob.java:45)
	at java.base/java.util.Optional.ifPresent(Optional.java:183)
	at org.sonarlint.eclipse.core.internal.jobs.ProjectStorageUpdateJob.run(ProjectStorageUpdateJob.java:45)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.net.SocketTimeoutException: timeout
	at org.sonarsource.sonarlint.shaded.okhttp3.internal.http2.Http2Stream$StreamTimeout.newTimeoutException(Http2Stream.java:616)
	at org.sonarsource.sonarlint.shaded.okhttp3.internal.http2.Http2Stream$StreamTimeout.exitAndThrowIfTimedOut(Http2Stream.java:624)
	at org.sonarsource.sonarlint.shaded.okhttp3.internal.http2.Http2Stream.takeResponseHeaders(Http2Stream.java:146)
	at org.sonarsource.sonarlint.shaded.okhttp3.internal.http2.Http2Codec.readResponseHeaders(Http2Codec.java:127)
	at org.sonarsource.sonarlint.shaded.okhttp3.internal.http.CallServerInterceptor.intercept(CallServerInterceptor.java:88)
	at org.sonarsource.sonarlint.shaded.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:147)
	at org.sonarsource.sonarlint.shaded.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:121)
	at org.sonarsource.sonarlint.core.util.ws.OkHttpClientBuilder.addHeaders(OkHttpClientBuilder.java:204)
	at org.sonarsource.sonarlint.shaded.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:147)
	at org.sonarsource.sonarlint.shaded.okhttp3.internal.connection.ConnectInterceptor.intercept(ConnectInterceptor.java:45)
	at org.sonarsource.sonarlint.shaded.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:147)
	at org.sonarsource.sonarlint.shaded.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:121)
	at org.sonarsource.sonarlint.shaded.okhttp3.internal.cache.CacheInterceptor.intercept(CacheInterceptor.java:93)
	at org.sonarsource.sonarlint.shaded.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:147)
	at org.sonarsource.sonarlint.shaded.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:121)
	at org.sonarsource.sonarlint.shaded.okhttp3.internal.http.BridgeInterceptor.intercept(BridgeInterceptor.java:93)
	at org.sonarsource.sonarlint.shaded.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:147)
	at org.sonarsource.sonarlint.shaded.okhttp3.internal.http.RetryAndFollowUpInterceptor.intercept(RetryAndFollowUpInterceptor.java:126)
	at org.sonarsource.sonarlint.shaded.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:147)
	at org.sonarsource.sonarlint.shaded.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:121)
	at org.sonarsource.sonarlint.shaded.okhttp3.RealCall.getResponseWithInterceptorChain(RealCall.java:200)
	at org.sonarsource.sonarlint.shaded.okhttp3.RealCall.execute(RealCall.java:77)
	at org.sonarsource.sonarlint.core.util.ws.HttpConnector.doCall(HttpConnector.java:194)
	... 17 more

I don’t have easy access to the server, but will follow up on the logs for that once I get feedback from our IT.


(Julien Henry) #6

Hi Paul,

Could you please try to open:
https://<server>/api/components/tree?qualifiers=FIL&component=<project>&ps=500&p=1
in your web browser and check:

  • the value of paging.total
  • the approximate time needed for returning the response

For example, trying https://sonarcloud.io/api/components/tree?qualifiers=FIL&component=android-kernel-htc-msm8974-travis&ps=500&p=1 returns 27089 files, but the response (of the 500 first results) is fast.


(Paul Wagland) #7

Hi Julien,

Well, this might be part of the problem :slight_smile:

Turns out that https://<server>/api/components/tree?qualifiers=FIL&component=<project>&ps=500&p=1 takes 597s to return.

The relevant part of the JSON file is:

{"paging": {"pageIndex":1,
            "pageSize":500,
            "total":19438}

It then takes 2.5ms to transfer the data, but I suspect that the problem is indeed that it takes 10 minutes to generate it. A second load is faster, it takes 418 seconds, but that is still too slow :smiley:

So the question now becomes, I guess, how can I make this query react faster? We have some monitoring on the database, and we see that it is at between 100-180% CPU for the entire time. A huge whack of memory is used on the SonarQube server, I am not sure about the CPU usage there, but I didn’t thin it was so high.

Cheers,
Paul


(Julien Henry) #8

Few random question/ideas:

  • can you share your DB vendor and precise version?
  • with some DB, it is possible to know the precise query that is taking so much time. Can you try to find it?
  • is your DB generally slow/low on memory when you browse the SQ UI? Or only this query make it under pressure?
  • I didn’t found anything related in the changelog of all recent SQ versions, but maybe upgrading from 7.1 to latest release could help. What would be great would be if you could have a test instance where you would reproduce the issue with 7.1, and then upgrade to latest and see if the problem is still there.

(Paul Wagland) #9

DB: PostgreSQL v10.6

The query that seems to be taking most of the time (slightly reformatted because of the copy to/from terminal):

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                                                                       
    inner join projects base on base.project_uuid = p.project_uuid and base.uuid = $1
    and p.uuid_path like $2 ESCAPE '/'                                                
    WHERE  p.enabled = true
    and p.qualifier in               
    (                                        
      $3                                   
    )

In general the UI is very responsive, even when selecting all issues so that 13K issues are shown.

We have already upgraded to 7.4 in December, but can upgrade to 7.5 if you believe that it will be better.

Cheers,
Paul


(Julien Henry) #10

No need to update to 7.5 just for this issue, I don’t think there is any change in the query.

Now we need to understand why this query is slow in your case. Can you provide the output of:

SELECT COUNT(1) FROM projects;
SELECT COUNT(1) FROM projects WHERE kee = <project>; // same <project> as when you call the WS from your browser
SELECT COUNT(1) FROM projects WHERE kee = <project> AND p.enabled = true AND p.qualifier = 'FIL'; // I expect 19438

(Paul Wagland) #11

results of the queries:

sonar=# SELECT COUNT(1) FROM projects; 
 count 
------- 
 25559 
(1 row) 


sonar=# SELECT COUNT(1) FROM projects WHERE kee = '<Project>'; 
 count 
------- 
     1 
(1 row) 


sonar=# SELECT COUNT(1) FROM projects WHERE kee = '<Project>' AND p.enabled = true AND p.qualifier = 'FIL'; 
ERROR:  missing FROM-clause entry for table "p" 
LINE 1: ...COUNT(1) FROM projects WHERE kee = '<Project>' AND p.enabled ... 

Does not work, each kee is unique:

sonar=# select count(1) from projects p where p.kee = '<Project>' and p.enabled = true and p.qualifier = 'FIL'; 
 count 
------- 
     0 
(1 row) 

We have a lot of projects with names like <Project>:<path to file>.

Note, despite only having one project in SonarQube, the list of projects in the database is very large:

sonar=# select count(distinct(kee)) from projects; 
 count 
------- 
 25559 
(1 row)

(Julien Henry) #12

Sorry, I did a mistake in requested queries. Could you please execute:

SELECT COUNT(1) FROM projects child, projects prj WHERE child.project_uuid = prj.uuid AND prj.kee = <project>; // same <project> as when you call the WS from your browser
SELECT COUNT(1) FROM projects child, projects prj WHERE child.project_uuid = prj.uuid AND prj.kee = <project> AND child.enabled = true AND child.qualifier = 'FIL'; // I expect 19438

(Paul Wagland) #13

No problems! Thanks for helping us track down this issue!

Here are the results from the updated queries:

sonar=# SELECT COUNT(1) FROM projects child, projects prj WHERE child.project_uuid = prj.uuid AND prj.kee = '<Project>';
 count
-------
 25559
(1 row)

sonar=# SELECT COUNT(1) FROM projects child, projects prj WHERE child.project_uuid = prj.uuid AND prj.kee = '<Project>' AND child.enabled = true AND child.qualifier = 'FIL';
 count
-------
 19438
(1 row)

(Paul Wagland) #14

Hi @Julien_HENRY,

Is there anything else that we can do to further diagnose what is going wrong here? We really want to be able to roll this out, but for various reasons, without IDE support, this is going to be very difficult to gain traction.

Cheers,
Paul


(Julien Henry) #15

Hi @pwagland, sorry I missed your reply.

My last request would be that you try to execute the slow query, and see if this is better without the ‘like’:

First get your project uuid:

SELECT uuid FROM projects WHERE kee = <project>;

Then run the slow query, and confirm it is slow:

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                                                                       
    inner join projects base on base.project_uuid = p.project_uuid and base.uuid = '<project uuid>'
    and p.uuid_path like '<project uuid>' ESCAPE '/'                                                
    WHERE  p.enabled = true
    and p.qualifier in               
    (                                        
      'FIL'                                   
    )

Finally run the same query without the ‘like’:

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                                                                       
    inner join projects base on base.project_uuid = p.project_uuid and base.uuid = '<project uuid>'
    WHERE  p.enabled = true
    and p.qualifier in               
    (                                        
      'FIL'                                   
    )

Can you confirm it is faster?


(Paul Wagland) #16

Results of the queries:

sonar=# \timing on 
Timing is on. 

# confirming i have got the correct uuid 
sonar=# SELECT p.uuid, p.kee FROM projects p WHERE p.uuid = 'AVboQF94S0sJDuKA7Qe3' and p.kee = ''<project>'' 
; 
         uuid         |   kee     
----------------------+---------- 
 AVboQF94S0sJDuKA7Qe3 | <project> 
(1 row) 

Time: 0.731 ms 

# First query replaced '<project uuid>' with 'AVboQF94S0sJDuKA7Qe3' 
sonar-# ; 
 id | organizationuuid | uuid | uuidpath | projectuuid | moduleuuid | moduleuuidpath | mainbranchprojectuuid | kee | deprecatedkey | name | longname | description | tagsstring | qualifier | scope | language | rootuuid | path | enabled  
| copycomponentuuid | isprivate | createdat  
----+------------------+------+----------+-------------+------------+----------------+-----------------------+-----+---------------+------+----------+-------------+------------+-----------+-------+----------+----------+------+--------- 
+-------------------+-----------+----------- 
(0 rows) 

Time: 22.146 ms

sonar=# select                                                   
sonar-#     p.id,                                                   
sonar-#     p.organization_uuid as organizationUuid, 
sonar-#     p.uuid as uuid,                           
sonar-#     p.uuid_path as uuidPath,           
sonar-#     p.project_uuid as projectUuid,   
sonar-#     p.module_uuid as moduleUuid,                                                           
sonar-#     p.module_uuid_path as moduleUuidPath,                                                  
sonar-#     p.main_branch_project_uuid as mainBranchProjectUuid,                                   
sonar-#     p.kee as kee,                                                                          
sonar-#     p.deprecated_kee as deprecatedKey,                                                     
sonar-#     p.name as name,                                                                       
sonar-#     p.long_name as longName,                                                               
sonar-#     p.description as description,                                                          
sonar-#     p.tags as tagsString,                                                                  
sonar-#     p.qualifier as qualifier,                                                              
sonar-#     p.scope as scope,                                                                      
sonar-#     p.language as language,                                                                
sonar-#     p.root_uuid as rootUuid,                                                               
sonar-#     p.path as path,                                                                        
sonar-#     p.enabled as enabled,                                                                  
sonar-#     p.copy_component_uuid as copyComponentUuid,                                            
sonar-#     p.private as isPrivate,                                                                
sonar-#     p.created_at as createdAt                                                              
sonar-#     from projects p                                                                        
sonar-#     inner join projects base on base.project_uuid = p.project_uuid and base.uuid = 'AVboQF94S0sJDuKA7Qe3' 
sonar-#     and p.uuid_path like 'AVboQF94S0sJDuKA7Qe3%' ESCAPE '/'                                                 
sonar-#     WHERE  p.enabled = true 
sonar-#     and p.qualifier in                
sonar-#     (                                         
sonar(#       'FIL'                                    
sonar(#     ); 
 id | organizationuuid | uuid | uuidpath | projectuuid | moduleuuid | moduleuuidpath | mainbranchprojectuuid | kee | deprecatedkey | name | longname | description | tagsstring | qualifier | scope | language | rootuuid | path | enabled 
| copycomponentuuid | isprivate | createdat 
----+------------------+------+----------+-------------+------------+----------------+-----------------------+-----+---------------+------+----------+-------------+------------+-----------+-------+----------+----------+------+--------- 
+-------------------+-----------+----------- 
(0 rows) 

Time: 31.109 ms 

sonar=# select
sonar-#     count(1)
sonar-#     from projects p
sonar-#     inner join projects base on base.project_uuid = p.project_uuid and base.uuid = 'AVboQF94S0sJDuKA7Qe3'
sonar-#     WHERE  p.enabled = true
sonar-#     and p.qualifier in
sonar-#     (
sonar(#       'FIL'
sonar(#     );

19438

The second query took 5 minutes 7 seconds, we re-ran it with the count to see how many rows it returned.


(Julien Henry) #17

Thanks Paul,

I don’t really understand why the first query (with the ‘like’) doesn’t return any value. But that’s not very important. If my understanding is correct, when you say

The second query took 5 minutes 7 seconds

you are talking about the query without the ‘like’ right? So it means we can focus on this one.

There is an index on project_uuid and qualifier, so I’m a bit surprised it takes so long to iterate on “only” 19K entries.

New idea, could you check if you have a lot of disabled components, but running the last count query without the condition p.enabled = true ?


(Paul Wagland) #18

Right. So, after trying to get this to run in a reasonable period of time, I did what I should have done some time ago, I ran explain and explain analyze on the query. Then, just to make sure that the indexes were up to date, we also did vacuum analyse projects;, lo and behold, suddenly our queries, that used to take 5 minutes, take 20ms, so yeah, a factor of 1500x improvement. Suddenly SonarLint also stopped complaining! Which makes sense, since the call that it uses now returns in under a second instead of in about five minutes…

So, a new one to add to your, and our, checklist: On PostrgeSQL, ensure that vacuum analyze has been run!

Thanks again for all of your help! Now I can actually try and being the integration of the SonarLint into our process… there might be more questions coming out of the other side of that, but they will be different topics :slight_smile:


(Julien Henry) #19

Thanks for the follow up Paul. That’s really surprising how big difference it makes.

Looking at our documentation, running vacuum is mentioned as part of the upgrade process:
https://docs.sonarqube.org/latest/setup/upgrading/#header-2

so maybe you should run a vacuum full to clean all your tables?


(Paul Wagland) #20

Hi Julien,

Yes, turns out that this is part of our normal IT tasks, but that wasn’t enabled for this database for some reason. Oh well… solved now, and it will stay solved in the future with any luck :slight_smile:

Cheers,
Paul