SonarQube upgrade to 7.3 version from 6.7.5v

Must-share information (formatted with Markdown):

  • which versions are you using (SonarQube, Scanner, Plugin, and any relevant extension)
    SonarQube 7.3
  • what are you trying to achieve
    Upgrade successfully to 7.3v.
  • what have you tried so far to achieve this
    I tried to upgrade the SonarQube instance to 7.3 version by following the instructions in the document .But it failed in DB migration process .Please find the attached document for the error and please let me know on how to go about it…
[username@servername logs]$ tail -100f web.log
2018.10.08 11:56:40 INFO web[][o.s.p.ProcessEntryPoint] Starting web
2018.10.08 11:56:41 INFO web[][o.a.t.u.n.NioSelectorPool] Using a shared selector for servlet write/read
2018.10.08 11:56:41 INFO web[][o.s.c.e.CoreExtensionsLoader] Loaded core extensions:
2018.10.08 11:56:42 INFO web[][o.e.p.PluginsService] no modules loaded
2018.10.08 11:56:42 INFO web[][o.e.p.PluginsService] loaded plugin [org.elasticsearch.join.ParentJoinPlugin]
2018.10.08 11:56:42 INFO web[][o.e.p.PluginsService] loaded plugin [org.elasticsearch.percolator.PercolatorPlugin]
2018.10.08 11:56:42 INFO web[][o.e.p.PluginsService] loaded plugin [org.elasticsearch.transport.Netty4Plugin]
2018.10.08 11:56:43 INFO web[][o.s.s.e.EsClientProvider] Connected to local Elasticsearch: [127.0.0.1:9001]
2018.10.08 11:56:43 INFO web[][o.s.s.p.LogServerVersion] SonarQube Server / 7.3.0.15553 / 7f72262e02808e9431d97735ece621a22a1d8208
2018.10.08 11:56:43 INFO web[][o.sonar.db.Database] Create JDBC data source for jdbc:oracle:thin:@lndbd201.hphc.org:1531:toldev1
2018.10.08 11:56:44 INFO web[][o.s.s.p.ServerFileSystemImpl] SonarQube home: /apps/ci/data/opt/sonar/sonarqube-7.3
2018.10.08 11:56:44 INFO web[][o.s.s.u.SystemPasscodeImpl] System authentication by passcode is disabled
2018.10.08 11:56:44 WARN web[][o.s.s.p.DatabaseServerCompatibility] Database must be upgraded. Please backup database and browse /setup
2018.10.08 11:56:44 INFO web[][o.s.s.p.ServerPluginRepository] Deploy plugin Git / 1.4.1.1128 / e051f5aeb379541b13956447b5dc8b4e0b23b098
2018.10.08 11:56:44 INFO web[][o.s.s.p.ServerPluginRepository] Deploy plugin LDAP / 2.2.0.608 / 79dc3fa4393a29667673c70182f3016288b548b7
2018.10.08 11:56:44 INFO web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarC# / 7.3.2.6129 / e7a731061a7fa928c4e1c11b830f22db97a61f83
2018.10.08 11:56:44 INFO web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarCSS / 1.0.1.508 / 2eb799e363666ae65036ad61a6e6adc1a1283097
2018.10.08 11:56:44 INFO web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarFlex / 2.4.0.1222 / 68d9cb3b7daccbc4869c4f2b89d09218d26a0829
2018.10.08 11:56:44 INFO web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarGo / 1.1.0.1612 / 1809c7be6d88396c0a1b8d597b1e9e0ed8d29730
2018.10.08 11:56:44 INFO web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarJS / 4.2.0.6476 / a21b56462f3375b5d51d90f1819862878476d817
2018.10.08 11:56:44 INFO web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarJava / 5.6.1.15064 / dd4545a04af6f7d0cb0486926cfa8cfc5cae5ee1
2018.10.08 11:56:44 INFO web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarKotlin / 1.0.1.965 / b5872affdfeda52e0a71494e2df9a3524b80d3bc
2018.10.08 11:56:44 INFO web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarPHP / 2.14.0.3569 / 59a202b8d1c5276ce32da2a0bc63c94cae4cdeb2
2018.10.08 11:56:44 INFO web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarPython / 1.10.0.2131 / a00e0ddc83bbeb01a9ee7475fab9eafe3f4096ac
2018.10.08 11:56:44 INFO web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarTS / 1.7.0.2893 / 0d296cae94240b183a0b3ae6e1a409a60fe9f865
2018.10.08 11:56:44 INFO web[][o.s.s.p.ServerPluginRepository] Deploy plugin SonarXML / 1.5.1.1452 / eadc1dcd0e24a3c77068e3c1d41af0327d6b16ea
2018.10.08 11:56:44 INFO web[][o.s.s.p.ServerPluginRepository] Deploy plugin Svn / 1.8.0.1168 / 4143f2d119022a758f6bf40aed4294e4c31706cf
2018.10.08 11:56:45 INFO web[][o.s.s.p.Platform] Database needs migration
2018.10.08 11:56:45 INFO web[][o.s.s.p.w.MasterServletFilter] Initializing servlet filter org.sonar.server.ws.WebServiceFilter@555652ef [pattern=UrlPattern{inclusions=[/api/system/migrate_db. *, …], exclusions=[/api/properties* , …]}]
2018.10.08 11:56:45 INFO web[][o.s.s.a.EmbeddedTomcat] HTTP connector enabled on port 9000
2018.10.08 11:57:18 INFO web[][o.s.s.p.d.m.DatabaseMigrationImpl] Starting DB Migration and container restart
2018.10.08 11:57:18 INFO web[][DbMigrations] Executing DB migrations…
2018.10.08 11:57:18 INFO web[][DbMigrations] #1900 ‘Add QUALITY_GATES.IS_BUILT_IN’…
2018.10.08 11:57:18 INFO web[][DbMigrations] #1900 ‘Add QUALITY_GATES.IS_BUILT_IN’: success | time=373ms
2018.10.08 11:57:18 INFO web[][DbMigrations] #1901 ‘Populate QUALITY_GATES.IS_BUILT_IN’…
2018.10.08 11:57:18 INFO web[][o.s.s.p.d.m.s.MassUpdate] 2 quality_gates processed (0 items/sec)
2018.10.08 11:57:18 INFO web[][DbMigrations] #1901 ‘Populate QUALITY_GATES.IS_BUILT_IN’: success | time=28ms
2018.10.08 11:57:18 INFO web[][DbMigrations] #1902 ‘Make QUALITY_GATES.IS_BUILT_IN not null’…
2018.10.08 11:57:18 INFO web[][DbMigrations] #1902 ‘Make QUALITY_GATES.IS_BUILT_IN not null’: success | time=111ms
2018.10.08 11:57:18 INFO web[][DbMigrations] #1903 ‘Remove quality gates loaded templates’…
2018.10.08 11:57:18 INFO web[][o.s.s.p.d.m.s.MassUpdate] 1 delete loaded templates for quality gate processed (0 items/sec)
2018.10.08 11:57:18 INFO web[][DbMigrations] #1903 ‘Remove quality gates loaded templates’: success | time=21ms
2018.10.08 11:57:18 INFO web[][DbMigrations] #1904 ‘Rename quality gate “SonarQube way” to “Sonar way”’…
2018.10.08 11:57:18 INFO web[][o.s.s.p.d.m.s.MassUpdate] 1 quality gates processed (0 items/sec)
2018.10.08 11:57:18 INFO web[][DbMigrations] #1904 ‘Rename quality gate “SonarQube way” to “Sonar way”’: success | time=15ms
2018.10.08 11:57:18 INFO web[][DbMigrations] #1905 ‘Drop LOADED_TEMPLATES table’…
2018.10.08 11:57:19 INFO web[][DbMigrations] #1905 ‘Drop LOADED_TEMPLATES table’: success | time=968ms
2018.10.08 11:57:19 INFO web[][DbMigrations] #1906 ‘Create table live_measures’…
2018.10.08 11:57:19 INFO web[][DbMigrations] #1906 ‘Create table live_measures’: success | time=240ms
2018.10.08 11:57:19 INFO web[][DbMigrations] #1907 ‘Populate table live_measures’…
2018.10.08 11:57:23 ERROR web[][DbMigrations] #1907 ‘Populate table live_measures’: failure | time=3268ms
2018.10.08 11:57:23 ERROR web[][DbMigrations] Executed DB migrations: failure | time=5030ms
2018.10.08 11:57:23 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=5096ms
2018.10.08 11:57:23 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=AVg1OT3qCLpZv7Td2B7l,project_uuid=AVg1OT3qCLpZv7Td2B7i,metric_id=10071,value=136,text_value=null,variation_value_1=0,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:44)
at org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl.execute(MigrationStepsExecutorImpl.java:75)
… 9 common frames omitted
Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (SONAR_SBX.LIVE_MEASURES_COMPONENT) violated
    at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10500)
    at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:230)
    at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
    at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
    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

