Upgrading SQ from 8.2 to 8.5 Developer Edition Failed at DB Upgrade

Hi,
I’m trying to Upgrade SonarQube Server from 8.2 to 8.5 Developer Edition. And as part of DB Upgrade I’m getting below exceptions in the logs:

2020.10.12 04:23:53 ERROR web[][DbMigrations] Executed DB migrations: failure | time=21ms
2020.10.12 04:23:53 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=142ms
2020.10.12 04:23:53 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 #3438 'Drop column 'ID' of 'PROJECT_MEASURES' table' 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 com.google.common.collect.ImmutableList.forEach(ImmutableList.java:405)
	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 ALTER TABLE project_measures DROP COLUMN id
	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:106)
	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:86)
	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:128)
	at org.sonar.server.platform.db.migration.version.v84.common.DropIdColumn.execute(DropIdColumn.java:39)
	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: com.microsoft.sqlserver.jdbc.SQLServerException: The index 'measures_person_composite_dw' is dependent on column 'id'.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1624)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:868)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:768)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2979)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:744)
	at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
	at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:91)
	... 14 common frames omitted

Could someone help. Thanks in advance

Ariv

Hi, this index does not ring a bell to me, I don’t know where it’s from. You can safely drop it, and resume your migration:

DROP INDEX measures_person_composite_dw;

Thanks much. We will try it out!

After dropping the index I got again:
Caused by: java.lang.IllegalStateException: Fail to execute ALTER TABLE project_measures DROP COLUMN variation_value_2, variation_value_3, variation_value_4, variation_value_5
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The object ‘DF__project_m__varia__6CF8245B’ is dependent on column ‘variation_value_2’.

After dropping the column manually again got:
Fail to execute ALTER TABLE project_measures DROP COLUMN variation_value_2, variation_value_3, variation_value_4, variation_value_5
ALTER TABLE DROP COLUMN failed because column ‘variation_value_2’ does not exist in table ‘project_measures’.

I’m getting that same error after attempting to upgrade from 8.4 to 8.5:

org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #4003 'Drop unused variation values columns in 'project_measures' table' 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 com.google.common.collect.ImmutableList.forEach(ImmutableList.java:405)
	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 ALTER TABLE project_measures DROP COLUMN variation_value_2, variation_value_3, variation_value_4, variation_value_5
	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:106)
	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:86)
	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:128)
	at org.sonar.server.platform.db.migration.version.v85.DropUnusedVariationsInProjectMeasures.execute(DropUnusedVariationsInProjectMeasures.java:36)
	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: com.microsoft.sqlserver.jdbc.SQLServerException: The object 'DF__project_m__varia__19DFD96B' is dependent on column 'variation_value_2'.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1624)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:868)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:768)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2979)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:744)
	at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
	at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:91)
	... 14 common frames omitted

Not sure what to do :confused:

Looks like SQ identified the bug four days ago: https://jira.sonarsource.com/projects/SONAR/issues/SONAR-13948

Running this script allowed the upgrade to complete for me:

USE [SonarQube]
GO

ALTER TABLE [dbo].[project_measures] DROP CONSTRAINT [DF__project_m__varia__18EBB532]
GO

ALTER TABLE [dbo].[project_measures] DROP CONSTRAINT [DF__project_m__varia__19DFD96B]
GO

ALTER TABLE [dbo].[project_measures] DROP CONSTRAINT [DF__project_m__varia__1AD3FDA4]
GO

ALTER TABLE [dbo].[project_measures] DROP CONSTRAINT [DF__project_m__varia__1BC821DD]
GO

ALTER TABLE [dbo].[project_measures] DROP CONSTRAINT [DF__project_m__varia__1CBC4616]
GO


ALTER TABLE [dbo].[snapshots] DROP CONSTRAINT [DF__snapshots__perio__245D67DE]
GO

ALTER TABLE [dbo].[snapshots] DROP CONSTRAINT [DF__snapshots__perio__25518C17]
GO

