Sonar database cleanup

Question

We use SonarQube 5.6.6 running a docker container connected to a mysql database. We have default settings in database cleaner configured. However, we recently found out that the database is not at all getting cleaned up.

I cannot find any log entries related to database cleaner. So I cannot figure out what is going wrong.

Here is our current database size

MySQL [sonar]> SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;

+--------------------+---------------+
| DB Name            | DB Size in MB |
+--------------------+---------------+
| information_schema |           0.2 |
| sonar              |       28842.6 |
+--------------------+---------------+

MySQL [sonar]> SELECT
    ->     table_name AS `Table`,
    ->     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
    -> FROM information_schema.TABLES
    -> WHERE table_schema = 'sonar'
    -> ;
+---------------------------+------------+
| Table                     | Size in MB |
+---------------------------+------------+
| active_dashboards         |       0.05 |
| active_rule_parameters    |       0.08 |
| active_rules              |       0.34 |
| activities                |       5.03 |
| authors                   |       0.03 |
| ce_activity               |       2.06 |
| ce_queue                  |       0.05 |
| dashboards                |       0.02 |
| duplications_index        |      14.55 |
| events                    |      23.58 |
| file_sources              |   11902.03 |
| group_roles               |       0.20 |
| groups                    |       0.02 |
| groups_users              |       0.06 |
| issue_changes             |      24.53 |
| issue_filter_favourites   |       0.03 |
| issue_filters             |       0.03 |
| issues                    |     451.50 |
| loaded_templates          |       0.02 |
| manual_measures           |       0.03 |
| measure_filter_favourites |       0.03 |
| measure_filters           |       0.03 |
| metrics                   |       0.08 |
| notifications             |       0.02 |
| perm_templates_groups     |       0.02 |
| perm_templates_users      |       0.02 |
| permission_templates      |       0.02 |
| project_links             |       0.31 |
| project_measures          |   11123.83 |
| project_qprofiles         |       0.03 |
| projects                  |    1804.22 |
| properties                |       0.03 |
| quality_gate_conditions   |       0.02 |
| quality_gates             |       0.03 |
| resource_index            |    3049.98 |
| rules                     |       3.72 |
| rules_parameters          |       0.17 |
| rules_profiles            |       0.03 |
| schema_migrations         |       0.02 |
| snapshots                 |     435.53 |
| user_roles                |       0.05 |
| user_tokens               |       0.05 |
| users                     |       0.11 |
| widget_properties         |       0.03 |
| widgets                   |       0.05 |
+---------------------------+------------+
45 rows in set (0.00 sec)

How can I cleanup the database manually? Thank you in advance for any hints.

What do you expect to be cleaned up that is not. Please be more specific than "not at all getting cleaned up". — G. Ann - SonarSource Team, Aug 14 '17 at 12:46
I expect that the database is not 28 Gig in size for a codebase of few hundred MB. This makes it difficult for us to size our database infra. Specifically, what do these tables contain? file_sources & project_measures. They contain 1.3 & 63 million rows respectively. I would like to clean these up manually if possible. — S N, Aug 15 '17 at 11:33

score 0 · Answer 1 · edited Apr 21 '19 at 05:10

I think that for database cleaner properties only works when the projects is analyzed again i.e db is cleaned for the particular project as per the properties only at the time of next SonarQube analysis.

Make sure you checking for deletion of snapshot's for the project which is analysed again.

Suggestion: Try using SonarQube ReST API to delete projects which will internally clean your database. SonarQube API to delete projects:

HTTP POST
<SonarQubeBaseURL>/api/projects/delete?id=project_id

As a reminder you can get documentaiton to the SonarQube APIs by going to this url <SonarQubeBaseURL>/web_api/ on your SonarQube server.

Sonar database cleanup

1 Answers1