1

Need to know how we can migrate data post change of database collation change. Basically, below is what I am planning.

  1. Get a new DB with new collation which is a requirement for Sonar (Latin1_General_CS_AS). Collation on our existing DB is - Latin1_General_CI_AS
  2. Setup the Sonar service and point to the new database with new collation. This will create new tables required for Sonar on the database.
  3. Migrate data from old database to new database without changing collation.

I can import data using SELECT INSERT but I ran into issue where Sonar service didn't start post this.(SonarQube service not starting)

We had to truncate and drop tables and re-create them. Can someone advise how we can migrate data?

I am aware of Sonar DB copy tool but I believe this is only for Enterprise version. (http://docs.sonarqube.org/display/SONAR/Sonar+DB+Copy+Tool)

Community
  • 1
  • 1
AmitP
  • 21
  • 4
  • if the db is MSSQL you have the option to change the collation of the DB. is this a viable path? – Paolo May 31 '16 at 12:35
  • which initial problem do you try to solve ? – Simon Brandhof May 31 '16 at 15:33
  • hi Paolo, yes this is MSSQL. Basically the collation can be changed by database properties but I think this will not change the collation of the tables. Also, we need to recreate the index if any. Hence, a fresh database with new collation was planned. – AmitP May 31 '16 at 17:03
  • hi Simon, kindly refer (http://stackoverflow.com/questions/36868433/sonarqube-service-not-starting) and (http://stackoverflow.com/questions/35827598/duplicate-key-error-post-sonarqube-5-3-upgrade) – AmitP May 31 '16 at 17:04
  • On test environment, we created a new DB with new collation. Pointed it in Sonar config which created tables. We dropped and re-created tables. Copied table data using SELECT INSERT from exisitng DB to new DB. Sonar is UP and running but we do not see projects. I see this mesage - "The widget cannot be displayed, because all components don't have the size measure." – AmitP Jun 01 '16 at 15:41
  • I see below error in sonar log: 2016.06.01 18:06:13 ERROR web[o.s.s.ui.JRubyFacade] Fail to render: http:///dashboard/index/6608 undefined method `project' for nil:NilClass – AmitP Jun 01 '16 at 17:10

1 Answers1

2

This solution might help you. Basically, it exports information between databases and / or DBMS using simple scripts. BTW, it avoids duplication if projects already exist in the destination.

https://github.com/awltech/sonar-data-migrator

To use, follow below steps.

  • Download (or clone) git project
  • Run maven install on the project, it will create bineries zip under target/assemly folder.
  • Extract zip on machine from where you have access to both sonar server DB.
  • Do sonar analysis on both source and target sonar with same code.
  • Configure source and target db details in database.properties file
  • Run init.sql on target sonar db, it will create temporary tables to store migrated data details
  • Set proper java path in script.bat file (you can use script.sh for executing on linux) .

Usage

  • Run the bat/sh script passing users as argument for migration of users
  • Run the bat/sh script passing data as first argument and project key as per "Key" value in sonar report as second argument. If key is not passed, it will work for all projects in db.

This script will migrate below things:

  • Users (default sonar-users role is assigned to all migrated users)
  • Action plans associated to the project
  • All changes to issue like assignee,severity,status,action plan and resolution
  • All reviews/comments added to issue
  • All false positive data

My migration was similar to yours (two databases in PostGre), step by step worked! :)

Jean-François Fabre
  • 137,073
  • 23
  • 153
  • 219
Rafael Pizao
  • 742
  • 8
  • 21