This page last changed on Feb 17, 2010 by edawson.

This page contains instructions on migrating your Crucible database from its default embedded form to an external database. This may be useful for the following reasons:

  • Improved Protection Against Data Loss: The Crucible built-in database, running HSQLDB is somewhat susceptible to data loss during system crashes. External databases are generally more resistant to data loss during a system crash.
  • Performance & Scalability: if you have many users on your Crucible instance, running the database on the same server as FishEye may slow it down. When using the embedded database, the database will always be hosted and run on the same server as Crucible.
  • Data Stored in the Crucible Database: The Crucible database stores all information besides the cache for repository scans. This means all reviews, comments, review states, user data and user preferences information.

On this page:

Overview

Crucible and FishEye offer alternatives to the built-in HSQLDB database for storing its relational data. At the time of writing, MySQL Enterprise Server and PostgreSQL are supported (see Supported Platforms for version numbers). This page outlines the steps required for switching to an external database.

Migrating to MySQL Enterprise Server

To switch from the built-in HSQLDB database to MySQL Enterprise Server, install MySQL Enterprise Server and follow the steps below.

1. The JDBC drivers for MySQL Enterprise Server are bundled with FishEye. Skip to step 2 if this meets your needs. If you want to install a specific, different version of the bundled JDBC driver, download the MySQL Enterprise Server JDBC driver .JAR file from the download website and copy the .JAR to your FISHEYE_INST/lib directory (create the lib/ directory if it doesn't already exist). Move the existing JDBC .JAR file to another location (and back it up). Restart FishEye or Crucible to have it pick up the new driver.

2. Create a UTF-8 Database:
Note that the DB must be case-sensitive, i.e. it must use the utf8_bin collation sequence.

CREATE DATABASE crucible CHARACTER SET utf8 COLLATE utf8_bin;

3. You will also need to set the Server Characterset to utf8. This can be done by adding the following in my.ini for Windows or my.cnf for other OS. It has to be declared in the Server section, which is the section after [mysqld]:

[mysqld]
default-character-set=utf8

4. Use the status command to verify database character encoding information:

Screenshot: Using the MySQL Enterprise Server Status Command

5. Create a user that can log in from the host that Crucible or FishEye is running on and make sure that the user has full access to the newly created database. In particular, the user should be allowed to create and drop tables, indexes and other constraints.

For instance, when Crucible and MySQL Enterprise Server run on the same machine (accessible through localhost), issue the following commands (replacing username and password with the appropriate values):

mysql> grant all on crucible.* to 'username'@'localhost' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

6. With the database prepared, navigate to the 'Database Configuration' section in the admin interface, select MySQL Enterprise Server from the drop down and fill out the database URL, username and password.

Then click 'Test Connection' to verify that Crucible or FishEye can log in to the database:

Screenshot: Testing the Connection

If this fails, verify that you have the MySQL Enterprise Server JDBC driver .JAR file in the classpath (by placing the .JAR file in FISHEYE_INST/lib). Also, ensure that the database user can log in to the database from the machine that Crucible or FishEye is running on and that all the required privileges are present.

7. Click 'Save & Migrate Data' to start the migration process.

During the migration process (which will take several minutes, depending on the size of your database and network throughput), the product will be inaccessible to users and external API clients. Users will see a maintenance screen that informs them of the process. Should the migration fail for any reason, Crucible will not switch to the new database and report on the encountered problems. Because the destination database may now contain some, but not yet all data, drop all tables, indexes and constraints before attempting a new migration.

Screenshot: Migrating the Database

Migrating to PostgreSQL

To switch from the built-in HSQLDB database to PostgreSQL, install PostgreSQL and follow the steps below.

1. The JDBC drivers for PostgreSQL are bundled with FishEye. Skip to step 2 if this meets your needs. If you want to install a specific, different version of the bundled JDBC driver, download the PostgreSQL JDBC driver .JAR file from the PostgreSQL website and copy the .JAR to your FISHEYE_INST/lib directory (create the lib/ directory if it doesn't already exist). Move the existing JDBC .JAR file to another location (and back it up). Restart FishEye or Crucible to have it pick up the new driver.

2. Create a new database user (replacing 'username' and 'password' with the appropriate values):

$ psql
> create user username password 'password';

3. Create a UTF-8 database and make the newly created user the owner:

> create database crucible ENCODING 'UTF-8' OWNER username;

4. Make sure the user has full access to the database:

> grant all on database crucible to username;

During the migration process (which will take several minutes, depending on the size of your database and network throughput), the product will be inaccessible to users and external API clients. Users will see a maintenance screen that informs them of the process. Should the migration fail for any reason, Crucible will not switch to the new database and report on the encountered problems. Because the destination database may now contain some, but not yet all data, drop all tables, indexes and constraints before attempting a new migration.

Support for other Databases

Crucible and FishEye currently ship with support for MySQL Enterprise Server and PostgreSQL as external databases (see Supported Platforms for version numbers).

If you are looking for support for Oracle or Microsoft SQL Server, please vote for the issues below. Your vote will help us prioritise them.

If you are using another database product that you would like to see supported, please create a JIRA issue for it under the Crucible project.


Document generated by Confluence on Feb 23, 2010 02:04