Database Cleanup - not working

[fergus...@barclays.com]

Hi all,

On SonarQube 6.3 and 5.6.3 running on Linux with SQL Server Database.

I'm seeing some strange behaviour with the database clean-up.  It doesn't appear to have deleted "ANY" of the snapshot files ever.

Our clean up setting have always been set to the default, so I'm unclear how this hasn't been working, I understand that the cleanup happens only when a new project is analysed, but this doesn't seem to have cleaned any of the database snapshots down.

Is there something I can look for in the logs to see why this isn't working as intended

I'm able to both delete projects and analysis manually in the UI (this doesnt seem to affect DB size when removed) , but the number of snapshots for the some of the projects is significant to be able to realistically do this by hand

Thanks in advance!

Fergus.

[G. Ann Campbell]

Hi Fergus,

Housekeeping only runs during analysis. There is no cron daemon that automatically kicks off a virtual Roomba. Instead, when I analyze project Foo, at the end of that analysis, previous Foo snapshots will be examined for whether or not they're deletable.

The analysis pattern shown in your screenshot seems odd: 15+ analysis on one day and then nothing for almost 4 months. 

Could you try running a new analysis, or even putting analysis of your project on a regular schedule, and then seeing how it looks?

Ann

[fergus...@barclays.com]

Hi Ann, 

Thanks for the response, this project I was cleaning up a significant portion of it manually, and this is why it looks like that.

Does the cleanup execute under an internal account, or is there some reliance on the credentials from the sonar.login used for execution (in our setup we have this account with execute analysis + browse only)

Kind Regards,

Fergus.

[fergus...@barclays.com]

I've added "administrator" privileges to the sonar.login we use for analysis for this project, and it successfully does the cleanup.

Ideally we don't want to grant this privilege to the account that is used to execute the analysis, "execute analysis" privilege should be enough to do all the tasks around the executing of the analysis.  Is there jira ticket on this already?

Kind Regards,

Fergus.

[G. Ann Campbell]

Hi Fergus,

This happens as part of the analysis, so if the account has permissions to do that, it should be good to go.

Ann

---

G. Ann Campbell | SonarSource

Product Manager

@GAnnCampbell

http://sonarsource.com

--
You received this message because you are subscribed to a topic in the Google Groups "SonarQube" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sonarqube/_zltt6jUiBk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sonarqube+unsubscribe@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sonarqube/dd709d14-ffa3-4a5c-9dd7-70cd29d81090%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[fergus...@barclays.com]

Thanks Anne, 

Can you please consider an improvement so that the "Execute Analysis" permission is able to do the cleanup which is run as part of the analysis (otherwise what is the point in "execute analysis", if all the users who run analysis have to have "Administer" permission too, in order that the snapshots are cleaned up properly)

Kind Regards,

Fergus.

To unsubscribe from this group and all its topics, send an email to sonarqube+...@googlegroups.com.

[G. Ann Campbell]

Hi Fergus,

I understand why you're asking that and appreciate the feedback, but we really see manual deletion of random snapshots as a more privileged operation that the execution of the admin-set housekeeping schedule so we're not likely to make that change.

Ann

[fergus...@barclays.com]

Hi Anne,

I'm not sure that you understand my point.

I'm not asking for the Execute Analysis permission to grant the ability for a user with that permission to remove random snapshots from the web gui or via the webapi - that should continue to be tied to the "Administer" permission. 

An account solely with the "execute analysis" and "browse" should however be able to during the analysis, execute the snapshot clean up (in line with the settings here https://docs.sonarqube.org/display/SONAR/Housekeeping) and this is what is not working today without having "Administer" permission on the project.  This is required due to the way that the database cleaner executes at the end of every analysis.

Kind Regards,

Fergus.

[G. Ann Campbell]

Hi Fergus,

Thanks for following up. For the record, the housekeeping routines should execute when a user with only Analyze runs an analysis. Do you have logs that indicate that this 

a) isn't working properly

b) does work if the analysis account as Administer on the project?

If so, could you share them please?

Because so far, I'm not convinced that the cleanup isn't happening properly. I agree that the fact that you have multiple snapshots remaining from a day in October is odd, but without knowing the schedule on which you analyze, it's only circumstantial. Also, just to tick the box, could you provide a screenshot of your housekeeping settings? 

(Project) Administration > General > Database Cleaner

Thx,

Ann

[G. Ann Campbell]

Hi Fergus,

Don't forget to include the Google Group in your replies so posterity can benefit from your wisdom. :-)

Thanks for the updated screenshots. I've figured out what's going on. At least with the project you screenshotted.

You're updating the version string for each analysis (with the build number?). This creates a "version event" on the snapshot, and snapshots with events aren't deleted until they're (by default) 5 years old. (https://docs.sonarqube.org/display/SONAR/Housekeeping)

Instead, I'd recommend you make your analysis versions correspond to your release versions (you don't release every single build, do you?)

Doing that would 

1) allow the housekeeping routines to work as you expect

2) allow you to make better use of the leak period to make sure that each new release was at least as good as the previous one.

Ann

P.S. We don't have a security upload facility, but you could have sent me your logs privately. 

---

G. Ann Campbell | SonarSource

Product Manager

@GAnnCampbell

http://sonarsource.com

On Wed, Jun 7, 2017 at 11:35 AM, <fergus...@barclays.com> wrote:

Hi Ann,

 

