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

ariv1983 · October 12, 2020, 4:10am

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

pierreguillot · October 12, 2020, 7:52am

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;

ariv1983 · October 12, 2020, 8:16am

Thanks much. We will try it out!

ariv1983 · October 12, 2020, 11:51am

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’.

IGx89 · October 12, 2020, 1:25pm

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

IGx89 · October 12, 2020, 1:27pm

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

IGx89 · October 12, 2020, 1:40pm

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

ariv1983 · October 13, 2020, 5:07am

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’.

pierreguillot · October 13, 2020, 7:06am

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

ariv1983 · October 13, 2020, 8:47am

Cool - Thanks for your reply!

ashwinb · October 18, 2020, 11:20am

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