SonarQube 5.6.7 unable to search for issues (ES error)

Question

I'm able to perform analysis, though this is running very slow. Now I'm facing even more problem with browsing through issues. Our infrastructure is not keeping up with Elastic Search requirements, SECCOMP is not compiled into kernel so I have this option added to sonar.properties sonar.search.javaAdditionalOpts=-Dbootstrap.system_call_filter=false

Also I have supplied crazy amount of memory,

sonar.search.javaOpts=-Xmx2G -Xms1G -Xss1m -Djava.net.preferIPv4Stack=true \
  -XX:+UseParNewGC -XX:+UseConcMarkSweepGC -XX:CMSInitiatingOccupancyFraction=75 \
  -XX:+UseCMSInitiatingOccupancyOnly -XX:+HeapDumpOnOutOfMemoryError

System 64-bit, 16 GM RAM and 2 cores; runs RHEL 6.7

And the ES breaks down with following log.

2018.03.01 14:32:34 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1285367970 [1.1gb] from field [key] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:09:43 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1285374084 [1.1gb] from field [key] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:10:04 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1285374084 [1.1gb] from field [key] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:20:15 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1285379720 [1.1gb] from field [key] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:20:47 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1285379720 [1.1gb] from field [key] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:21:30 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1285379720 [1.1gb] from field [key] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:21:39 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1287374978 [1.1gb] from field [key] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:22:02 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1287374978 [1.1gb] from field [key] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:22:12 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1279785543 [1.1gb] from field [issueCreatedAt] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:22:12 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1279798446 [1.1gb] from field [issueCreatedAt] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:22:12 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1279787446 [1.1gb] from field [issueCreatedAt] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:22:12 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1279771440 [1.1gb] from field [issueCreatedAt] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:22:12 WARN   es[o.e.indices.breaker]  [sonar-1519909671773] [FIELDDATA] New used memory 1279800560 [1.1gb] from field [issueCreatedAt] would be larger than configured breaker: 1278030643 [1.1gb], breaking
2018.03.01 15:22:13 ERROR web[o.s.s.w.WebServiceEngine] Fail to process request http://172.17.33.86:9091/sonar/api/issues/search?p=1&ps=50&s=FILE_LINE&asc=true&additionalFields=_all&facets=types%2Cresolutions%2CcreatedAt&resolved=false&types=VULNERABILITY&sinceLeakPeriod=true&componentUuids=AWHS0koMv8wI-iczVKXw
java.lang.IllegalStateException: Fail to execute ES search request '{"from":0,"size":50,"query":{"filtered":{"query":{"match_all":{}},"filter":{"bool":{"must":[{"terms":{"modulePath":["AWHS0koMv8wI-iczVKXw"]}},{"missing":{"field":"resolution"}},{"terms":{"type":["VULNERABILITY"]}},{"range":{"issueCreatedAt":{"from":"2018-02-19T19:14:08.456Z","to":null,"include_lower":true,"include_upper":true},"_cache":false}},{"has_parent":{"query":{"filtered":{"query":{"match_all":{}},"filter":{"bool":{"must":{"or":{"filters":[{"term":{"users":"admin"}},{"term":{"groups":"Anyone"}},{"term":{"groups":"sonar-users"}},{"term":{"groups":"sonar-administrators"}}]}},"_cache":true}}}},"parent_type":"authorization"}}]}}}},"sort":[{"project":{"order":"asc","missing":"_first"}},{"filePath":{"order":"asc","missing":"_first"}},{"line":{"order":"asc","missing":"_first"}},{"severityValue":{"order":"desc","missing":"_first"}},{"key":{"order":"asc","missing":"_first"}}],"aggregations":{"types":{"global":{},"aggregations":{"types_filter":{"filter":{"bool":{"must":[{"query":{"match_all":{}}},{"terms":{"modulePath":["AWHS0koMv8wI-iczVKXw"]}},{"missing":{"field":"resolution"}},{"range":{"issueCreatedAt":{"from":"2018-02-19T19:14:08.456Z","to":null,"include_lower":true,"include_upper":true},"_cache":false}},{"has_parent":{"query":{"filtered":{"query":{"match_all":{}},"filter":{"bool":{"must":{"or":{"filters":[{"term":{"users":"admin"}},{"term":{"groups":"Anyone"}},{"term":{"groups":"sonar-users"}},{"term":{"groups":"sonar-administrators"}}]}},"_cache":true}}}},"parent_type":"authorization"}}]}},"aggregations":{"types":{"terms":{"field":"type","size":10,"min_doc_count":1,"order":{"_count":"desc"}}},"types_selected":{"terms":{"field":"type","include":"VULNERABILITY"}}}}}},"resolutions":{"global":{},"aggregations":{"resolutions_filter":{"filter":{"bool":{"must":[{"query":{"match_all":{}}},{"terms":{"modulePath":["AWHS0koMv8wI-iczVKXw"]}},{"terms":{"type":["VULNERABILITY"]}},{"range":{"issueCreatedAt":{"from":"2018-02-19T19:14:08.456Z","to":null,"include_lower":true,"include_upper":true},"_cache":false}},{"has_parent":{"query":{"filtered":{"query":{"match_all":{}},"filter":{"bool":{"must":{"or":{"filters":[{"term":{"users":"admin"}},{"term":{"groups":"Anyone"}},{"term":{"groups":"sonar-users"}},{"term":{"groups":"sonar-administrators"}}]}},"_cache":true}}}},"parent_type":"authorization"}}]}},"aggregations":{"resolutions":{"terms":{"field":"resolution","size":15,"min_doc_count":1,"order":{"_count":"desc"}}},"resolutions_missing":{"missing":{"field":"resolution"}}}}}},"createdAt":{"date_histogram":{"field":"issueCreatedAt","interval":"1d","min_doc_count":0,"pre_zone":"GMT","offset":"-3600s","format":"yyyy-MM-dd'T'HH:mm:ssZ","extended_bounds":{"min":1519067648456,"max":1519914131788}}}}}' on indices '[issues]' on types '[issue]'
    at org.sonar.server.es.request.ProxySearchRequestBuilder.get(ProxySearchRequestBuilder.java:47) ~[sonar-server-5.6.7.jar:na]
    at org.sonar.server.es.request.ProxySearchRequestBuilder.get(ProxySearchRequestBuilder.java:35) ~[sonar-server-5.6.7.jar:na]
    at org.sonar.server.issue.index.IssueIndex.search(IssueIndex.java:235) ~[sonar-server-5.6.7.jar:na]
    at org.sonar.server.issue.ws.SearchAction.doHandle(SearchAction.java:301) ~[sonar-server-5.6.7.jar:na]
    at org.sonar.server.issue.ws.SearchAction.handle(SearchAction.java:288) ~[sonar-server-5.6.7.jar:na]
    at org.sonar.server.ws.WebServiceEngine.execute(WebServiceEngine.java:107) ~[sonar-server-5.6.7.jar:na]
    at sun.reflect.GeneratedMethodAccessor228.invoke(Unknown Source) ~[na:na]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_91]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_91]
    at org.jruby.javasupport.JavaMethod.invokeDirectWithExceptionHandling(JavaMethod.java:425) [jruby-complete-1.7.9.jar:na]
    at org.jruby.javasupport.JavaMethod.invokeDirect(JavaMethod.java:292) [jruby-complete-1.7.9.jar:na]
    at org.jruby.java.invokers.InstanceMethodInvoker.call(InstanceMethodInvoker.java:44) [jruby-complete-1.7.9.jar:na]

