Upgrading SonarQube from 5.1 to 6.0

SonarQube is a really nice software, but for what I experienced it does not play well with Sql Server. Even if Sql Server is fully supported, there are always some little problem in setting everything up, and this is probably due to the fact that most of the people using SonarQube are using MySql as Database Engine.

Today I was upgrading a test instance from version 5.1 to 6.0, I’ve installed the new version, launched database upgrade procedure, and after some minutes the upgrade procedure stopped with a bad error

image

Figure1: Databse error during upgrade.

Remember that SonarQube upgrade procedure does not have a rollback procedure, so it is mandatory that you take a full backup of the system before performing the upgrade.

From what I’ve learned in previous situation, whenever something does not work in SonarQube, you need to look at the log. The above error message is really misleading, because it seems that there were some connection problems. Reading the log I discovered that the error is really different.

2016.08.11 19:24:00 ERROR web[o.s.s.d.m.DatabaseMigrator] Fail to execute database migration: org.sonar.db.version.v60.CleanUsurperRootComponents
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "Latin1_General_CS_AS" in the equal to operation.

Whenever you have problem with SonarQube do not forget to read the log, because only in the log you can understand the real cause of errors.

Collation problem are quite common with SonarQube, documentation is somewhat not correct, because it tell you that you need to use an Accent and Case sensitive collation, but does not specify the collation. My SonarQube 5.1 instance worked perfectly with SQL_Latin1_General_CP1_CS_AS, but sadly enough, script to upgrade db to version 6.0 fails because it is expecting Latin1_General_CS_AS.

If you install SonarQube with Sql Server, it is better to chose Latin1_General_CS_AS as collation to avoid problems.

Luckly enough you can change database collation for an existing database, the only caveat is that you need to set the database in Single User Mode to perform this operation.

USE master;  
GO  
  
ALTER DATABASE Sonar SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
GO 

ALTER DATABASE Sonar  COLLATE Latin1_General_CS_AS ;  
GO 

ALTER DATABASE Sonar SET MULTI_USER; 
GO  

Clearly you should have a backup of your database before the migration, or you will end with a corrupted database and nothing to do. So I restored the database from the backup, run the above script, restart SonarQube and try to perform the upgrade again.

image

Figure 2: Database is upgraded correctly

My instance is now running 6.0 version.

Gian Maria.

Published by

Ricci Gian Maria

.Net programmer, User group and community enthusiast, programmer - aspiring architect - and guitar player :). Visual Studio ALM MVP

4 thoughts on “Upgrading SonarQube from 5.1 to 6.0”

  1. It didn’t solved it for me.

    if I do the collation setting with

    SELECT name, collation_name
    FROM sys.databases
    WHERE name = N’Sonarqube’;
    GO

    I do get Latin1_General_CS_AS, the collation I set to the DB but still I do find the following error in the log:

    2016.09.28 18:41:14 ERROR web[o.s.s.d.m.DatabaseMigrator] Fail to execute database migration: org.sonar.db.version.v60.CleanUsurperRootComponents
    com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between “Latin1_General_CS_AS” and “SQL_Latin1_General_CP1_CS_AS” in the equal to operation.

    Seems that the server collation needs to be changed, not DB only.
    As do not know details about the collation, do you have any other insight over this issue?

    Thanks

  2. You can ignore my message. I found out a leaner way of solving the issue. We should have followed the indicated upgrade path. I was running version 5.5, this means that I first should have updated to 5.6.x before moving to 6.0.
    After I followed this upgrade path, no issues with collation occurred.

    Cheers

  3. Sorry for late response, it is a Crazy period and I completely forgot to check the blog comment.

    You are true, I was upgrading from 5.6.x version, and I did not experiemented upgrading directly from 5.5.

    Thanks for the clarification.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.