Upgrade to SQ 7.4 failed at DB upgrade phase

My upgrade from SQ 7.3 to 7.4 failed at database upgrade phase(triggered by hitting \setup url) SonarQube log produced the following.

To resolve this, I droppped organization_key from sonar DB(which is in SQL Server), completed the upgrade and recreated this index. But sonar application is now logging HTTP 500 errors to browser console:

Failed to load resource: the server responded with a status of 500 ()
The request that causes this log is like:
/api/ce/component?componentKey=<my_project_key>

2018.11.06 10:05:17 INFO  web[][o.s.s.p.w.MasterServletFilter] Initializing servlet filter org.sonar.server.ws.WebServiceFilter@662f7274 [pattern=UrlPattern{inclusions=[/api/system/migrate_db.*, ...], exclusions=[/api/properties*, ...]}]
2018.11.06 10:05:17 INFO  web[][o.s.s.a.EmbeddedTomcat] HTTP connector enabled on port 9000
2018.11.06 10:05:19 INFO  web[][o.s.s.p.d.m.DatabaseMigrationImpl] Starting DB Migration and container restart
2018.11.06 10:05:19 INFO  web[][DbMigrations] Executing DB migrations...
2018.11.06 10:05:19 INFO  web[][DbMigrations] #2321 'Increase organization key and name length'...
2018.11.06 10:05:19 ERROR web[][DbMigrations] #2321 'Increase organization key and name length': failure | time=0ms
2018.11.06 10:05:19 ERROR web[][DbMigrations] Executed DB migrations: failure | time=0ms
2018.11.06 10:05:19 ERROR web[][o.s.s.p.d.m.DatabaseMigrationImpl] DB migration failed | time=93ms
2018.11.06 10:05:19 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 #2321 'Increase organization key and name length' 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 ALTER TABLE organizations ALTER COLUMN kee NVARCHAR (255) NOT NULL
	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.v74.IncreaseOrganizationsKeeAndNameLength.execute(IncreaseOrganizationsKeeAndNameLength.java:41)
	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 index 'organization_key' is dependent on column 'kee'.
	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.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:175)
	at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:175)
	at org.sonar.server.platform.db.migration.step.DdlChange$Context.execute(DdlChange.java:82)
	... 14 common frames omitted
2018.11.06 10:06:10 INFO  web[][o.s.p.StopWatcher] Stopping process

Hi,

I’m not able to reproduce this issue, could you please provided the version of your SQL server ?
Could you also provide the server’s log when executing web service /api/ce/component ?

Thanks.

We are using SQL Server 2017 and SQL complains about the collation. SQ log shows that it tries to run the following query and gets the collation mismatch between ctm.task_uuid and ca.uuid because one of them is collated Turkish_100_CS_AS and the other is Turkish_CS_AS.

In the end, I dropped the DB and created a fresh DB to resolve the error. Because I don’ t need historic data about the analyses, it wasn’ t a problem.

Thank you

select ca.id, ca.uuid, ca.task_type as taskType, ca.component_uuid as componentUuid, ca.main_component_uuid as mainComponentUuid, ca.analysis_uuid as analysisUuid, ca.status as status, ca.submitter_uuid as submitterUuid, ca.submitted_at as submittedAt, ca.worker_uuid as workerUuid, ca.started_at as startedAt, ca.executed_at as executedAt, ca.created_at as createdAt, ca.updated_at as updatedAt, ca.is_last as isLast, ca.is_last_key as isLastKey, ca.main_is_last as mainIsLast, ca.main_is_last_key as mainIsLastKey, ca.execution_time_ms as executionTimeMs, ca.error_message as errorMessage, ca.error_type as errorType, cast(case when csc.task_uuid is null then 0 else 1 end as bit) as hasScannerContext , ca.error_stacktrace as errorStacktrace, (select count(1) from ce_task_message ctm where ctm.task_uuid = ca.uuid) as warningCount from ce_activity ca left outer join ce_scanner_context csc on ca.uuid = csc.task_uuid where ca.uuid=?

Alpay,

Please note from our Requirements documentation that SQL Server 2017 is not a supported/tested database version. https://docs.sonarqube.org/latest/requirements/requirements/

Colin

By doing it, did you need to drop the index in organizations table ?

Actually, that index prevented me to upgrade SQ to the new version, that’ s why I had to drop and recreate the index. However, after the completion of upgrade, SQ wasn’ t functioning as it should, so I had to drop the whole DB and create it from the scratch.

I didn’ t drop the index from organizations table after this operation. SQ seems to work correctly now.

Ok, thanks for this confirmation.