DB Migration error from SQ 7.9.6 to 8.7 with Microsoft SQL

Hi!
We are trying to upgrade SonarQube

SonarQube versions

  • From 7.9.6 to 8.7

DB is MSSQL:

  • Version: 13.00.5102
  • Driver: Microsoft JDBC Driver 7.2 for SQL Server
  • Driver versión: 7.2.2.0
  • It was created to connect with a SonarQube 5.6.7 (the first version of SonarQube we used)

Error observed (web.log):

2021.03.22 13:58:02 INFO  web[][o.s.s.p.Platform] Database needs to be migrated. Please refer to https://docs.sonarqube.org/latest/setup/upgrading
2021.03.22 13:58:02 INFO  web[][o.s.s.p.w.MasterServletFilter] Initializing servlet filter org.sonar.server.platform.web.WebServiceFilter@1257d0f3 [pattern=UrlPattern{inclusions=[/api/system/migrate_db.*, ...], exclusions=[/api/components/update_key, ...]}]
2021.03.22 13:58:02 INFO  web[][o.s.s.a.EmbeddedTomcat] HTTP connector enabled on port 80
2021.03.22 14:00:28 INFO  web[][o.s.s.p.d.m.DatabaseMigrationImpl] Starting DB Migration and container restart
2021.03.22 14:00:28 INFO  web[][DbMigrations] Executing DB migrations...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3000 'Set Organizations#guarded column nullable'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3000 'Set Organizations#guarded column nullable': success | time=47ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3001 'Create ProjectQualityGates table'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3001 'Create ProjectQualityGates table': success | time=62ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3002 'Make index on DEPRECATED_RULE_KEYS.RULE_ID non unique'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3002 'Make index on DEPRECATED_RULE_KEYS.RULE_ID non unique': success | time=33ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3003 'Populate ProjectQualityGate table from Properties table'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3003 'Populate ProjectQualityGate table from Properties table': success | time=47ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3004 'Rename ANALYSIS_PROPERTIES.SNAPSHOT_UUID to ANALYSIS_UUID'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3004 'Rename ANALYSIS_PROPERTIES.SNAPSHOT_UUID to ANALYSIS_UUID': success | time=173ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3005 'Remove default quality gate property from Properties table'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3005 'Remove default quality gate property from Properties table': success | time=15ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3006 'Create NEW_CODE_PERIOD table'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3006 'Create NEW_CODE_PERIOD table': success | time=16ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3007 'Populate NEW_CODE_PERIOD table'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3007 'Populate NEW_CODE_PERIOD table': success | time=63ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3008 'Remove leak period properties'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3008 'Remove leak period properties': success | time=16ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3009 'Remove GitHub login generation strategy property'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3009 'Remove GitHub login generation strategy property': success | time=15ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3100 'Create ALM_SETTINGS table'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3100 'Create ALM_SETTINGS table': success | time=31ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3101 'Create PROJECT_ALM_SETTINGS table'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3101 'Create PROJECT_ALM_SETTINGS table': success | time=16ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3102 'Migrate property 'sonar.alm.github.app.privateKey.secured' to 'sonar.alm.github.app.privateKeyContent.secured''...
2021.03.22 14:00:28 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 0 rows processed (0 items/sec)
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3102 'Migrate property 'sonar.alm.github.app.privateKey.secured' to 'sonar.alm.github.app.privateKeyContent.secured'': success | time=48ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3103 'Migrate GitHub ALM settings from PROPERTIES to ALM_SETTINGS tables'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3103 'Migrate GitHub ALM settings from PROPERTIES to ALM_SETTINGS tables': success | time=16ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3104 'Migrate Bitbucket ALM settings from PROPERTIES to ALM_SETTINGS tables'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3104 'Migrate Bitbucket ALM settings from PROPERTIES to ALM_SETTINGS tables': success | time=16ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3105 'Migrate Azure ALM settings from PROPERTIES to ALM_SETTINGS tables'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3105 'Migrate Azure ALM settings from PROPERTIES to ALM_SETTINGS tables': success | time=31ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3106 'Delete 'sonar.pullrequest.provider' property'...
2021.03.22 14:00:28 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 0 rows processed (0 items/sec)
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3106 'Delete 'sonar.pullrequest.provider' property': success | time=15ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3107 'Migrate default branches to keep global setting'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3107 'Migrate default branches to keep global setting': success | time=63ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3108 'Add EXCLUDE_FROM_PURGE column'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3108 'Add EXCLUDE_FROM_PURGE column': success | time=31ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3109 'Populate EXCLUDE_FROM_PURGE column'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3109 'Populate EXCLUDE_FROM_PURGE column': success | time=16ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3110 'Remove 'sonar.branch.longLivedBranches.regex''...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3110 'Remove 'sonar.branch.longLivedBranches.regex'': success | time=15ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3111 'Rename 'sonar.dbcleaner.daysBeforeDeletingInactiveShortLivingBranches' setting'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3111 'Rename 'sonar.dbcleaner.daysBeforeDeletingInactiveShortLivingBranches' setting': success | time=16ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3112 'Migrate short and long living branches types to common BRANCH type'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3112 'Migrate short and long living branches types to common BRANCH type': success | time=16ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3113 'Migrate short and long living branches types to common BRANCH type in ce tasks table'...
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3113 'Migrate short and long living branches types to common BRANCH type in ce tasks table': success | time=15ms
2021.03.22 14:00:28 INFO  web[][DbMigrations] #3200 'Drop 'In Review' Security Hotspots status '...
2021.03.22 14:00:29 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 0 rows processed (0 items/sec)
2021.03.22 14:00:29 INFO  web[][DbMigrations] #3200 'Drop 'In Review' Security Hotspots status ': success | time=111ms
2021.03.22 14:00:29 INFO  web[][DbMigrations] #3201 'Migrate Manual Vulnerabilities to Security Hotspots '...
2021.03.22 14:00:29 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 0 rows processed (0 items/sec)
2021.03.22 14:00:29 INFO  web[][DbMigrations] #3201 'Migrate Manual Vulnerabilities to Security Hotspots ': success | time=31ms
2021.03.22 14:00:29 INFO  web[][DbMigrations] #3202 'Remove 'newsbox.dismiss.hotspots' user property'...
2021.03.22 14:00:29 INFO  web[][DbMigrations] #3202 'Remove 'newsbox.dismiss.hotspots' user property': success | time=16ms
2021.03.22 14:00:29 INFO  web[][DbMigrations] #3203 'Ensure Security Hotspots have status TO_REVIEW'...
2021.03.22 14:00:29 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 0 rows processed (0 items/sec)
2021.03.22 14:00:29 INFO  web[][DbMigrations] #3203 'Ensure Security Hotspots have status TO_REVIEW': success | time=31ms
2021.03.22 14:00:29 INFO  web[][DbMigrations] #3204 'Rename table 'PROJECTS' to 'COMPONENTS''...
2021.03.22 14:00:29 INFO  web[][DbMigrations] #3204 'Rename table 'PROJECTS' to 'COMPONENTS'': success | time=0ms
2021.03.22 14:00:29 INFO  web[][DbMigrations] #3205 'Add PROJECTS table'...
2021.03.22 14:00:29 INFO  web[][DbMigrations] #3205 'Add PROJECTS table': success | time=16ms
2021.03.22 14:00:29 INFO  web[][DbMigrations] #3206 'Populate PROJECTS table'...
2021.03.22 14:00:29 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 45 projects processed (0 items/sec)
2021.03.22 14:00:29 INFO  web[][DbMigrations] #3206 'Populate PROJECTS table': success | time=47ms
2021.03.22 14:00:29 INFO  web[][DbMigrations] #3207 'Drop 'TAGS' column from COMPONENTS table'...
2021.03.22 14:00:29 INFO  web[][DbMigrations] #3207 'Drop 'TAGS' column from COMPONENTS table': success | time=15ms
2021.03.22 14:00:29 INFO  web[][DbMigrations] #3208 'Remove old Security Review Rating measures'...
2021.03.22 14:00:31 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 45 rows processed (0 items/sec)
2021.03.22 14:00:34 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 45 rows processed (0 items/sec)
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3208 'Remove old Security Review Rating measures': success | time=5127ms
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3209 'Create ALM_PATS table'...
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3209 'Create ALM_PATS table': success | time=16ms
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3210 'Add index on ALM_slug'...
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3210 'Add index on ALM_slug': success | time=47ms
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3211 'Delete conditions using 'security_hotspots' and 'new_security_hotspots' metrics'...
2021.03.22 14:00:34 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 0 rows processed (0 items/sec)
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3211 'Delete conditions using 'security_hotspots' and 'new_security_hotspots' metrics': success | time=16ms
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3300 'Add 'summary_comment_enabled' boolean column to 'project_alm_settings''...
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3300 'Add 'summary_comment_enabled' boolean column to 'project_alm_settings'': success | time=0ms
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3301 'Enable 'summary_comment_enabled' for GitHub based projects'...
2021.03.22 14:00:34 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 0 rows processed (0 items/sec)
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3301 'Enable 'summary_comment_enabled' for GitHub based projects': success | time=15ms
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3302 'Add 'component_uuid' column to 'properties''...
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3302 'Add 'component_uuid' column to 'properties'': success | time=16ms
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3303 'Migrate 'resource_id' to 'component_uuid' in 'properties''...
2021.03.22 14:00:34 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 6 rows processed (0 items/sec)
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3303 'Migrate 'resource_id' to 'component_uuid' in 'properties'': success | time=31ms
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3304 'Remove column 'resource_id' in 'properties''...
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3304 'Remove column 'resource_id' in 'properties'': success | time=0ms
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3305 'Add 'component_uuid' column to 'group_roles''...
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3305 'Add 'component_uuid' column to 'group_roles'': success | time=34ms
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3306 'Migrate 'resource_id' to 'component_uuid' in 'group_roles''...
2021.03.22 14:00:34 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 405 rows processed (6 items/sec)
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3306 'Migrate 'resource_id' to 'component_uuid' in 'group_roles'': success | time=129ms
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3307 'Remove column 'resource_id' in 'group_roles''...
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3307 'Remove column 'resource_id' in 'group_roles'': success | time=35ms
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3308 'Add 'component_uuid' column to 'user_roles''...
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3308 'Add 'component_uuid' column to 'user_roles'': success | time=16ms
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3309 'Migrate 'resource_id' to 'component_uuid' in 'user_roles''...
2021.03.22 14:00:34 INFO  web[][o.s.s.p.d.m.s.MassUpdate] 0 rows processed (0 items/sec)
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3309 'Migrate 'resource_id' to 'component_uuid' in 'user_roles'': success | time=15ms
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3310 'Remove column 'resource_id' in 'user_roles''...
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3310 'Remove column 'resource_id' in 'user_roles'': success | time=16ms
2021.03.22 14:00:34 INFO  web[][DbMigrations] #3311 'Remove column 'id' in 'components''...
2021.03.22 14:00:35 ERROR web[][DbMigrations] #3311 'Remove column 'id' in 'components'': failure | time=312ms
2021.03.22 14:00:35 ERROR web[][DbMigrations] Executed DB migrations: failure | time=6956ms
2021.03.22 14:00:35 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=7022ms
2021.03.22 14:00:35 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 #3311 'Remove column 'id' in 'components'' 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 components 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.v83.DropIdFromComponentsTable.execute(DropIdFromComponentsTable.java:43)
	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 'idx_2020_06_10_182_181' 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

Potential workaround
We saw this bug solved (https://jira.sonarsource.com/browse/SONAR-13948), so we tried to upgrade to 8.6 too, but we obtain the same error.


Do you know what’s the problem?

Thank you so much

King regards

Hi,

There was a bug fix version - 8.7.1 - to address some migration errors, and 8.8 has since been released. Can you try one of them & report back, please?

 
Ann

Thank you Ann,
I will try as soon as possible and let you know.

Kind regards.