Thanks,
Nagashree.B

I would suggest first reviewing existing ‘unicity constraint’ errors reported here, couple of discussions on this topic. For example:

Hi Nicholas,
Once 7.3 version is brought up,it is making connection to the DB & even after terminating the connections ,it is getting respawn.Please suggest what can be done to not to make connection with db before db migration…

Thanks,
Nagashree.B

Am not entirely clear on what concrete error/issue you’re hitting here. I would suggest reviewing the various SonarQube logs (see troubleshooting documentation), looking at existing topics for similar problems, and then describing your issue more in details if needed (with corresponding logs).

Hello,
I have the same problem, have you solved yours?
I found on the internet: https://stackoverflow.com/questions/48666926/sonarqube-update-database-for-version-7-fails

Is this a viable solution?

Thank you

Hi KevinL,

we have received following as output for the query to our Oracle DB:

SQL> SELECT p.uuid, pm.metric_id, COUNT(1) FROM project_measures pm INNER JOIN projects p on p.uuid = pm.component_uuid INNER JOIN snapshots s on s.uuid = pm.analysis_uuid WHERE s.islast = TRUE and pm.person_id is null GROUP BY p.uuid, pm.metric_id HAVING COUNT(1) > 1;

SELECT p.uuid, pm.metric_id, COUNT(1) FROM project_measures pm INNER JOIN projects p on p.uuid = pm.component_uuid INNER JOIN snapshots s on s.uuid = pm.analysis_uuid WHERE s.islast = TRUE and pm.person_id is null GROUP BY p.uuid, pm.metric_id HAVING COUNT(1) > 1

                                                                                                                                                                                    *

ERROR at line 1:

ORA-00904: "TRUE": invalid identifier
Please let me know if the query has no syntax error …

Hi Ann,
Please check out the link and let us know if there is any side effects on this action too…

Thanks,
Nagashree

I replaced “true” with “0”.

I removed the index, and the migration worked.
But I do not know the future impacts of the removal of this unique index …

Hi Nicholas,

Please add Alex & Ann for the discussion & let me know if we do the action suggested by KevinL works or not & what may be the side effects for the same…

Thanks,
nagashree

We solved our problem, we were able to remove duplicates.

select
       m.id, m.name, m.description,
       CASE WHEN d.metric_id IS NULL THEN 'ok' ELSE 'KO' END
from
       metrics m
       left outer join (
             select distinct 
                    a.metric_id 
             from 
                    metrics m,
                    (
                    select
                           component_uuid, metric_id, count(1) as c
                    from 
                           live_measures
                    group by
                           component_uuid, metric_id
                    having
                           count(1) > 1
                    ) as a
             where
                    m.id = a.metric_id
       ) as d on (m.id = d.metric_id);

Then, with the result of the knockouts, execute the query (by changing the id=5 )

select
       distinct p.long_name
from 
       live_measures l,
       projects p,
       (
             select
                    component_uuid, count(1) as c
             from 
                    live_measures
             where
                    metric_id = 5
             group by
                    component_uuid
             having
                    count(1) > 1
       ) as  b

where
       p.uuid = l.project_uuid
       and l.component_uuid = b.component_uuid
       and l.metric_id = 5;

Then, for each project found, delete it via the HMI and recreate the unique index

CREATE UNIQUE INDEX live_measures_component ON live_measures (component_uuid,metric_id);

Hi Kevin,
Thank you…But are these sonarsource corrections for the issue ? or do we still need to check with them on this solution?

Thanks,
Nagashree.B