There isn’t anything in the logs I could see that indicated an issue with this

 

Do you have a secure upload facility in order that we can provide full logs (without this I’m not able to send these)?

 

2017.06.06 17:14:43 DEBUG ce[AVx-Ln_mqx_HjmD26x8p][o.s.s.c.t.s.ComputationStepExecutor] Update last usage date of quality profiles | time=7ms

2017.06.06 17:14:44 DEBUG ce[AVx-Ln_mqx_HjmD26x8p][o.s.d.p.p.KeepOneFilter] -> Keep one snapshot per day between 2017-05-23 and 2017-06-05

2017.06.06 17:14:44 DEBUG ce[AVx-Ln_mqx_HjmD26x8p][o.s.d.p.p.DefaultPeriodCleaner] <- Delete analyses of component AVmYwE8oUt_h4Z9LjbH2:

2017.06.06 17:14:44 DEBUG ce[AVx-Ln_mqx_HjmD26x8p][o.s.d.p.p.KeepOneFilter] -> Keep one snapshot per week between 2017-05-02 and 2017-05-23

2017.06.06 17:14:44 DEBUG ce[AVx-Ln_mqx_HjmD26x8p][o.s.d.p.p.DefaultPeriodCleaner] <- Delete analyses of component AVmYwE8oUt_h4Z9LjbH2:

2017.06.06 17:14:44 DEBUG ce[AVx-Ln_mqx_HjmD26x8p][o.s.d.p.p.KeepOneFilter] -> Keep one snapshot per month between 2017-05-02 and 2017-05-02

2017.06.06 17:14:44 DEBUG ce[AVx-Ln_mqx_HjmD26x8p][o.s.d.p.p.DefaultPeriodCleaner] <- Delete analyses of component AVmYwE8oUt_h4Z9LjbH2:

2017.06.06 17:14:44 DEBUG ce[AVx-Ln_mqx_HjmD26x8p][o.s.d.p.p.DeleteAllFilter] -> Delete data prior to: 2017-05-02

2017.06.06 17:14:44 DEBUG ce[AVx-Ln_mqx_HjmD26x8p][o.s.d.p.p.DefaultPeriodCleaner] <- Delete analyses of component AVmYwE8oUt_h4Z9LjbH2:

2017.06.06 17:14:44 DEBUG ce[AVx-Ln_mqx_HjmD26x8p][o.s.d.purge.PurgeDao] <- Delete aborted builds

2017.06.06 17:14:53 DEBUG ce[AVx-Ln_mqx_HjmD26x8p][o.s.s.c.t.s.ComputationStepExecutor] Purge db | time=9682ms

 

Up until yesterday, I had the “default” settings set up, since this is out test server, however, I’ve change these now to try and reduce database bloat

 

This is how it looks from the gui

 

 

 

Kind Regards,

Fergus.

 

 

From: G. Ann Campbell [mailto:ann.campbell@sonarsource.com]
Sent: Wednesday, June 07, 2017 3:56 PM
To: SonarQube
Cc: Murray, Fergus: R&A (GLA)
Subject: Re: Database Cleanup - not working

 

This mail originated from outside our organisation

---

_______________________________________________

This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer.

For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please see http://publicresearch.barclays.com.

_______________________________________________

[fergus...@barclays.com]

Hi Ann,

Thanks for helping us identifying that the cause of the issue was passing the version to the analysis which we have been using to tie a CI build number to the Sonarqube analysis.

Since Sonarqube is integrated in our CI pipeline (nightly builds), the version numbers that are passed are the "build" version numbers from our CI process (teamcity) for develop (default) branches of the git code.

We release off separate release branches, so running sonarqube on there is already too late!

We have a lot of sonar projects affected in this way, and our only course of action is to create some script with JSON to leverage the webapi to remove the version events for all our projects, I couldnt see another way of doing this.  Unfortunately the webapi calls for this are only available at 6.3, so I'm unclear how I can clean up our live version (5.6.3)

Would you please consider an enhancement to have a cleanup option that allows to ignore those version events to allow the cleanup to work across them

Kind Regards,

Fergus.

On Wednesday, June 7, 2017 at 4:46:09 PM UTC+1, G. Ann Campbell wrote:

Hi Fergus,

[G. Ann Campbell]

Hi Fergus,

This feels like a dumb question at this point, but why not just hard-code the sonar.version values you pass in to analysis?

Ann

---

G. Ann Campbell | SonarSource

Product Manager

@GAnnCampbell

http://sonarsource.com

To unsubscribe from this group and all its topics, send an email to sonarqube+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sonarqube/57f65c71-bea6-4b9f-9a90-578f6a246dfd%40googlegroups.com.

[G. Ann Campbell]

...Or perhaps explain why you want to tie analyses to your builds so explicitly?

Ann

[fergus...@barclays.com]

That's what we have done going forward for just now, but we have a significant legacy to clean-up to handle.

Having a link back from SQ to the CI build version that generated the analysis, is something that is useful from a research and audit perspective, and something we use to help teams troubleshoot specific issues with "why" questions like "why did my coverage drop on version x".  Having the build number ties it back to a specific build, and from this we can identify a specific code commit (or error in the build execution potentially) .

Kind Regards,

Fergus.

[G. Ann Campbell]

Hi Fergus,

Thanks for sharing your use case. We do actually have this in our sights but there's no ETA yet for the level of detail you seek.

Ann