Migrating to MySQL

Prerequisites

To start with:

1. Install a supported version of MySQL. Check the Supported platforms page for the exact versions that are supported. Note that MariaDB and Percona variants of MySQL are not supported, and are known to cause issues when used with Fisheye.
   
   
2. Set up 4-byte UTF8 encoding in your MySQL server (my.ini or my.cnf):
   
   [mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_bin
   
   You'll need to restart MySQL for that change to take effect. 
   
   
3. Download and install the JDBC driver, if necessary.
1. Download the MySQL Connector/J JDBC driver from the MySQL download website.
2. Expand the downloaded zip/tar.gz file.
3. Copy the mysql-connector-java-x.y.zz-bin.jar file to your FISHEYE_INST/lib directory. If the lib/ directory doesn't already exist, you will need to create it. 
4. Restart Fisheye/Crucible.

Step 1. Create a MySQL database

Set up a MySQL database as follows:

• Configure the database to use the InnoDB storage engine
• Create a database on MySQL for Fisheye/Crucible to use
• Create a Fisheye user on the database
• Configure the database to use utf8mb4 character set encoding
• Configure the database to use utf8mb4_bin collation (to ensure case sensitivity).

Here is an example of how to do that. When Fisheye/Crucible and MySQL run on the same machine (accessible through localhost), issue the following commands (replacing fisheyeuser and password with your own values):

mysql> SET GLOBAL default_storage_engine = 'InnoDB';
mysql> CREATE USER 'fisheyeuser'@'localhost' IDENTIFIED BY 'password';
mysql> CREATE DATABASE fisheye CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
mysql> GRANT ALL PRIVILEGES ON fisheye.* TO 'fisheyeuser'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> QUIT

This creates an empty MySQL database with the name fisheye, and a user that can log in from the host that FIsheye is running on who has full access to the newly created database. In particular, the user should be allowed to create and drop tables, indexes and other constraints.

Now, verify that database character encodings are correct:

SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

Step 2. Configure Fisheye/Crucible to use MySQL, and migrate data

In order to migrate to a different database backend, you must create a backup of sql data, configure the database and finally import the data using a backup restoration process. This can be done from either the Fisheye/Crucible administration console, which streamlines the process, or using the command line tool which Fisheye/Crucible provides.

Option 1: Migrate using the UI (Fisheye/Crucible Administration)

1. Navigate to the Database page in Fisheye/Crucible's Administration console.

   To log in to the Admin area, you can either:

   • click Administration at the foot of the page. 
   • navigate to http://HOSTNAME:8060/admin/, where HOSTNAME is the name of the server on which you installed Fisheye.

   Once logged in as an administrator you can also get to the Admin area by clicking the 'cog' menu in the Fisheye/Crucible header, and choosing Administration.

2. Choose Edit > Test Connection to verify that Fisheye/Crucible can log in to the database.
3. Select MySQL from the database type.
4. Fill in the appropriate fields, replacing the host, port, database name, username and password using the same connection details as used when creating the MySQL database in Step 1 above.
5. In the Parameters field, enter:
   

   useUnicode=true
characterEncoding=UTF8MB4
connectionCollation=utf8mb4_bin
nullCatalogMeansCurrent=true



6. Click Test Connection to validate the values.
   
   If this fails, verify that you have the MySQL JDBC driver .jar file in the classpath (see Prerequisites section above for instructions on how to install the driver). Also, ensure that the database user can log in to the database from the machine that Fisheye/Crucible is running on and that all the required privileges are present.
   
   
7. Click Save & Migrate 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, Fisheye/Crucible will not switch to the new database, and will 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
   
   

Option 2: Migrate using the command line

1. Create a backup of the sql data from the Fisheye/Crucible instance. Read Backing up and restoring Fisheye data and Backing up and restoring Crucible data for information on how to create a backup.

2. Run the following command from the <FishEye installation directory>/bin directory:

   $ ./fisheyectl.sh restore --sql \
           --file '/path/to/backup.zip' \
           --dbtype mysql \
           --jdbcurl 'jdbc:mysql://hostname/dbname?useUnicode=true&characterEncoding=UTF8MB4&connectionCollation=utf8mb4_bin&nullCatalogMeansCurrent=true' \
           --username crucible \
           --password password
   

3. When the import is complete, Fisheye/Crucible can be started and will use MySQL.