Skip to content

Using MySQL

Jump to bottom Edit New page
Nazul Grimaldo edited this page Nov 4, 2015 · 30 revisions

Describes switching the ThreadFix database from [the default] HSQL to MySQL

Introduction

To switch ThreadFix to use MySQL instead of HSQL, you'll need to have a couple things ready:

Once you have these things, you can start with the instructions in the next section.

These instructions refer to the latest ThreadFix Community zip package, although they should be easy to adapt for other environments. Note: For 4-byte Unicode support, MySQL must be at least version 5.5.3 or later.

MySQL Configuration

Create a new user, specifically for ThreadFix, in MySQL. To create a new user run the following commands:

  • CREATE DATABASE threadfix CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
  • CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';
  • CREATE USER '<username>'@'%' IDENTIFIED BY '<password>';
  • GRANT ALL PRIVILEGES ON threadfix.* TO '<username>'@'localhost' IDENTIFIED BY '<password>';
  • GRANT ALL PRIVILEGES ON threadfix.* TO '<username>'@'%' IDENTIFIED BY '<password>';
  • FLUSH PRIVILEGES;

Replace 'username' and 'password' with your desired credentials.

More information on creating users in MySQL can be found here: Adding Users

Update MySQL Character Set

MySQL does not, by default, support the full Unicode character set. ThreadFix requires the MySQL character set to updated to utf8mb4. Set by step instructions on that process can be found here.

Note: If you used the CREATE DATABASE command above then the character set will already be set to utf8mb4 already so an update will not be necessary.

ThreadFix Configuration

First, if ThreadFix doesn't have a folder in tomcat/webapps, unzip the threadfix.war file to a folder named threadfix. Starting the server will unzip the WAR automatically.

  • Navigate to tomcat/webapps/threadfix/WEB-INF/classes
  • Create a backup of the current properties file. cp jdbc.properties jdbc.properties.bak
  • Copy the MySQL properties over the HSQL properties file. cp jdbc.properties.mysql jdbc.properties
  • Fields that need to be changed:
    • jdbc.username (MySQL username)
    • jdbc.password (MySQL password)
    • hibernate.hbm2ddl.auto (Simply change the hibernate.hbm2ddl.auto option from 'update' to 'create'.)

After ThreadFix has started successfully change the create option back to update! If the create option is still in place the database will be scratched each time ThreadFix is started.

Note: When recreating a fresh database from scratch, it is advisable to drop any previous database named "threadfix" (This will DELETE all data in that database! Make sure to back up existing records if you want to keep them!), and then create it again. This ensures no leftover records interfere with ThreadFix's database initialization process. To do so:
DROP DATABASE threadfix;
CREATE DATABASE threadfix;

Running ThreadFix

Now that the configuration is complete start ThreadFix by starting your Tomcat service or running the start script in the ThreadFix zip package.

Once ThreadFix is started open your web browser and navigate to http://localhost:8080/threadfix

The ThreadFix login page will appear.

  • Username: user
  • Password: password
    Change default login credentials as soon as possible

###Red Hat Enterprise Linux Users Hibernate uses foreign key constraints in MySQL. Some ThreadFix users running older versions of Red Hat Enterprise Linux have reported issues with these foreign key constraints not being supported. To override the default database behavior and assign a supported database engine, execute these SQL commands on your ThreadFix database:

ALTER TABLE Application ENGINE = InnoDB;
ALTER TABLE RemoteProviderType ENGINE = InnoDB;
ALTER TABLE VulnerabilityComment ENGINE = InnoDB;

Migrating to SQL Server

To migrate you MySQL ThreadFIX database, you can use the following application:

After installing the SQL Server Migration Assistant, open the application and create a new project, select the SQL Server you want to migrate (SQL Server 2014) when creating the new project.

Now connect to your MySQL database by clicking on Connect to MySQL in the toolbar. Enter the details of your MySQL server and click Connect.

After the connection your MySQL database was successfully established, click on Connect to SQL Server on the toolbar and enter the details of for the connection, and click on Connect.

Both connections should have been successfully established, if that is not the case, then check your credentials again and try to connect again.

With the connections established to the MySQL and SQL Server databases, migrate your database as follows:

  • Right click on your MySQL ThreadFix database.
  • Click on Convert Schema and wait until the process finishes successfully.
  • Right click on the SQL Server database and click on Synchronize with Database and wait until it finishes successfully.
  • Right click on the MySQL ThreadFix database and click on Migrate Data.
  • The migration should finish successfully.
Add a custom sidebar

Clone this wiki locally