Using MySQL

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:

• A ThreadFix instance [available here] (http://www.threadfix.org/download/)
• A MySQL instance [available here] (http://www.mysql.com/downloads/)

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;