Migration from 8.2.0.32929 to 8.9.4.50575

[DEYS (Paul Binnell)]

I am trying to upgrade from 8.2.0.32929 to 8.9.4.50575 but get the following error when running the DB migration. Any ideas.

2021.12.15 13:59:16 INFO  web[][DbMigrations] Executing DB migrations...
2021.12.15 13:59:16 INFO  web[][DbMigrations] #3414 'Add primary key on 'UUID' column of 'CE_QUEUE' table'...
2021.12.15 13:59:16 ERROR web[][DbMigrations] #3414 'Add primary key on 'UUID' column of 'CE_QUEUE' table': failure | time=23ms
2021.12.15 13:59:16 ERROR web[][DbMigrations] Executed DB migrations: failure | time=26ms
2021.12.15 13:59:16 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=135ms
2021.12.15 13:59:16 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 #3414 'Add primary key on 'UUID' column of 'CE_QUEUE' 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 ce_queue ADD CONSTRAINT pk_ce_queue PRIMARY KEY (uuid)
	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.version.v84.cequeue.AddPrimaryKeyOnUuidColumnOfCeQueueTable.execute(AddPrimaryKeyOnUuidColumnOfCeQueueTable.java:35)
	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: Cannot define PRIMARY KEY constraint on nullable column in table 'ce_queue'.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:872)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:767)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3274)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:743)
	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)
	... 13 common frames omitted

[Lukasz_Jarocki (Łukasz Jarocki)]

Warm welcome @DEYS

Based on the stacktrace above we think that for some reason, unexpectedly your UUID column in the CE_QUEUE table is nullable. This is not expected by SonarQube. Do you have any knowledge of anyone doing any manual DDL changes to your database?

To further troubleshoot the issue could you run the following queries on your database?

select table_name, column_name, is_nullable, data_type 
from INFORMATION_SCHEMA.columns 
where table_name = 'ce_queue'

and

select count(*) from ce_queue where uuid is null

The results of these queries won’t contain any sensitive information and will help with resolving the issue.

Thank you

[DEYS (Paul Binnell)]

Lukasz, No changes were made by anyone to the database. I was able to fix this issues, and another I found with the following SQL and continued the upgrade with no issues.

DROP INDEX [ce_activity_uuid] ON [dbo].[ce_activity]
GO

ALTER TABLE [dbo].[ce_queue]
ALTER COLUMN [uuid] NVARCHAR(40) NOT NULL

ALTER TABLE [dbo].[ce_activity]
ALTER COLUMN [uuid] NVARCHAR(40) NOT NULL

CREATE UNIQUE NONCLUSTERED INDEX [ce_activity_uuid] ON [dbo].[ce_activity]
(
[uuid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO