9.5 to 9.6 MS SQL DB upgrade error [SOLVED]

SonarQube Server / Community Build
,
1

Upgrading SonarQube on Windows from 9.5 to 9.6.0.59041
DB upgrade failed:

2022.08.16 11:12:14 INFO  web[][DbMigrations] Executing DB migrations...
...
2022.08.16 11:12:14 INFO  web[][DbMigrations] #6508 'Migrate 'sonarlint_ad_seen' from users to properties': success | time=47ms
2022.08.16 11:12:14 INFO  web[][DbMigrations] #6509 'Drop column sonarlint_ad_seen in 'users''...
2022.08.16 11:12:14 ERROR web[][DbMigrations] #6509 'Drop column sonarlint_ad_seen in 'users'': failure | time=31ms
2022.08.16 11:12:14 ERROR web[][DbMigrations] Executed DB migrations: failure | time=594ms

org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #6509 'Drop column sonarlint_ad_seen in 'users'' failed
...
Caused by: java.lang.IllegalStateException: Fail to execute ALTER TABLE users DROP CONSTRAINT DF__users__sonarlint__5A4F643B`
...
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 'DF__users__sonarlint__5A4F643B' is not a constraint.

I was able to fix this issue, by connecting to the DB server and dropping the constraint myself from SSMS:

ALTER TABLE [dbo].[users] DROP CONSTRAINT [DF__users__sonarlint__5A4F643B]
GO

And then restarting the SonarQube Service and completing the DB upgrade process.

4

Thank you for sharing this valuable feedback with us, it will help us improve our next database migration. The fact that we need to explicitly drop constraint on this column before removing it was something that we actually took into consideration during the dev, we are running this SQL query to retrieve the constraint name (as the name is not predictable, we need search the system table to find it):

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 = 'users' AND c.name in ('sonarlint_ad_seen')

Any chance you have a backup of your database prior to the migration, where you could run this query and tell us what it’s returning on your dataset?

If you don’t, we have the same constraint on the the plugins.removed column. If we need to alter this column in the future, we will search the constraint name with this query:

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 = 'plugins' AND c.name in ('removed')

It would be awesome if you can give it a try on any version of your database (even in 9.6), that would help us figure out what we are missing here.

1 Like
5

Here is the result of the first query from our 9.5 database backup:

DF__users__sonarlint__29E1370A

and the second query on our 9.5 database backup:

DF__plugins__removed__1F63A897

and the second query on our 9.6 database:

DF__plugins__removed__1F63A897
DF__plugins__removed__4FD1D5C8
2 Likes
6

Thank you Brian for taking the time to run these queries. At the moment, we do not identify exactly what happened. I’ll make sure to consolidate the information you provided when we get more insight into this issue.