SQ 7.2. DB schema migration fails

Bertk · June 19, 2018, 11:29am

The SQ 7.2. DB schema migration failed with an exception:

    2018.06.19 10:21:49 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.util.Iterator.forEachRemaining(Unknown Source)
                    at java.util.Spliterators$IteratorSpliterator.forEachRemaining(Unknown Source)
                    at java.util.stream.ReferencePipeline$Head.forEachOrdered(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:50)
                    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)
                    ... 11 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)
                    ... 16 common frames omitted

We removed the rows with the duplicate uniq_external_id values and restarted the migration. This failed again:

    2018.06.19 11:18:11 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.util.Iterator.forEachRemaining(Unknown Source)
                    at java.util.Spliterators$IteratorSpliterator.forEachRemaining(Unknown Source)
                    at java.util.stream.ReferencePipeline$Head.forEachOrdered(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:50)
                    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 users_uuid ON users (uuid)
                    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:38)
                    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)
                    ... 11 common frames omitted
    Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The operation failed because an index or statistics with name 'users_uuid' already exists on table 'users'.
                    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)
                    ... 16 common frames omitted

Finally after removing the index the migration was successful and the SQ services were started

Some entries in the user table were not shown in the UI because the accounts were already disabled and the problematic data was created with the Azure Active Directory plug-in.

julienlancelot · June 19, 2018, 12:48pm

Hi Bertk,

Thanks for reporting this issue.

I have some questions to understand what was your issue and how you’ve resolved it :

Do you remember the number of duplicated users that you have removed ?
Were these users all disabled ?
Have you restore the unique index ?

Thanks.
Regards,
Julien Lancelot

Bertk · June 20, 2018, 6:52am

Hi Julian,

we removed around 12 accounts with identical uniq_external_id and all the accounts were disabled. We also removed some out-dated accounts as well.
We did not restore the unique index for users_uuid but I will check whether it exists today.

Best regards
Bert

Bertk · June 21, 2018, 3:54pm

Hi Julian,

the index was created uniq_external_id was created automatically.

Unfortunately some users cannot login anymore and I have doubts that the AAD plug-in works with SQ7.2.
The data for new users in ‘uuid’ and ‘external_id’ columns are not OK e.g. the ‘external_id’ is always filled with ‘Azure AD’.

Best regards
Bert

Bertk · June 22, 2018, 6:55am

Hi Julian,

there are known issues for the AAD plug-in and we use the aad plugin 1.1-snapshot version (group synchronization) . I will change back to the V1.0.

Best regards
Bert

GeertvanHorrik · July 3, 2018, 9:08am

Having the same issue here:

We had duplicate user names (1 disable, 1 enabled), which we had to delete first
The AAD (Azure Active Directory) plugin uses “Azure AD” as external_id. This makes it impossible to finish the migration.

Things I did as a workaround:

Edit the external_id to the user_id for all users using Azure AD
Run the upgrade
Disable the unique constraint for the external_id
Change the values back to “Azure AD”

Unfortunately this allows users to login, but users aren’t allowed to see anything.

Bertk · July 6, 2018, 9:05am

We solved the issue differently and created an update trigger which sets the eMail address for external_id, uuid.

create TRIGGER externalIdUpdate on [dbo].[users] AFTER UPDATE
AS 
UPDATE [users] SET external_id = u.email, uuid = u.email FROM [users] u INNER JOIN inserted i ON u.id = i.id

This workaround allows us to add users without conflicts (unique constraint) but is not a solution. The user has to trigger the update setting a home page.
All users are able to access the data and we use the AAD groups to manage the permissions for the projects.

Bertk · July 14, 2018, 8:56am

I think disabling the unique constraint for external_id is a better solution after I checked the class UserIdentity https://github.com/SonarSource/sonarqube/blob/master/sonar-plugin-api/src/main/java/org/sonar/api/server/authentication/UserIdentity.java.

julienlancelot · August 3, 2018, 12:23pm

This issue is on Azure Active Directory plugin side, as it should set unique identifier as external ID, not “Azure AD”.
Please open a Github issue to solve this problem.

Thanks

Bertk · August 9, 2018, 3:25pm

Thank you for the hint. Meanwhile a PR was already merged.

julienlancelot · August 9, 2018, 3:40pm

Indeed, this issue is fixed on the master of sonar-auth-add, it only requires a release in order to use it.

Shubh_Rocks_Goel · August 20, 2018, 6:34am

Hi @julienlancelot

Is this issue resolve?
I am trying to Upgrade the database and running into the errors:

#2114 ‘Add unique indexes on table users’: failure

org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #2114 ‘Add unique indexes on table users’ failed

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The operation failed because an index or statistics with name ‘users_uuid’ already exists on table ‘users’.

I am using the Sonar version 7.2.1 which was downloaded around 2 weeks ago (Enterprise Version). Do I need to download again and start from scratch?

@Bertk If a AAD plugin upgrade or downgrade is required, then please tell how achieve that.

Please help.

julienlancelot · August 20, 2018, 8:03am

Hi,

As I’ve explained in my previous message, a release of the sonar-auth-add plugin is required.
So as long as you’re not using the new version (1.2 or 2.0 depending of what will choose the maintainers of the plugin), this issue won’t be fixed.

Regards

Anderson_Leite · October 30, 2019, 2:35pm

Take a look into: https://github.com/hkamel/sonar-auth-aad/wiki/Upgrading