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

DB migration fails because of duplicates in table LIVE_MEASURES

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Won't Fix
    • Affects Version/s: 7.0, 7.1, 7.2, 7.3, 7.4, 7.5, 7.6, 7.7, 7.8, 7.9.1
    • Fix Version/s: None
    • Component/s: Database
    • Labels:
      None
    • Edition:
      Community
    • Production Notes:
      None

      Description

      The problem

      Before 7.9

      When upgrading from LTS 6.7 to any later version of SonarQube prior to 7.9, the DB migration "Populate table live_measures" can fail with an error such as the following:

      2019.05.04 20:04:12 ERROR web[][DbMigrations] #1907 'Populate table live_measures': failure | time=1053608ms
      2019.05.04 20:04:12 ERROR web[][DbMigrations] Executed DB migrations: failure | time=1054538ms
      2019.05.04 20:04:12 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=1054641ms
      2019.05.04 20:04:12 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration ended with an exception
      org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #1907 'Populate table live_measures' failed
              at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:79)
              at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:67)
              at java.lang.Iterable.forEach(Iterable.java:75)
              at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:52)
              at org.sonar.server.platform.db.migration.engine.MigrationEngineImpl.execute(MigrationEngineImpl.java:68)
              at org.sonar.server.platform.db.migration.DatabaseMigrationImpl.doUpgradeDb(DatabaseMigrationImpl.java:105)
              at org.sonar.server.platform.db.migration.DatabaseMigrationImpl.doDatabaseMigration(DatabaseMigrationImpl.java:80)
              at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
              at java.lang.Thread.run(Thread.java:745)
      Caused by: java.lang.IllegalStateException: Error during processing of row: [uuid=AVwkmBmj8RayhpqjBGdA,project_uuid=AVwkmBmj8RayhpqjBGc_,metric_id=98,value=136.00000000000000000000,text_value=null,variation_value_1=null,measure_data=null]
              at org.sonar.server.platform.db.migration.step.SelectImpl.newExceptionWithRowDetails(SelectImpl.java:89)
              at org.sonar.server.platform.db.migration.step.SelectImpl.scroll(SelectImpl.java:81)
              at org.sonar.server.platform.db.migration.step.MassUpdate.execute(MassUpdate.java:92)
              at org.sonar.server.platform.db.migration.version.v70.PopulateLiveMeasures.execute(PopulateLiveMeasures.java:57)
              at org.sonar.server.platform.db.migration.step.DataChange.execute(DataChange.java:45)
              at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:75)
              ... 9 common frames omitted
      Caused by: java.sql.BatchUpdateException: Duplicate entry 'AVwkmBmj8RayhpqjBGc_-1' for key 'live_measures_component'
              at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
              at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
              at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
              at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
              at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
              at com.mysql.jdbc.Util.getInstance(Util.java:408)
              at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1163)
              at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1587)
              at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1253)
              at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:970)
              at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:223)
              at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:223)
              at org.sonar.server.platform.db.migration.step.UpsertImpl.addBatch(UpsertImpl.java:56)
              at org.sonar.server.platform.db.migration.step.MassUpdate.callSingleHandler(MassUpdate.java:119)
              at org.sonar.server.platform.db.migration.step.MassUpdate.lambda$execute$0(MassUpdate.java:92)
              at org.sonar.server.platform.db.migration.step.SelectImpl.scroll(SelectImpl.java:78)
              ... 13 common frames omitted 

      In 7.9 or later

      When upgrading from LTS 6.7 to SonarQube 7.9 or 7.9.1, the DB migration "Add live_measures.metric_id index" can fail with an error such as the following:

      2019.07.10 11:34:38 INFO  web[][DbMigrations] #1942 'Add live_measures.metric_id index'...
      2019.07.10 11:34:48 ERROR web[][DbMigrations] #1942 'Add live_measures.metric_id index': failure | time=9769ms
      2019.07.10 11:34:48 ERROR web[][DbMigrations] Executed DB migrations: failure | time=807844ms
      2019.07.10 11:34:48 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=807938ms
      2019.07.10 11:34:48 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration ended with an exception
      org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #1942 'Add live_measures.metric_id index' failed
      	at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:79)
      	at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:67)
      	at java.base/java.lang.Iterable.forEach(Iterable.java:75)
      	at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:52)
      	at org.sonar.server.platform.db.migration.engine.MigrationEngineImpl.execute(MigrationEngineImpl.java:68)
      	at org.sonar.server.platform.db.migration.DatabaseMigrationImpl.doUpgradeDb(DatabaseMigrationImpl.java:105)
      	at org.sonar.server.platform.db.migration.DatabaseMigrationImpl.doDatabaseMigration(DatabaseMigrationImpl.java:80)
      	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
      	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
      	at java.base/java.lang.Thread.run(Thread.java:834)
      Caused by: java.lang.IllegalStateException: Fail to execute CREATE UNIQUE INDEX live_measures_component ON live_measures (component_uuid, metric_id)
      	at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:97)
      	at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:77)
      	at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:117)
      	at org.sonar.server.platform.db.migration.version.v70.AddLiveMeasuresMetricIndex.execute(AddLiveMeasuresMetricIndex.java:44)
      	at org.sonar.server.platform.db.migration.step.DdlChange.execute(DdlChange.java:45)
      	at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:75)
      	... 9 common frames omitted
      Caused by: java.sql.SQLException: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
      
      	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
      	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
      	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
      	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
      	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
      	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
      	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
      	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:30)
      	at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:931)
      	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
      	at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1792)
      	at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1745)
      	at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:334)
      	at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:175)
      	at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:175)
      	at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:82) 

      What's happening

      Before 7.9, a DB migration step fails to insert a row in table LIVE_MEASURES because of unique index on columns COMPONENT_UUID and METRIC_ID.

      In 7.9, another DB migration step fails when the unique index on columns COMPONENT_UUID and METRIC_ID is being created. 

      The problem is the same. The effect differs because in 7.9, to improve overall DB migration performance, the creation has been moved to a later step.

      LIVE_MEASURES is a new table added in 7.0. It's data is coming from table PROJECT_MEASURES. The DB migration step "populate live measures" copies the measures of the latest analysis of all projects and branches from table PROJECT_MEASURES into table LIVE_MEASURES.

      The errors above appear when data of one (or many) analysis was copied from table PROJECT_MEASURES which contains more than one row for a given metric and component.

      Table PROJECT_MEASURES does not have a constraint enforcing uniqueness of a unique value per component. The reason is that this constraint is not true as this table old many analysis of a single component. Adding a constraint on table PROJECT_MEASURES to enforce uniqueness by snapshot adds cost at runtime and brings no value but data integrity in the DB. On the other hand, this constraint, and most importantly the unique index which comes with it, is a strong requirement of the features which relies on table LIVE_MEASURES.

      Even if the DB constraint does not exist, the SonarQube software garanties the uniqueness of a single row for a given metric and component. This guarantee was added when the Compute Engine was created and the DB connection dropped from the scanners in SonarQube 5.6 LTS.

      Conclusion: failure is coming from projects which have never been analysed since SonarQube 5.6.

      Work around

      To workaround the issue, there are two possibles actions to take before migrating SonarQube:

      1. delete all or some of the projects causing the problem
      2. re-analyse with SQ 5.6 or later the projects you did not delete

      To find out which projects are problematic, one can execute the following SQL request:

      PostgreSQL

      select
        p.kee,p."name"
      from
        projects p
      where
        p.qualifier = 'TRK'
        and p.scope = 'PRJ'
        and exists (
          select
            pm.component_uuid, pm.metric_id, count(1)
          from
            project_measures pm
          inner join snapshots s on
            s.component_uuid = p.uuid
            and s.islast = true
          where
            pm.analysis_uuid = s.uuid
          group by
            pm.component_uuid, pm.metric_id
          having
            count(1) > 1
       )
      

      Oracle and SQL Server

      select
          p.kee,p.name
      from
        projects p
      inner join snapshots s on
        s.component_uuid = p.uuid
        and s.islast = 1
      where
       p.qualifier = 'TRK'
       and p.scope = 'PRJ'
       and exists (
          select
            pm.component_uuid, pm.metric_id, count(1)
          from
            project_measures pm
          where
            pm.analysis_uuid = s.uuid
          group by
            pm.component_uuid, pm.metric_id
          having
            count(1) > 1
       ) 
       

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              sebastien.lesaint Sebastien Lesaint
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: