My OperaMigrating Sonar from Derby to MySQL
Sunday, April 3, 2011 10:00:38 AM
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.
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...
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.sqlI 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:
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 )
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.
Unregistered user # Thursday, October 20, 2011 9:20:34 AM
Wutske # Sunday, November 6, 2011 7:49:35 PM
Unregistered user # Thursday, November 24, 2011 12:05:16 PM
Unregistered user # Friday, December 16, 2011 2:27:17 AM
Unregistered user # Thursday, January 26, 2012 8:51:43 PM
Unregistered user # Wednesday, February 1, 2012 5:00:23 PM
Unregistered user # Thursday, February 9, 2012 8:59:12 AM
Wutske # Thursday, February 16, 2012 6:11:43 PM
Feel free to suggest it anyway
Unregistered user # Monday, March 5, 2012 12:26:53 PM
Unregistered user # Friday, March 9, 2012 11:12:22 PM
Unregistered user # Tuesday, March 20, 2012 9:48:40 AM
Wutske # Saturday, April 7, 2012 8:50:10 PM
Originally posted by anonymous:
If your MySQL server is not running on the same machine as Sonar (thus not connecting to localhost), you have to ensure that you grant the user 'sonar' the rights so make a connection from an external system. If it's running localhost, it's not a bad idea the check the user rights tooUnregistered user # Friday, April 13, 2012 10:25:00 AM