📙 You will learn

How to upgrade from 2.8.7 to 2.8.8.3.

Prerequisites

Audience: IT Professional and/or End User
Difficulty: Advanced
Time needed: Approximately 60 minutes
Tools required: Tomcat, MySQL or MS SQL Server

ALWAYS PERFORM A FULL BACKUP OF YOUR DATABASE BEFORE ATTEMPTING ANY UPGRADE!

MySQL Server: You can run the mysqldump command from the MySQL server, e.g., mysqldump -u mydbuser -p mydatabase > path/to/backup.sql (we do not recommend using MySQL Workbench to perform the backup).

MS SQL Server: Using SSMS, right-click on the database, select Tasks → Back Up…, then choose the location & name of the backup file and click OK.

If you have any questions or concerns or if you wish to upgrade from an older version of ThreadFix, please reach out to our support team here: support@threadfix.it

ThreadFix Deployment Update

Follow these steps to deploy the updated version of ThreadFix:

Stop the Tomcat instance on which ThreadFix is running.
Move your current ThreadFix deployment directory from your Tomcat webapps folder into a backup directory.
1. webapps directory location: //TOMCAT_HOME/webapps
Copy the updated ThreadFix folder from your new artifact into your webapps directory.
1. webapps directory location: //TOMCAT_HOME/webapps
Copy the following files from your backed up ThreadFix directory to the newly updated ThreadFix directory:
(FROM: <previous-threadfix-deploy>/WEB-INF/classes/, TO: <new-threadfix-deploy>/WEB-INF/classes/, except as noted below)
1. custom.properties
2. ESAPI.properties (See REQUIRED update below)
  1. Replace the current ESAPI.Logger (ESAPI.Logger=com.denimgroup.threadfix.logging.esapi.slf4j.CustomESAPISlf4jLogFactory) with the lines below in order to prevent issues or errors that may break the deployment:
    ESAPI.Logger=org.owasp.esapi.logging.slf4j.Slf4JLogFactory Logger.UserInfo=false Logger.ClientInfo=false Encryptor.MinEncryptionKeyLength=128
3. jdbc.properties
4. jms.properties
5. threadfix.license
6. If you've updated any of these in your previous deployment, copy them as well:
  1. <threadfix-deploy>/WEB-INF/security.xml
  2. <threadfix-deploy>/WEB-INF/classes/security/samlKeystore.jks (and/or any other Java keystore that you've saved in this directory)
Copy the velocityTemplates directory from your backed up ThreadFix directory to the newly-updated ThreadFix directory.
(FROM: <previous-threadfix-deploy> TO:<new-threadfix-deploy>)
In Linux environments, you may need to grant your tomcat user/group ownership and permissions to the newly-deployed artifact.
Examples:
```
sudo chown -R tomcat:tomcat /opt/tomcat
sudo chmod -R 775 /opt/tomcat
```
Cleanup - Delete the following:
1. The contents of the <tomcat-deploy>/work directory
2. Your web browser's cache/history

Before-Restart Database Updates

Database Updates

Do Not Restart Tomcat If Scripts Do Not Complete

If you receive an error when running before-restart database update scripts, please report the issue to ThreadFix Support (create a ticket in Service Desk), including a screenshot of the script & error message.

You should NOT proceed to start Tomcat until the issue is resolved. If you do, Hibernate may make unrecoverable changes that will require you to restore your database from backup.

Caution/warning messages are OK; just not errors.

Expand the MySQL or MS SQL Server code block to reveal the scripts:

MySQL:

UPDATE RemoteProviderAuthenticationField
SET filterable = 0
WHERE name in ('API Key','API Secret')
AND applicationId = (SELECT id FROM RemoteProviderType WHERE name = 'Veracode');
  
ALTER TABLE RemoteProviderAuthenticationField DROP FOREIGN KEY FKDF097F6B4E5D4B9B;
ALTER TABLE RemoteProviderAuthenticationEntity DROP FOREIGN KEY fk_RPAuthenticationEntity_RPAuthenticationField;
  
DELETE rpae.* FROM RemoteProviderAuthenticationEntity AS rpae
JOIN RemoteProviderAuthenticationField AS rpaf ON rpae.remoteProviderAuthenticationFieldId = rpaf.id
JOIN RemoteProviderType AS rpt ON rpt.id = rpaf.applicationId
WHERE rpaf.name = 'Organization' AND rpt.name = 'SonarQube';
  
DELETE rpae.* FROM RemoteProviderAuthenticationEntity AS rpae
JOIN RemoteProviderEntity AS rpe ON rpae.remoteProviderEntityId = rpe.id
JOIN RemoteProviderType AS rpt ON rpe.remoteProviderTypeId = rpt.id
WHERE rpt.name = 'SonarQube'
AND rpae.remoteProviderEntityId IN (SELECT * FROM (SELECT remoteProviderEntityId
FROM RemoteProviderAuthenticationEntity 
WHERE value = 'v7 (7.9)') as rpeids);
  
DELETE rpaf.* FROM RemoteProviderAuthenticationField AS rpaf
JOIN RemoteProviderType AS rpt ON rpt.id = rpaf.applicationId
WHERE rpt.name = 'SonarQube' AND rpaf.name = 'Organization';
  
DELETE rpafsj.* FROM RemoteProviderAuthenticationField_SelectOption_Join AS rpafsj
JOIN RemoteProviderAuthenticationField AS rpaf ON rpafsj.remoteProviderAuthenticationField_id = rpaf.id
JOIN SelectOption AS so ON rpafsj.selectOptions_id = so.id
WHERE so.value = 'v7 (7.9)';
  
DELETE so.* FROM SelectOption AS so WHERE so.value = 'v7 (7.9)';
  
ALTER TABLE RemoteProviderAuthenticationEntity
    ADD CONSTRAINT fk_RPAuthenticationEntity_RPAuthenticationField FOREIGN KEY (remoteProviderAuthenticationFieldId) REFERENCES RemoteProviderAuthenticationField (id);
  
ALTER TABLE RemoteProviderAuthenticationField
    ADD CONSTRAINT FKDF097F6B4E5D4B9B FOREIGN KEY (applicationId) REFERENCES RemoteProviderType (id);
  
ALTER TABLE User MODIFY title varchar(128);
  
DROP PROCEDURE IF EXISTS dropForeignKeyAndIndexIfExists;
DELIMITER $$
CREATE PROCEDURE dropForeignKeyAndIndexIfExists(tableName varchar(100), refTableName varchar(100))
BEGIN
    DECLARE constraintName varchar(100);
    DECLARE columnName varchar(100);
    DECLARE indexName varchar(100);
  
    SELECT constraint_name, column_name INTO constraintName, columnName FROM information_schema.key_column_usage
    WHERE referenced_table_name = refTableName
    AND table_name = tableName
    AND table_schema = DATABASE();
  
    IF (constraintName IS NOT NULL) THEN
      SET @s1 = CONCAT('ALTER TABLE `', tableName, '` DROP FOREIGN KEY `', constraintName, '`;');
      PREPARE statement FROM @s1;
      EXECUTE statement;
  
      IF ((SELECT COUNT(*) FROM information_schema.statistics WHERE index_name = constraintName AND table_name = tableName AND TABLE_SCHEMA = DATABASE()) > 0) THEN
        SET @s2 = CONCAT('ALTER TABLE `', tableName, '` DROP INDEX `', constraintName, '`;');
        PREPARE statement FROM @s2;
        EXECUTE statement;
      END IF;
    END IF;
END$$
 
DELIMITER ;
  
CALL dropForeignKeyAndIndexIfExists('User_Group', 'User');
CALL dropForeignKeyAndIndexIfExists('User_Group', 'Groups');
  
ALTER TABLE User_Group CHANGE User_Id temp int(11) NOT NULL;
ALTER TABLE User_Group CHANGE Group_Id User_Id int(11) NOT NULL;
ALTER TABLE User_Group CHANGE temp Group_Id int(11) NOT NULL;
  
ALTER TABLE User_Group
    ADD CONSTRAINT FK_UserGroup_GroupId FOREIGN KEY (Group_Id) REFERENCES Groups (id),
    ADD CONSTRAINT FK_UserGroup_UserId FOREIGN KEY (User_Id) REFERENCES User (id);
DROP PROCEDURE IF EXISTS dropForeignKeyAndIndexIfExists;
 
ALTER TABLE DefaultConfiguration MODIFY COLUMN oauthAccessToken VARCHAR(265) NULL;

MS SQL Server

UPDATE RemoteProviderAuthenticationField
SET filterable = 0
WHERE name in ('API Key','API Secret')
AND applicationId = (SELECT id FROM RemoteProviderType WHERE name = 'Veracode');
GO
 
ALTER TABLE RemoteProviderAuthenticationField DROP CONSTRAINT FKDF097F6B4E5D4B9B;
ALTER TABLE RemoteProviderAuthenticationEntity DROP CONSTRAINT fk_RPAuthenticationEntity_RPAuthenticationField;
 
DELETE rpae FROM RemoteProviderAuthenticationEntity rpae 
JOIN RemoteProviderAuthenticationField rpaf ON rpae.remoteProviderAuthenticationFieldId = rpaf.id
JOIN RemoteProviderType rpt ON rpt.id = rpaf.applicationId
WHERE rpaf.name = 'Organization' AND rpt.name = 'SonarQube';
 
DELETE rpae FROM RemoteProviderAuthenticationEntity rpae
JOIN RemoteProviderEntity rpe ON rpae.remoteProviderEntityId = rpe.id
JOIN RemoteProviderType rpt ON rpe.remoteProviderTypeId = rpt.id
WHERE rpt.name = 'SonarQube'
AND rpae.remoteProviderEntityId IN (SELECT * FROM (SELECT remoteProviderEntityId
FROM RemoteProviderAuthenticationEntity  
WHERE value = 'v7 (7.9)') as rpeids);
 
DELETE rpaf FROM RemoteProviderAuthenticationField rpaf
JOIN RemoteProviderType rpt ON rpt.id = rpaf.applicationId
WHERE rpt.name = 'SonarQube' AND rpaf.name = 'Organization';
 
DELETE rpafsj FROM RemoteProviderAuthenticationField_SelectOption_Join rpafsj
JOIN RemoteProviderAuthenticationField rpaf ON rpafsj.remoteProviderAuthenticationField_id = rpaf.id
JOIN SelectOption so ON rpafsj.selectOptions_id = so.id
WHERE so.value = 'v7 (7.9)';
 
DELETE so FROM SelectOption so WHERE so.value = 'v7 (7.9)';
 
ALTER TABLE RemoteProviderAuthenticationEntity
    ADD CONSTRAINT fk_RPAuthenticationEntity_RPAuthenticationField FOREIGN KEY (remoteProviderAuthenticationFieldId) REFERENCES RemoteProviderAuthenticationField (id);
 
ALTER TABLE RemoteProviderAuthenticationField
    ADD CONSTRAINT FKDF097F6B4E5D4B9B FOREIGN KEY (applicationId) REFERENCES RemoteProviderType (id);
 
ALTER TABLE [User] ALTER COLUMN [title] varchar(128);
 
IF (OBJECT_ID('removeForeignKeyConstraint', 'P') IS NOT NULL)
DROP PROCEDURE removeForeignKeyConstraint
GO
 
CREATE PROCEDURE removeForeignKeyConstraint(@table_name varchar(100), @ref_table_name varchar(100))
    AS
    DECLARE @constraint varchar(200);
    SELECT @constraint = (SELECT so.name
                          FROM sysobjects so
                                   inner join sysobjects c on so.parent_obj = c.id
                                   inner join sysreferences r on so.id = r.constid
                                   inner join sysobjects p on r.rkeyid = p.id
                                   inner join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
                                   inner join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
                          WHERE so.type = 'F'
                            AND p.name = @ref_table_name
                            AND c.name = @table_name);
    IF @constraint IS NOT NULL
    BEGIN
        EXEC('ALTER TABLE ' + @table_name + ' DROP CONSTRAINT ' + @constraint);
    END
GO
 
BEGIN TRANSACTION
 
EXEC removeForeignKeyConstraint @table_name = 'User_Group', @ref_table_name = 'user';
EXEC removeForeignKeyConstraint @table_name = 'User_Group', @ref_table_name = 'groups';
 
 
EXEC sp_RENAME 'User_Group.User_Id' , 'temp', 'COLUMN';
EXEC sp_RENAME 'User_Group.Group_Id' , 'User_Id', 'COLUMN';
EXEC sp_RENAME 'User_Group.temp' , 'Group_Id', 'COLUMN';
 
ALTER TABLE User_Group ADD CONSTRAINT FK_UserGroup_GroupId FOREIGN KEY (Group_Id) REFERENCES [Groups](id);
ALTER TABLE User_Group ADD CONSTRAINT FK_UserGroup_UserId FOREIGN KEY (User_Id) REFERENCES [User](id);
 
COMMIT;
GO
 
IF (OBJECT_ID('removeForeignKeyConstraint', 'P') IS NOT NULL)
DROP PROCEDURE removeForeignKeyConstraint
GO
 
ALTER TABLE DefaultConfiguration ALTER COLUMN oauthAccessToken VARCHAR(265) NULL;
GO

Tip

For large scripts, it may be most efficient to save them to a .sql file and either drag & drop them into your UI-based database manager (e.g., SSMS or MySQL Workbench), or if using a CLI to access your MySQL server, you can use the following example command to execute the script:

mysql -u [username] -p [dbname] < [updatescript.sql] -v -v

Start Tomcat/ThreadFix

Restart Tomcat/ThreadFix.
Navigate to the login page to ensure that it loads as expected. If so, you may now log in and verify that the new version is installed; the version is included in the page footer after logging in.

When launching the new deployment for the first time after deploying the update, ThreadFix may take a few minutes to make necessary schema updates, so there may be a delay in accessing the login page.

Monitor the <tomcat-deploy>/logs/threadfix.log file to determine when it has finished, look for the second instance of the following event: Finished updating Enterprise Tags.

Please do not interrupt this process by stopping Tomcat.

Upgrade from 2.8.7 to 2.8.8.3