Error upgrading from SonarQube 6.7 (or 6.7.5) to 7.3

[ericlemes]

Hi there,

When upgrading to SonarQube 7.3, there's an issue with one of the indexes that are created. The index expects that the columns external_identity_provider and external_id are unique, but the plugin fills external_id with "Azure AAD" for all the users. Here's the log:

2018.08.22 11:36:59 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 #2114 'Add unique indexes on table users' 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 java.lang.Iterable.forEach(Unknown Source)
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.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.IllegalStateException: Fail to execute CREATE UNIQUE INDEX uniq_external_id ON users (external_identity_provider, external_id)
at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:97)
at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:77)
at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:117)
at org.sonar.server.platform.db.migration.version.v72.AddUniqueIndexesOnUsers.execute(AddUniqueIndexesOnUsers.java:45)
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 CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.users' and the index name 'uniq_external_id'. The duplicate key value is (aad, Azure AD).
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:845)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:752)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:729)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:82)
... 14 common frames omitted

[ericlemes]

I noticed that this is already fixed in a commit from June. Are you planning to release?

[PSCNed]

Any news on this one? This prevents us from upgrading to Sonarqube 7 at this moment.

[srvrguy]

Not an official build, but I linked my updated version over in #40 and it works with the latest SonarQube.

Note that before you upgrade, you'll need to manually fix the 'users' database table to assign a unique value to the 'external_login' column. As this is only a display column, it's a safe operation to make. Once the users log in again, the column contents will be changed to the user's AAD login (their e-mail address).

This is a sample query for PostgreSQL to make the needed changes to the table. You can likely adapt this to the DBMS that you're using. Obviously, make a full backup of the database before making any changes.

UPDATE users
SET external_login = md5(random()::text)
WHERE external_identity_provider = 'aad';

[abuis]

I noticed that the latest release is from 2.5 years ago... is there any intention to create a new release anytime?

[srvrguy]

Yes, a new release is being worked on. As far as I know, @hkamel is testing the latest build and if it works good for them, it'll be published. If you need newer code or support for SQ7, then you can try my temporary build linked in #40 until an official release is posted.

[abuis]

@srvrguy - Thanks for the update!

[hkamel]

A new draft release has been published 1.1-RC2 the announcement has been sent to the official SonarSource community to kick the official release.

Thank you everyone for your contributions and @srvrguy big thank you for you great efforts and contributions

[shubhamgoel4aug]

Thanks @hkamel , was trying the upgrade from 6.7.4 to 7.4 with the comment by @srvrguy . I just checked the db created by v6.7.4 which seem to not have the column external_login in the users table.

Please suggest on this whether should we do a error out upgrade and then apply db change suggested by @srvrguy or we need to update some other db column.

Currently my db's users table contains the following columns: (Just wondering if external_identity column will do the same)

USE [Sonar7.4]
GO

/****** Object: Table [dbo].[users] Script Date: 11/23/2018 11:04:45 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[users](
[id] [int] IDENTITY(1,1) NOT NULL,
[login] nvarchar NULL,
[name] nvarchar NULL,
[email] nvarchar NULL,
[crypted_password] nvarchar NULL,
[salt] nvarchar NULL,
[active] [bit] NULL DEFAULT ((1)),
[created_at] [bigint] NULL,
[updated_at] [bigint] NULL,
[scm_accounts] nvarchar NULL,
[external_identity] nvarchar NULL,
[external_identity_provider] nvarchar NULL,
[user_local] [bit] NULL,
[is_root] [bit] NOT NULL,
[onboarded] [bit] NOT NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

[baywet]

Ran into the same issue what did the trick for me:

• ran the following query

DROP INDEX [users_uuid]
    ON [dbo].[users]

(sql server)

• then in the users table, replace the Azure AD value in the external id column by the email address.
• got the RC 1.1 from the releases here and updated it in the plugins folder
• removed the following plugins that were incompatible for the time of the migration (sonar-java-plugin-5.6.0.15032.jar, sonar-scm-git-plugin-1.4.1.1128.jar, sonar-csharp-plugin-7.3.0.5690.jar, sonar-sonargraph-integration-2.1.5.jar, sonar-sonargraph-plugin-3.5.jar, sonar-scm-tfvc-plugin-2.1.2.jar)
• run the upgrade again (/setup)

[philippesollaud]

Hi @hkamel,
Do you have a release date for the version 1.1 ?

[srvrguy]

It will probably be released in the next few months. I want to get #35 fixed first, and then remaining issues can be saved for a 1.2 or 1.1.x release depending on severity.

[ganncamp]

@hkamel you posted the RFF, but never the release of this, so the Marketplace hasn't been updated. Was that overlooked?

[srvrguy]

I think it was an oversight, as the process to update the marketplace files wasn't in place at that time. I'm working on getting the final 1.2 release posted currently, I just have been a bit busy with day job stuff.