ALTER TABLE [dbo].[snapshots] DROP CONSTRAINT [DF__snapshots__perio__2645B050]
GO

ALTER TABLE [dbo].[snapshots] DROP CONSTRAINT [DF__snapshots__perio__2739D489]
GO

ALTER TABLE [dbo].[snapshots] DROP CONSTRAINT [DF__snapshots__perio__282DF8C2]
GO

ALTER TABLE [dbo].[snapshots] DROP CONSTRAINT [DF__snapshots__perio__29221CFB]
GO

ALTER TABLE [dbo].[snapshots] DROP CONSTRAINT [DF__snapshots__perio__2A164134]
GO

ALTER TABLE [dbo].[snapshots] DROP CONSTRAINT [DF__snapshots__perio__2B0A656D]
GO

Thanks for the inputs - however the snapshots are varying so I had to drop one-by-one.

After dropping these constraints, I have managed to start the server but while navigating the application I’m getting below error in the logs:

2020.10.13 05:58:21 ERROR web[AXUgUP6oVXelgRZ6AABW][o.s.s.w.WebServiceEngine] Fail to process request http://sonarqube.xxx.xxxx:9000/api/measures/search_history?from=2019-10-13T04%3A57%3A26%2B0000&component=ATG&metrics=bugs%2Cvulnerabilities%2Csqale_index%2Cduplicated_lines_density%2Cncloc%2Ccoverage%2Ccode_smells%2Creliability_rating%2Csecurity_rating%2Csqale_rating&ps=1000
org.apache.ibatis.exceptions.PersistenceException:

Error querying database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name ‘metric_uuid’.

The error may exist in org.sonar.db.measure.MeasureMapper

The error may involve org.sonar.db.measure.MeasureMapper.selectPastMeasuresOnSeveralAnalyses-Inline

The error occurred while setting parameters

SQL: select pm.uuid as uuid, pm.metric_uuid as metricUuid, pm.component_uuid as componentUuid, pm.analysis_uuid as analysisUuid, pm.value as value, pm.text_value as textValue, pm.alert_status as alertStatus, pm.alert_text as alertText, pm.measure_data as dataValue, pm.variation_value_1 as variation from project_measures pm inner join snapshots analysis on analysis.uuid = pm.analysis_uuid where pm.component_uuid = ? and analysis.created_at>=? and pm.metric_uuid in ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) and analysis.status=?

Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name ‘metric_uuid’.

Hi, your issues look indeed caused by https://jira.sonarsource.com/browse/SONAR-13948. Please hold on and wait for the fix, we are working on it this very week. a 8.5.1 will be released to address that. In the meantime, I suggest you to revert your database to your previous stable version and subscribe to notifications of the jira ticket :slight_smile:

Cool - Thanks for your reply!

Hi,

Has there been any update on the upgrade error as yet?
I also get the same error when upgrading from 8.4 to 8.5.
Any assistance would be appreciated.

I see the following error in the logs.

2020.10.15 23:24:57 ERROR web[DbMigrations] Executed DB migrations: failure | time=44ms
2020.10.15 23:24:57 ERROR web[o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=105ms
2020.10.15 23:24:57 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 #4003 ‘Drop unused variation values columns in ‘project_measures’ table’ 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 com.google.common.collect.ImmutableList.forEach(ImmutableList.java:405)
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 ALTER TABLE project_measures DROP COLUMN variation_value_2, variation_value_3, variation_value_4, variation_value_5
at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:106)
at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:86)
at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:128)
at org.sonar.server.platform.db.migration.version.v85.DropUnusedVariationsInProjectMeasures.execute(DropUnusedVariationsInProjectMeasures.java:36)
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: com.microsoft.sqlserver.jdbc.SQLServerException: ALTER TABLE DROP COLUMN failed because column ‘variation_value_2’ does not exist in table ‘project_measures’.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1624)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:868)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:768)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2979)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:744)
at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:194)
at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:91)
… 14 common frames omitted