Yes it does plead for sometime before breaking down and refuses to show any issue on UI. Please help, all set to upgrade to SQ 5.6.7 so later we can upgrade to SQ 6.7.1 but now we are still running SQ 4.5.4 in Production (and it is able to handle such large amount of data)

How large is our DB? Our database has close to 27 million LOC, with 5.6 million closed/open issues on Database.

What do you expect from this? You've already stated 1) that your infrastructure isn't properly set up and 2) you plan to upgrade. I don't see the point in even attempting to "fix" your ES problems under these circumstances. — G. Ann - SonarSource Team, Mar 01 '18 at 15:57
The point is SonarQube 4.5.4 in this infrastructure is able to handle the data. We met the requirements of Sonarqube 5.6 still ES is failing, with this I won't be able to convince stakeholders to upgrade — Shiva, Mar 01 '18 at 17:12
It's possible you're facing [SONAR-8067](https://jira.sonarsource.com/browse/SONAR-8067) which was fixed in 6.1. In any case, you're going to have a hard time finding support for the _previous_ LTS. — G. Ann - SonarSource Team, Mar 02 '18 at 12:43
Ah! maybe, we are hitting it too. God help us! @G.Ann-SonarSourceTeam Thanks, we'd try and fine tune for now. Soon will upgrade to 6LTS — Shiva, Mar 02 '18 at 13:10
is this resolved, we are also facing similar issues recently — Ashokekumar S, Mar 05 '18 at 17:59
I'm trying to trim issues table, will let you know the result — Shiva, Mar 05 '18 at 18:14

Shiva · Accepted Answer · 2018-04-28T18:30:35.823

Proper Solution

Upgrade to SonarQube 6.1 LTS
if you are Oracle DB, make sure tables are analysed. i.e., keep indexing up-to-date
If you don't have the room to upgrade and indexes are up-to-date yet you encounter slowness, set minimal weeks for Database Cleaner, even less than what is recommended by SonarQube and run analysis.

When SonarQube removes old Snapshots all related old issues will be properly removed, only when new analysis is ran db cleaner is executed on the component. If you have ghost projects (not in SCM anymore) create empty projects and use same key to clear the DB.

Stupid Hack

For long and hard, I tried to change the code of SonarQube as recommended in the issue SONAR-8067 (linked in comments by Ann). But I was not able to do that, the description was too high level and I have almost no time at hand to reverse engineer like mad, so I picked the brute forced.

Special mention: I believe we already have a pretty crushing policy for closed issues, but they remained stubborn.

Thanks to scanning of minified JS, everyday our DB is exploding exponentially, right now it has 6.8 million rows in Issues table, and with no choice I executed the following command.

delete from issues where status = 'CLOSED' or status = 'RESOLVED'

This sql statement took 14 minutes and cleared 4.5 million rows from the table. Later from SonarQube home directory removed data, temp directories and restarted SonarQube, now ES is breathing. But the story is not over yet. Now the dashboard is unstable and the 6.8million rows are back in the table! (I have no idea how this can even happen, some rogue code in SonarQube I suspect).

Issues are still browsable from Issues, Measures and Code Dashboards. Like us if you have a custom Dashboard add Issues Widget,

Note to moderators: the stupid hack might be enough sometimes.

thanks shiva for the detailed steps, if we delete the complete data folder and restart the server will there be any data loss, please let me know — Ashokekumar S, Mar 07 '18 at 15:36
data will not be lost since then can be pulled from DB. data directory actually host data for ES. There will also be data for web and compute engine. (If you have any pending tasks those will be lost, I have not tested this part maybe history if CE will also be lost). — Shiva, Mar 07 '18 at 15:45

SonarQube 5.6.7 unable to search for issues (ES error)

1 Answers1