Uploaded image for project: 'SonarQube'
  1. SonarQube
  2. SONAR-10131

Purge of db table project_measures is incomplete

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 6.0
    • Fix Version/s: 6.7.1, 7.0
    • Component/s: Database
    • Labels:
      None

      Description

      The table project_measures is purged during analysis so that the past measures on files, and optionally directories, are deleted and keeps size of table under control.
      Since version 6.0, the measures on unit test files are never purged. That generates a significative amount of unused data. For example on https://next.sonarqube.com/sonarqube, the table has 16.4M rows, distributed in:

      • 1.5M rows for "last" measures, including 1.1M for main and test files
      • 14.8M rows to past measures, including
        • 0.2M rows for main files
        • 9.5M rows for test files ---> this is the data leak.

      This ticket aims to fix the leak: correctly remove file measures when purging an analysis. Managing removal of existing past file measures is not part of the fix in order to minimize DB migration duration on a patch. If needed the following SQL request can be executed in background while SonarQube is up:

      -- MySQL and SQLServer
      delete pm from project_measures pm
      inner join projects c on c.uuid = pm.component_uuid 
      where c.qualifier in ('UTS', 'FIL') or pm.person_id is not null
      
      -- PostgreSQL
      delete from project_measures pm 
      using projects c 
      where c.uuid = pm.component_uuid 
      and (c.qualifier in ('UTS', 'FIL') or pm.person_id is not null)
      
      -- Oracle
      delete from project_measures pm where exists (
        select 1 from project_measures pm2 
        inner join projects c on c.uuid = pm2.component_uuid 
        where (c.qualifier in ('UTS', 'FIL') or pm.person_id is not null) 
        and pm.id = pm2.id
      )
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                simon.brandhof Simon Brandhof
                Reporter:
                simon.brandhof Simon Brandhof
              • Votes:
                1 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Due:
                  Created:
                  Updated:
                  Resolved: