Unique ID Requirement in SQ 7.2

[srvrguy]

The newly released version 7.2 of SonarQube seems to fail on the db migration when you have AAD users. Specifically, it seems to now require that the external_identity column in the users table be unique. This column is set to "Azure AD" for every AAD login, which makes the index creation fail.

[Sylvainbeqom]

Hello,

We are facing the same problem when trying to migrate from 7.0 to 7.2 version of SonarQube.

Potential SonarQube improvement ticket that is causing the issue => https://jira.sonarsource.com/browse/SONAR-10599

Here is the log of the error:

2018.06.20 07:33:52 INFO web[][DbMigrations] #2114 'Add unique indexes on table users'...
2018.06.20 07:33:52 ERROR web[][DbMigrations] #2114 'Add unique indexes on table users': failure | time=94ms
2018.06.20 07:33:52 ERROR web[][DbMigrations] Executed DB migrations: failure | time=78575ms
2018.06.20 07:33:52 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=78653ms
2018.06.20 07:33:52 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
2018.06.20 08:37:13 ERROR web[][o.s.s.w.WebServiceEngine] Fail to process request http://localhost:9000/api/system/db_migration_status
java.lang.IllegalStateException: Failed to read content of table schema_migrations
at org.sonar.server.platform.db.migration.history.MigrationHistoryImpl.getLastMigrationNumber(MigrationHistoryImpl.java:72)
at org.sonar.server.platform.db.migration.version.DatabaseVersion.getVersion(DatabaseVersion.java:51)
at org.sonar.server.platform.ws.DbMigrationStatusAction.handle(DbMigrationStatusAction.java:68)
at org.sonar.server.ws.WebServiceEngine.execute(WebServiceEngine.java:110)
at org.sonar.server.ws.WebServiceFilter.doFilter(WebServiceFilter.java:88)
at org.sonar.server.platform.web.MasterServletFilter$GodFilterChain.doFilter(MasterServletFilter.java:126)
at org.sonar.server.platform.web.MasterServletFilter.doFilter(MasterServletFilter.java:95)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.sonar.server.user.UserSessionFilter.doFilter(UserSessionFilter.java:87)
at org.sonar.server.user.UserSessionFilter.doFilter(UserSessionFilter.java:71)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.sonar.server.platform.web.CacheControlFilter.doFilter(CacheControlFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.sonar.server.platform.web.SecurityServletFilter.doHttpFilter(SecurityServletFilter.java:76)
at org.sonar.server.platform.web.SecurityServletFilter.doFilter(SecurityServletFilter.java:48)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.sonar.server.platform.web.RedirectFilter.doFilter(RedirectFilter.java:61)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.sonar.server.platform.web.requestid.RequestIdFilter.doFilter(RequestIdFilter.java:58)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.sonar.server.platform.web.RootFilter.doFilter(RootFilter.java:62)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
at ch.qos.logback.access.tomcat.LogbackValve.invoke(LogbackValve.java:256)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)

[Kormyr]

Same issue here.
It is very annoying, but fortunately this is happening during our investigations on which product to use.
So we may commit to another solution that has a better support for AAD auth with less regressions.

[srvrguy]

I'm actually working on a patch right now that should resolve the issue. I'll submit it here as soon as I have it tested a bit more.

[Kormyr]

Thanks srvrguy for the fix.

When can we expect it to be released?

[srvrguy]

I'm not sure when an official build will be made, but if you need it in a pinch, you can always try building the software yourself or I can provide an unofficial binary.

[GeertvanHorrik]

@srvrguy is there a way to download the artifacts for any build from Travis? I am looking for a hotfixed version so we can continue using this login plugin, would be happy even if it's an unofficial one.

Thanks!

[Sylvainbeqom]

@srvrguy
Thank you for the fix.

It would be good if we can have a an unofficial version of this plugin to download. Then we can test again to upgrade to the latest version of SonarQube and see if everything works fine now.

Thanks again for your help.

[Kormyr]

Thanks, @srvrguy

I wonder when another version will be released as it seems this plugin is kind of dead.
Which explains why we are facing this kind of issues and the lack of reactivity on Sonarqube team part.

It would be very kind to provide a way to get this unofficial build. I will then try to convince my Information Security team to accept the use of this plugin.

I really want to push for the use of Sonar in my company and do everything I can but Sonarqube team is not really helping on this...

[srvrguy]

There isn't a way to get the build artifacts from Travis-CI. There is a way to configure an upload to S3 during the run, but no way after the fact.

If you trust that I'm not malicious, you can download an unofficial build here. It's built from commit eedb377. Alternately, you can compile it yourself. It's not too complex to do via maven directly or via a maven docker image.

One note: If you're upgrading to 7.2 and already have users via this plugin, you either need to install this on your current system and have everyone log in (so their data is updated), or manually update the users table to ensure that the schema upgrade will succeed. If you have questions about how to do this, let me know.

Also, this plugin isn't managed by SonarSource, the company behind SonarQube. It's a community-contributed plugin and is only supported by the maintainer of this repository.

[GeertvanHorrik]

Excellent. Just did a quick check, all looks good, thanks a lot!

I've noticed 2 things:

1. Users seems to be displayed twice in Sonar (but if I edit 1, it will edit both). I double checked the db, there is just 1 entry
2. It still contains a few test dependencies, but it works so it's all good for me :-)

Thanks!

[srvrguy]

Odd, I never ran into a duplicate user issue in my testing. The plugin shouldn't manipulate that area either, which makes it even weirder.

The plugin is basically an OAuth2 provider, and adds entries (if you allow sign-up) and sets the external_login and external_identity_provider columns to mark the "owner" of the account. It'll use "aad" for external_identity_provider rather than "sonarqube" and will use the user's e-mail address for "external_login". The value for "login" will depend on the login strategy you choose (it'll use the e-mail address with "@aad" appended for unique and the e-mail address by itself for same as). Other than that during login, SQ itself handles all the user display features.

Not sure what you mean by test dependencies.

[GeertvanHorrik]

For example the mockito-extensions? It wasn't part of the original package.

Also pom.xml contains several test dependencies.

[srvrguy]

I see what you mean now.

That mockito-extensions folder won't bother anything, but I can submit a PR to move it where it won't be included in the package.

The pom.xml will contain packages with a test scope, that's normal. I suppose this could be configured so that none of the maven meta-inf contents are included if it's a big concern.

If you would like those cleaned up, can you open a new issue? I'll make some PRs against them then.

[Kormyr]

Thanks a lot for your help on this issue @srvrguy

Also, this plugin isn't managed by SonarSource, the company behind SonarQube. It's a community-contributed plugin and is only supported by the maintainer of this repository.

I completely understand this but when a company is advertising on its website that something is compatible it should be at least tested, even if it was not developed internally.

https://docs.sonarqube.org/display/PLUG/Plugin+Version+Matrix