Migration error when upgrading to 9.7.1

Hi, i try to upgrade from 8.9.10 LTS to 9.7.1

I got this DB Migration error , any hints?

org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #6006 'Drop 'user_managed' column from 'metrics' 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:422)
	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:66)
	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:829)
Caused by: java.lang.IllegalStateException: Fail to execute ALTER TABLE metrics DROP CONSTRAINT DF__metrics__user_ma__084B3915
	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.step.DropColumnWithConstraint.execute(DropColumnWithConstraint.java:46)
	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: 'DF__metrics__user_ma__084B3915' is not a constraint.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1673)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:907)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:802)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7627)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3912)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:268)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:242)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:775)
	at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
	at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
	at org.sonar.server.platform.db.migration.step.DdlChange$ContextImpl.execute(DdlChange.java:91)
	... 14 common frames omitted

Hello,

Between 8.9 and 9.7, we dropped the column user_managed from the metrics table. This process is handled automatically by SonarQube and in the case of Microsoft SQL server, we need to drop constraints of the column before. This is done by introspecting the schema to find the default constraints placed on this column and attempting to drop them, this should not generate an error.

I’m not sure why it’s not working in your case, do you use a supported version of Microsoft SQL server?
Are you able to validate the existence of DF__metrics__user_ma__084B3915 as a constraint on the metrics table?

SonarQube uses the following script to find the constraint to drop (use the correct default schema):

SELECT d.name FROM sys.tables t
JOIN sys.default_constraints d ON d.parent_object_id = t.object_id
JOIN sys.columns c ON c.object_id = t.object_id AND c.column_id = d.parent_column_id
WHERE  t.name = 'metrics' AND c.name in ('user_managed');

If you have more information about your issue, let me know so I can try to help you further.

1 Like

Hi ,

I ran the query an i got

DF__metrics__user_ma__084B3915
DF__metrics__user_ma__1332DBDC

Looks correct to me

I use Azure SQL,

After manually deleting the constraint - it does work

ALTER TABLE dbo.metrics 
DROP CONSTRAINT DF__metrics__user_ma__084B3915;  
GO

The SQL Server Version is Microsoft SQL Server 12.00.816