Migrating Sonar from Derby to MySQL

For those people who are not familiar with Sonar, it’s an opensource source code analysis tool which you can use to improve the quality of your coding. By default, Sonar comes with a simple Java Derby embedded database which is great for testing it out, but with at least 8 different projects with 25.000 – 200.000 lines of code it becomes clear that using a Derby embedded database is not a good solution. Analysis of a single project takes about 1,5 hour and these projects are growing so it’s obvious that we need a serious performance boost.
Sonar Screenshot

The great thing about Sonar is that it supports different databases by default; MySQL, DB2, PostgreSQL and MSSQL. I decided it was time to move from Derby to MySQL and so began my queeste to transfer all the data from one db to another…

It took me a while to find the great (but unfortunately not free) tool RazorSQL. This tool was one of the few ones who was able to easily make a SQL dump of a Derby database. Dumping this 500Mb large DB generated over 400.000 SQL Queries (many of them contained LOBs) and took a good two hours to complete. Using some old school

mysql -u root < dump.sql

I then tried to import the data in MySQL. Unfortunately this didn’t work since RazorSQL used the Derby SQL dialect.

After a good night sleep I went back to the internet and quickly found a great tool: openDBCopy. This tool makes it easy to migrate from one database to another !
It did require some cheating tough because Derby isn’t supported by default. So here’s a small step-by-step guide to help you migrate your Sonar database. You can find mysql.exe and mysqldump.exe in the MySQL Workbench.
Fyi: I don’t think you can ruin anything by following this guide, but everything should be done at own risk. I’m not responsible if something goes wrong.
Fyi2: this guide describes a Derby to MySQL migration for Sonar. However, it’s easy to transform this guide in a x -> y migration guide for z. Feel free to do so and don’t forget to mention me 😉 . Of course, all at own risk.

Configuring the MySQL database:
1) Execute the MySQL query you can find in Sonar\extras\database\mysql. This will set up the schema and user.
2) Change the sonar.properties file in the conf folder so that Sonar uses the MySQL database.

# Comment the 3 following lines to deactivate the default embedded database
#sonar.jdbc.url:                            jdbc:derby://localhost:1537/sonar;create=true
#sonar.jdbc.driverClassName:                org.apache.derby.jdbc.ClientDriver
#sonar.jdbc.validationQuery:                values(1)
# directory containing Derby database files. By default it's the /data directory in the sonar installation.
#sonar.embeddedDatabase.dataDir:
# derby embedded database server listening port, defaults to 1527
#sonar.derby.drda.portNumber:               1537

#----- MySQL 5.x/6.x
# Comment the embedded database and uncomment the following lines to use MySQL
sonar.jdbc.url:                            jdbc:mysql://localhost:3306/sonar?useUnicode=true&characterEncoding=utf8
sonar.jdbc.driverClassName:                com.mysql.jdbc.Driver
sonar.jdbc.validationQuery:                select 1

3) Start Sonar and verify it is working. If Sonar is up and running then it has populated the schema for you (if not, check the log files).
4) Stop Sonar and change the sonar.properties file so that it uses the derby database (we’ll need the database up and running later).
5) To prevent duplicate primary keys we’ll have to dump all the data in the MySQL database. This can be done in two steps:
a)

mysqldump.exe -u root --no-data --add-drop-table --database sonar > mysql.sql

b)

mysql.exe -u root < mysql.sql

Moving the data from derby to MySQL with openDBCopy:
1) You’ll need to add some libraries to the lib folder of openDBCopy:
derbyclient.jar
derbytools.jar
mysql.jar

You can find these libraries on the MySQL site and the Derby site or copy them from another application
2) start openDBcopy (I used v0.51rc2)
3) Plugin > Select new Plugin > copy data from a source into a destination database
4) Important step: click on * dir() and select a directory, otherwise it won’t work.
5) Make sure Sonar is running (since the Derby database needs to be running) and fill in the second tab like this:
OpenDBCopy settings for Sonar migration

Ps. the default derby port is 1527 😉
6) Click the “apply and test” buttons, you should see the connection url appear on the bottom of the application. You’ll get a pop-up only if there’s a problem.
7) The next screens are just a matter of clicking on the available buttons. Leave the mapping as it is, it should work fine.
8) On the last tab, just press execute and wait for it to finish.
9) Change the sonar.properties file so it uses the MySQL database and start Sonar.

Optional steps:
These steps are optional, but might be required

  • Update your project’s pom.xml to match the new database url and driver
  • Update Continuum integration tool (eg. Hudson)
  • Test new builds (not realy optional I guess :p )

If you forget to update the database url you’ll probably get something like this (in Maven):

Embedded error: PicoLifecycleException: method ‘public void org.sonar.jpa.session.AbstractDatabaseConnector.start()’, instance ‘org.sonar.jpa.session.DriverDatabaseConnector@168c1e1, java.lang.RuntimeException: wrapper
org.apache.derby.jdbc.ClientDriver

If everything went fine then you’ve successfully migrated the Sonar database from Derby to MySQL.

This post was originally posted on my My.Opera blog on Sunday, April 3, 2011 10:00:38 AM. This post has been copied to my WordPress blog since the My.Opera blogging service will be discontinued.

Advertisements

6 thoughts on “Migrating Sonar from Derby to MySQL

  1. Thanks for the nice post. I’m looking for information about openDBCopy because I’m planning of migration from PostgreSQL to Derby Embedded for footprint saving. I’m curious how much that your migration of Derby Embedded to MySQL improved performance.

      1. Absolutely not 😉 . You may want to try out H2 DB, not sure how it copes with large datasets, but it claims to be one of the fastests.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s