SQ 7.2. DB schema migration fails

upgrade
sonarqube

(Bert) #1

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 :slight_smile:

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.


(Julien Lancelot) #2

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


(Bert) #3

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


(Bert) #4

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


(Bert) #5

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


(Geert van Horrik) #6

Having the same issue here:

  1. We had duplicate user names (1 disable, 1 enabled), which we had to delete first
  2. 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:

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

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


(Bert) #7

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.


(Bert) #8

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.


(Julien Lancelot) #9

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


Upgrade from 6.7 to 7.3 failed
(Bert) #10

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


(Julien Lancelot) #11

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) #12

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.


(Julien Lancelot) #13

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


SonarQube 7.3 database upgrade failed on #2114 'Add unique indexes on table users'