{"id":8167,"date":"2024-07-05T13:11:28","date_gmt":"2024-07-05T04:11:28","guid":{"rendered":"https:\/\/hasu0707.duckdns.org\/blog\/?p=8167"},"modified":"2026-01-19T21:23:51","modified_gmt":"2026-01-19T12:23:51","slug":"fortify-ssc-database-upgrade","status":"publish","type":"post","link":"https:\/\/hasu0707.duckdns.org\/blog\/?p=8167","title":{"rendered":"[Fortify] SSC Database Migration (MySQL)"},"content":{"rendered":"\n1. MySQL Database \ubc31\uc5c5<\/strong>
  mysqldump --single-transaction --routines --triggers --max_allowed_packet=2048M -u root -p<Password> db_ssc_2222 > 20240801_ssc_backup.sql


2. SSC \uc124\uce58 \uc9c4\ud589<\/strong>
  Database \ud56d\ubaa9\uc5d0\uc11c DB Connection \ud14c\uc2a4\ud2b8 \ud6c4 \uc6b0\uce21\ud558\ub2e8 \uc2a4\ud06c\ub9bd\ud2b8 \ub2e4\uc6b4\ub85c\ub4dc\uc5d0\uc11c ssc-migration.sql \ub2e4\uc6b4\ub85c\ub4dc
  (\ud558\ub2e8 \uadf8\ub9bc \ucc38\uace0)


3. ssc-migration.sql \uc2a4\ud06c\ub9bd\ud2b8\ub85c DB \ub9c8\uc774\uadf8\ub808\uc774\uc158 \uc9c4\ud589<\/strong>
  mysql -u root -p<Password> db_ssc_2222 < ssc-migration.sql

  \ub9cc\uc77c \uc624\ub958 \ubc1c\uc0dd \uc2dc \uc544\ub798\uc758 \uc2a4\ud06c\ub9bd\ud2b8\ub97c \uc2e4\ud589\ud558\uc5ec \uc6d0\uc0c1 \ubcf5\uad6c
  DROP DATABASE IF EXISTS db_ssc_2222;
  CREATE DATABASE db_ssc_2222 CHARACTER SET utf8mb3 COLLATE utf8mb3_bin;

  mysql -u root -p<Password> db_ssc_2222 < 20240705_103707_ssc_db.sql

4. SSC \uc124\uce58 \uc9c4\ud589<\/strong>
  SEED\ub3c4 \uc815\uc0c1\uc801\uc73c\ub85c \uc5c5\ub85c\ub4dc \ud55c\ub2e4.
  (\ud558\ub2e8 \uadf8\ub9bc \ucc38\uace0)<\/span>\n\n\n\n
\n\n\n\n
\"\"<\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\n\n\n\n

[\uc0d8\ud50c] ssc-migration.sql<\/p>\n\n\n\n

--  *********************************************************************\n--  Update Database Script\n--  *********************************************************************\n--  Change Log: dbMaster.xml\n--  Ran at: 24. 7. 5. \uc624\ud6c4 12:09\n--  Against: root@jdbc:mariadb:\/\/10.10.10.149\/db_ssc_2222\n--  Liquibase version: 4.10.0\n--  *********************************************************************\n\n--  Create Database Lock Table\nCREATE TABLE db_ssc_2222.DATABASECHANGELOGLOCK (ID INT NOT NULL, `LOCKED` TINYINT(1) NOT NULL, LOCKGRANTED datetime NULL, LOCKEDBY VARCHAR(255) NULL, CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));\n\n--  Initialize Database Lock Table\nDELETE FROM db_ssc_2222.DATABASECHANGELOGLOCK;\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOGLOCK (ID, `LOCKED`) VALUES (1, 0);\n\n--  Lock Database\nUPDATE db_ssc_2222.DATABASECHANGELOGLOCK SET `LOCKED` = 1, LOCKEDBY = 'LENOVO-E430 (10.10.10.127)', LOCKGRANTED = NOW() WHERE ID = 1 AND `LOCKED` = 0;\n\n--  Changeset dbF360_Init.xml::f360_init_mysql_1::hp\nSET collation_connection = @@collation_database;\n\nUPDATE db_ssc_2222.DATABASECHANGELOG SET COMMENTS = '', CONTEXTS = NULL, DATEEXECUTED = NOW(), DEPLOYMENT_ID = '0148965608', EXECTYPE = 'RERAN', LABELS = NULL, MD5SUM = '8:c0200fd5942e9c97af8ac5709e56d3cb', ORDEREXECUTED = 808 WHERE ID = 'f360_init_mysql_1' AND AUTHOR = 'hp' AND FILENAME = 'dbF360_Init.xml';\n\n--  Changeset dbF360_23.1.xml::f360_23.1_1::fortify\n--  Add indices to support eventType and userName filtering\nCREATE INDEX EVENTLOGENTRY_TYPE_IND ON db_ssc_2222.eventlogentry(eventType);\n\nCREATE INDEX EVENTLOGENTRY_NAME_TYPE_IND ON db_ssc_2222.eventlogentry(userName, eventType);\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.1_1', 'fortify', 'dbF360_23.1.xml', NOW(), 809, '8:f0cd5b243e24b12b34472ea39b4fdc65', 'createIndex indexName=EVENTLOGENTRY_TYPE_IND, tableName=eventlogentry; createIndex indexName=EVENTLOGENTRY_NAME_TYPE_IND, tableName=eventlogentry', 'Add indices to support eventType and userName filtering', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_23.1.xml::f360_23.1_2::fortify\n--  Adding startDate and finishDate columns to savedreport table\nALTER TABLE db_ssc_2222.savedreport ADD startDate datetime(3) NULL, ADD finishDate datetime(3) NULL;\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.1_2', 'fortify', 'dbF360_23.1.xml', NOW(), 810, '8:4774f0387df72c051dbcfdf79c97525d', 'addColumn tableName=savedreport', 'Adding startDate and finishDate columns to savedreport table', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_23.1.xml::f360_23.1_3::fortify\n--  Adding table as an extension to the issue table because the issue table is too large.\n--              We can use this table to append any columns we would like to attach to issues.\n--              issue_id maps to issue.id.  projectVersion_id, issueInstanceId and engineType also map to corresponding columns in the issue table.\n--              reintroducedDate is the date an issue is reintroduced in an app version, similar to foundDate and removedDate.\nCREATE TABLE db_ssc_2222.issue_extension (issue_id INT NOT NULL, projectVersion_id BIGINT NOT NULL, issueInstanceId VARCHAR(80) NOT NULL, engineType VARCHAR(20) NOT NULL, reintroducedDate BIGINT NULL, CONSTRAINT PK_ISSUE_EXTENSION PRIMARY KEY (issue_id));\n\nCREATE INDEX ISSUE_EXTENSION_PV_IID_ENGINE_IDX ON db_ssc_2222.issue_extension(projectVersion_id, issueInstanceId, engineType);\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.1_3', 'fortify', 'dbF360_23.1.xml', NOW(), 811, '8:760b177832a55b791e0796352399cebe', 'createTable tableName=issue_extension; createIndex indexName=ISSUE_EXTENSION_PV_IID_ENGINE_IDX, tableName=issue_extension', 'Adding table as an extension to the issue table because the issue table is too large.\n            We can use this table to append any columns we would like to attach to issues.\n            issue_id maps to issue.id.  projectVersion_id, issueInstan...', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_23.1.xml::f360_23.1_4::fortify\n--  add start and finish dates to data exports\nALTER TABLE db_ssc_2222.dataexport ADD startDate datetime(3) NULL;\n\nALTER TABLE db_ssc_2222.dataexport ADD finishDate datetime(3) NULL;\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.1_4', 'fortify', 'dbF360_23.1.xml', NOW(), 812, '8:b707b1e845027df177bf9cb3cee8a7eb', 'addColumn tableName=dataexport; addColumn tableName=dataexport', 'add start and finish dates to data exports', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_23.1.xml::f360_23.1_5::fortify\n--  add new table for issues delta exports\nCREATE TABLE db_ssc_2222.issuesdeltaexport (id BIGINT AUTO_INCREMENT NOT NULL, fileName VARCHAR(255) NOT NULL, note VARCHAR(255) NULL, requestDate datetime(3) NOT NULL, startDate datetime(3) NULL, finishDate datetime(3) NULL, userName VARCHAR(255) NOT NULL, status VARCHAR(30) NOT NULL, messages MEDIUMTEXT NULL, documentInfo_id INT NULL, projectVersionIds MEDIUMTEXT NULL, deltaSinceDate datetime(3) NULL, CONSTRAINT PK_ISSUESDELTAEXPORT PRIMARY KEY (id));\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.1_5', 'fortify', 'dbF360_23.1.xml', NOW(), 813, '8:f532a7c3961d83d175526aa421a308c3', 'createTable tableName=issuesdeltaexport', 'add new table for issues delta exports', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360Mysql_23.1_datetime.xml::f360Mysql_23.1_datetime::fortify\n--  Enhance to millisecond precision for datetime columns added between 20.2 and 22.2 which were incorrectly created as datetime instead of datetime(3) in scripts generated prior to ssc v23.1\nALTER TABLE fortifygroup MODIFY created datetime(3) NOT NULL, MODIFY lastModified datetime(3) NOT NULL;\n\nALTER TABLE fortifyuser MODIFY created datetime(3) NOT NULL, MODIFY lastModified datetime(3) NOT NULL;\n\nALTER TABLE jobqueue MODIFY createTime datetime(3) NOT NULL;\n\nALTER TABLE ldapcache MODIFY uploadDate datetime(3) NOT NULL;\n\nALTER TABLE scan_issue_correlation MODIFY target_scan_time datetime(3);\n\nALTER TABLE webhook MODIFY creationDate datetime(3) NOT NULL;\n\nALTER TABLE webhookhistory MODIFY createdAt datetime(3) NOT NULL;\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360Mysql_23.1_datetime', 'fortify', 'dbF360Mysql_23.1_datetime.xml', NOW(), 814, '8:efc7aa749c470eae51e25472fca50bec', 'sql', 'Enhance to millisecond precision for datetime columns added between 20.2 and 22.2 which were incorrectly created as datetime instead of datetime(3) in scripts generated prior to ssc v23.1', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_23.2.xml::f360_23.2_1::fortify\n--  Remove orphan records in documentinfo and datablob\nDELETE FROM db_ssc_2222.documentinfo WHERE NOT EXISTS( SELECT 1 FROM\n                (SELECT artifact.documentInfo_id\n                FROM artifact\n                UNION\n                SELECT au.documentInfo_id\n                FROM auditattachment au\n                UNION\n                SELECT de.documentInfo_id\n                FROM dataexport de\n                UNION\n                SELECT df.templateInfo_id\n                FROM documentdef df\n                UNION\n                SELECT dsp.fileValueDocumentInfo_id\n                FROM dynamicscanparameter dsp\n                UNION\n                SELECT ide.documentInfo_id\n                FROM issuesdeltaexport ide\n                UNION\n                SELECT pm.documentInfo_id\n                FROM pluginmetadata pm\n                UNION\n                SELECT pt.documentInfo_id\n                FROM projecttemplate pt\n                UNION\n                SELECT rd.templateDoc_id\n                FROM reportdefinition rd\n                UNION\n                SELECT rl.fileDoc_id FROM\n                reportlibrary rl\n                UNION\n                SELECT rp.documentInfo_id\n                FROM rulepack rp UNION\n                SELECT sr.reportOutputDoc_id\n                FROM savedreport sr) dids\n                WHERE dids.documentInfo_id = documentinfo.id);\n\nDELETE FROM db_ssc_2222.datablob WHERE NOT EXISTS( SELECT 1 FROM\n                (SELECT documentinfo.fileBlob_id\n                FROM documentinfo\n                UNION\n                SELECT snapshot.auditBlob_id\n                FROM snapshot\n                UNION\n                SELECT ldapcache.blobId\n                FROM ldapcache\n                UNION\n                SELECT projectversion.currentFprBlob_id\n                FROM projectversion) blobs\n                WHERE datablob.id = blobs.fileBlob_id);\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_1', 'fortify', 'dbF360_23.2.xml', NOW(), 815, '8:906f6f63924a29f20ac33d86e038d409', 'delete tableName=documentinfo; delete tableName=datablob', 'Remove orphan records in documentinfo and datablob', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_23.2.xml::f360_23.2_2::fortify\n--  Drop index and re-create it as non-unique.  It may have previously been created as unique.\nDROP INDEX ISSUE_EXTENSION_PV_IID_ENGINE_IDX ON db_ssc_2222.issue_extension;\n\nCREATE INDEX ISSUE_EXTENSION_PV_IID_ENGINE_IDX ON db_ssc_2222.issue_extension(projectVersion_id, issueInstanceId, engineType);\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_2', 'fortify', 'dbF360_23.2.xml', NOW(), 816, '8:26fa343f49f07cdcf5f02b38f2735cae', 'dropIndex indexName=ISSUE_EXTENSION_PV_IID_ENGINE_IDX, tableName=issue_extension; createIndex indexName=ISSUE_EXTENSION_PV_IID_ENGINE_IDX, tableName=issue_extension', 'Drop index and re-create it as non-unique.  It may have previously been created as unique.', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_23.2.xml::f360_23.2_4::fortify\n--  Update documentType for project template documents\nUPDATE documentinfo\n             SET documentType = 24\n             WHERE EXISTS(SELECT 1 from projecttemplate\n                                   WHERE projecttemplate.documentInfo_id = documentinfo.id);\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_4', 'fortify', 'dbF360_23.2.xml', NOW(), 817, '8:e4a40d317e31c02783c287f758348e01', 'sql', 'Update documentType for project template documents', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_23.2.xml::f360_23.2_5::fortify\n--  Update documentType for report documents\nUPDATE documentinfo\n             SET documentType = 4\n             WHERE EXISTS(SELECT 1\n                          FROM savedreport\n                          WHERE savedreport.reportOutputDoc_id = documentinfo.id\n                            AND savedreport.format = 'PDF');\n\nUPDATE documentinfo\n             SET documentType = 5\n             WHERE EXISTS(SELECT 1\n                          FROM savedreport\n                          WHERE savedreport.reportOutputDoc_id = documentinfo.id\n                            AND savedreport.format = 'DOC');\n\nUPDATE documentinfo\n             SET documentType = 23\n             WHERE EXISTS(SELECT 1\n                          FROM savedreport\n                          WHERE savedreport.reportOutputDoc_id = documentinfo.id\n                            AND savedreport.format = 'XLS');\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_5', 'fortify', 'dbF360_23.2.xml', NOW(), 818, '8:48e01f5bdece489d3a2f0a47434de339', 'sql; sql; sql', 'Update documentType for report documents', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_23.2.xml::f360_23.2_6::fortify\n--  Add auditAssistantTrainingCustomTagGuid to ProjectVersion to identify which custom tag to use for Audit Assistant training.\nALTER TABLE db_ssc_2222.projectversion ADD auditAssistantTrainingCustomTagGuid VARCHAR(255) NULL;\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_6', 'fortify', 'dbF360_23.2.xml', NOW(), 819, '8:b33361c7395796eed2ed0119eb7ee678', 'addColumn tableName=projectversion', 'Add auditAssistantTrainingCustomTagGuid to ProjectVersion to identify which custom tag to use for Audit Assistant training.', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_23.2.xml::f360_23.2_7::fortify\n--  Add column to attrlookup for defining Audit Assistant Training Label\nALTER TABLE db_ssc_2222.attrlookup ADD auditAssistantTrainingLabel VARCHAR(255) DEFAULT 'SKIP_FOR_TRAINING' NOT NULL;\n\nALTER TABLE db_ssc_2222.attrlookup ALTER auditAssistantTrainingLabel DROP DEFAULT;\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_7', 'fortify', 'dbF360_23.2.xml', NOW(), 820, '8:3ffe7546ef51773632dcc9bc884d738f', 'addColumn tableName=attrlookup; dropDefaultValue columnName=auditAssistantTrainingLabel, tableName=attrlookup', 'Add column to attrlookup for defining Audit Assistant Training Label', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_23.2.xml::f360_23.2_8::fortify\n--  Add table for SBOM storage.  Links SBOM file to project version by engineType.\n--  Link SBOM Document to a scan and some SBOM info\nCREATE TABLE db_ssc_2222.sbom (id BIGINT AUTO_INCREMENT NOT NULL, projectVersion_id BIGINT NOT NULL, engineType VARCHAR(20) NOT NULL, documentInfo_id BIGINT NOT NULL, format VARCHAR(255) NOT NULL, serialization VARCHAR(255) NOT NULL, CONSTRAINT PK_SBOM PRIMARY KEY (id));\n\nALTER TABLE db_ssc_2222.scan ADD sbomDocumentInfo_id BIGINT NULL, ADD sbomFormat VARCHAR(255) NULL, ADD sbomSerialization VARCHAR(255) NULL;\n\nALTER TABLE db_ssc_2222.attrlookup ALTER auditAssistantTrainingLabel DROP DEFAULT;\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_8', 'fortify', 'dbF360_23.2.xml', NOW(), 821, '8:5c82436f8b1aeb884e671de1b3a919ff', 'createTable tableName=sbom; addColumn tableName=scan; dropDefaultValue columnName=auditAssistantTrainingLabel, tableName=attrlookup', 'Add table for SBOM storage.  Links SBOM file to project version by engineType.\nLink SBOM Document to a scan and some SBOM info', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_23.2.xml::f360_23.2_9::fortify\n--  Add index for sbom table\nCREATE UNIQUE INDEX SBOM_PV_ENGINE_IDX ON db_ssc_2222.sbom(projectVersion_id, engineType);\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_9', 'fortify', 'dbF360_23.2.xml', NOW(), 822, '8:9eda2793fe19d138ca6f78ec941b7ec4', 'createIndex indexName=SBOM_PV_ENGINE_IDX, tableName=sbom', 'Add index for sbom table', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_23.2.xml::f360_23.2_11::fortify\n--  Disable Audit Assistant in order to allow users to gracefully transition to Audit Assistant G2\nUPDATE db_ssc_2222.configproperty SET propertyValue = 'false' WHERE propertyName='auditassistant.enabled';\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_11', 'fortify', 'dbF360_23.2.xml', NOW(), 823, '8:4aab4e7b2f0ff0c23d4dde095fc44d78', 'update tableName=configproperty', 'Disable Audit Assistant in order to allow users to gracefully transition to Audit Assistant G2', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_23.2.xml::f360_23.2_12_mysql_oracle::fortify\n--  Add indexes on variablehistory and measurementhistory to make project version state queries more efficient\nCREATE INDEX VARIABLEHISTORY_SNAPID_VARID_VARVALUE_IDX ON db_ssc_2222.variablehistory(snapshot_id, variable_id, variableValue);\n\nCREATE INDEX MEASUREMENTHISTORY_SNAPID_MSRID_MSRVALUE_IDX ON db_ssc_2222.measurementhistory(snapshot_id, measurement_id, measurementValue);\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_12_mysql_oracle', 'fortify', 'dbF360_23.2.xml', NOW(), 824, '8:3d23939b9a9500d05ed0649d42d3be48', 'createIndex indexName=VARIABLEHISTORY_SNAPID_VARID_VARVALUE_IDX, tableName=variablehistory; createIndex indexName=MEASUREMENTHISTORY_SNAPID_MSRID_MSRVALUE_IDX, tableName=measurementhistory', 'Add indexes on variablehistory and measurementhistory to make project version state queries more efficient', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_23.2.xml::f360_23.2_13::fortify\n--  Add index for jobqueue table\nCREATE INDEX JOBQUEUE_PVID_JOBCLASS_STATE_IDX ON db_ssc_2222.jobqueue(projectVersion_id, jobClassName, state);\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_13', 'fortify', 'dbF360_23.2.xml', NOW(), 825, '8:3e9fb1c313ab1088bb75d3827a95a565', 'createIndex indexName=JOBQUEUE_PVID_JOBCLASS_STATE_IDX, tableName=jobqueue', 'Add index for jobqueue table', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_23.2.xml::f360_23.2_14_mysql::fortify\n--  Add an index on a scan_issue table to improve performance during FPRs deletions.\nDROP INDEX SCAN_PV_IDX ON db_ssc_2222.scan_issue;\n\nCREATE INDEX SCANISSUE_PVID_SCANID_ISSUEID_IDX ON db_ssc_2222.scan_issue(projectVersion_id, scan_id, issue_id);\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_14_mysql', 'fortify', 'dbF360_23.2.xml', NOW(), 826, '8:8c1fda40198f2224d866c90f5174b1cc', 'dropIndex indexName=SCAN_PV_IDX, tableName=scan_issue; createIndex indexName=SCANISSUE_PVID_SCANID_ISSUEID_IDX, tableName=scan_issue', 'Add an index on a scan_issue table to improve performance during FPRs deletions.', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_23.2.xml::f360_23.2_15_mssql_mysql::fortify\n--  Allow storing longer tokens for bugtracker state management.\nALTER TABLE db_ssc_2222.bugstatemgmtconfig MODIFY password VARCHAR(4000);\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_23.2_15_mssql_mysql', 'fortify', 'dbF360_23.2.xml', NOW(), 827, '8:5f12a2a3156ccdbeaf832b4c5608549f', 'modifyDataType columnName=password, tableName=bugstatemgmtconfig', 'Allow storing longer tokens for bugtracker state management.', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360Mysql_23.2_scan_issue_id.xml::f360Mysql_23.2_scan_issue_id::fortify\n--  Update scan_issue ID from INT to BIGINT\nALTER TABLE scan_issue\n                MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT;\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360Mysql_23.2_scan_issue_id', 'fortify', 'dbF360Mysql_23.2_scan_issue_id.xml', NOW(), 828, '8:618a78a0fa7dd54837d58336474506b8', 'sql', 'Update scan_issue ID from INT to BIGINT', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_24.2.xml::f360_24.2_1::fortify\n--  Adding new column uiTheme to persist user preferred UI theme.\nALTER TABLE db_ssc_2222.userpreference ADD uiTheme VARCHAR(100) NULL;\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_1', 'fortify', 'dbF360_24.2.xml', NOW(), 829, '8:e54ec3f9f40e1ff0880ab4e79ad9630d', 'addColumn tableName=userpreference', 'Adding new column uiTheme to persist user preferred UI theme.', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_24.2.xml::f360_24.2_2::fortify\n--  Remove invalid tokens referencing removed token definitions.\nDELETE FROM db_ssc_2222.agentcredential WHERE type IN ('AuditToken', 'ScanCentralOneTimeJobToken', 'WIESystemToken', 'WIEUserToken');\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_2', 'fortify', 'dbF360_24.2.xml', NOW(), 830, '8:426c762d31dcce0cf63e1e050db6dbd2', 'delete tableName=agentcredential', 'Remove invalid tokens referencing removed token definitions.', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_24.2.xml::f360_24.2_3::fortify\n--  Remove unused requirement documents and associated file blobs\nDELETE FROM db_ssc_2222.datablob WHERE EXISTS (SELECT 1 FROM documentdef dd\n                INNER JOIN documentinfo di ON dd.templateInfo_id = di.id\n                WHERE di.fileBlob_id = datablob.id);\n\nDELETE FROM db_ssc_2222.documentinfo WHERE EXISTS (SELECT 1 FROM documentdef dd WHERE documentinfo.id = dd.templateInfo_id);\n\nDROP TABLE db_ssc_2222.documentdef;\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_3', 'fortify', 'dbF360_24.2.xml', NOW(), 831, '8:4e89b666da17655f266764d88a6fbfd6', 'delete tableName=datablob; delete tableName=documentinfo; dropTable tableName=documentdef', 'Remove unused requirement documents and associated file blobs', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_24.2.xml::f360_24.2_4::fortify\n--  Remove unused governance tables\nDROP TABLE db_ssc_2222.rtassignment;\n\nDROP TABLE db_ssc_2222.activity_persona;\n\nDROP TABLE db_ssc_2222.documentactivity;\n\nDROP TABLE db_ssc_2222.requirement_activity;\n\nDROP TABLE db_ssc_2222.timelapseactivity;\n\nDROP TABLE db_ssc_2222.projectstateactivity;\n\nDROP TABLE db_ssc_2222.activity;\n\nDROP TABLE db_ssc_2222.requirement_persona;\n\nDROP TABLE db_ssc_2222.requirement;\n\nDROP TABLE db_ssc_2222.projectpersonaassignment;\n\nDROP TABLE db_ssc_2222.requirementtemplate_persona;\n\nDROP TABLE db_ssc_2222.metadatarule;\n\nDROP TABLE db_ssc_2222.requirementtemplate;\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_4', 'fortify', 'dbF360_24.2.xml', NOW(), 832, '8:4f6e381b235cd2ad790e5d14f2bd1fed', 'dropTable tableName=rtassignment; dropTable tableName=activity_persona; dropTable tableName=documentactivity; dropTable tableName=requirement_activity; dropTable tableName=timelapseactivity; dropTable tableName=projectstateactivity; dropTable tabl...', 'Remove unused governance tables', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_24.2.xml::f360_24.2_5::fortify\n--  Remove unused governance alerts\nDELETE FROM db_ssc_2222.alert WHERE monitoredEntityType IN ('RT_INSTANCE', 'ACTIVITY_INSTANCE', 'REQ_INSTANCE');\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_5', 'fortify', 'dbF360_24.2.xml', NOW(), 833, '8:b7b4363a94cb5f6bfe943796db53e733', 'delete tableName=alert', 'Remove unused governance alerts', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_24.2.xml::f360_24.2_6::fortify\n--  Adding table storedobjectinfo to persist information relevant to object storage.\nCREATE TABLE db_ssc_2222.storedobjectinfo (objectKey VARCHAR(255) NOT NULL, creationDate datetime(3) NOT NULL, checksum VARCHAR(255) NULL, deletionRequestDate datetime(3) NULL, CONSTRAINT PK_STOREDOBJECTINFO PRIMARY KEY (objectKey));\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_6', 'fortify', 'dbF360_24.2.xml', NOW(), 834, '8:516551ac7c3fc4c713d8187560def4db', 'createTable tableName=storedobjectinfo', 'Adding table storedobjectinfo to persist information relevant to object storage.', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_24.2.xml::f360_24.2_6_part2::fortify\n--  Add storedObjectKey column to documentInfo\nALTER TABLE db_ssc_2222.documentinfo ADD storedObjectKey VARCHAR(255) NULL;\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_6_part2', 'fortify', 'dbF360_24.2.xml', NOW(), 835, '8:00404b2670ae61c9580e4ceb03ecf933', 'addColumn tableName=documentinfo', 'Add storedObjectKey column to documentInfo', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_24.2.xml::f360_24.2_7::fortify\n--  Add a new table to track failed Kafka events by project version\nCREATE TABLE db_ssc_2222.failedkafkaevent (projectVersion_id BIGINT NOT NULL, earliestAuditTime BIGINT NOT NULL, CONSTRAINT PK_FAILEDKAFKAEVENT PRIMARY KEY (projectVersion_id));\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_7', 'fortify', 'dbF360_24.2.xml', NOW(), 836, '8:3a1dd5ac8d8d7a06557f4806e94565a5', 'createTable tableName=failedkafkaevent', 'Add a new table to track failed Kafka events by project version', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_24.2.xml::f360_24.2_8::fortify\n--  Set issue status as Under Review when an issue has an audit history but its status is still set as Unreviewed.\n--              Ignore issues where primary tag has been set (audited) and audithistory changes where guid is user assignment and suppressed.\nUPDATE issue i\n                JOIN audithistory ah ON i.id = ah.issue_id AND i.issueStatus = 'Unreviewed' AND i.audited = 'N'\n                AND ah.attrGuid NOT IN ('userAssignment', '22222222-2222-2222-2222-222222222222')\n                SET i.issueStatus = 'Under Review';\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_8', 'fortify', 'dbF360_24.2.xml', NOW(), 837, '8:a0484b467f565fa943899dad2587f99d', 'sql; sql; sql', 'Set issue status as Under Review when an issue has an audit history but its status is still set as Unreviewed.\n            Ignore issues where primary tag has been set (audited) and audithistory changes where guid is user assignment and suppressed.', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_24.2.xml::f360_24.2_9::fortify\n--  Add table for data retention (cleanup) policies and add policy-related columns for projectVersion table.\nCREATE TABLE db_ssc_2222.dataretentionpolicy (id BIGINT AUTO_INCREMENT NOT NULL, guid VARCHAR(255) NOT NULL, objectVersion INT NOT NULL, name VARCHAR(255) NOT NULL, `description` VARCHAR(2000) NOT NULL, systemOwned VARCHAR(1) DEFAULT 'N' NOT NULL, minArtifacts INT NOT NULL, maxArtifacts INT NOT NULL, minArtifactAgeInDays INT NOT NULL, maxArtifactAgeInDays INT NOT NULL, CONSTRAINT PK_DR_POLICY PRIMARY KEY (id), CONSTRAINT UQ_DR_POLICY_GUID UNIQUE (guid), CONSTRAINT UQ_DR_POLICY_NAME UNIQUE (name));\n\nALTER TABLE db_ssc_2222.projectversion ADD dataRetentionPolicyOverride VARCHAR(1) DEFAULT 'N' NOT NULL, ADD dataRetentionPolicyGuid VARCHAR(255) NULL;\n\nCREATE INDEX PV_DATA_RETENTION_IDX ON db_ssc_2222.projectversion(dataRetentionPolicyOverride, dataRetentionPolicyGuid);\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_9', 'fortify', 'dbF360_24.2.xml', NOW(), 838, '8:1d101f59feb56abea7653f4de1a93ef6', 'createTable tableName=dataretentionpolicy; addColumn tableName=projectversion; createIndex indexName=PV_DATA_RETENTION_IDX, tableName=projectversion', 'Add table for data retention (cleanup) policies and add policy-related columns for projectVersion table.', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_24.2.xml::f360_24.2_10::fortify\n--  In 24.2, admins cannot create new policies but can edit and\/or enable the system-owned default policy. Pre-create the system-owned default policy entity with benign properties.\nINSERT INTO db_ssc_2222.dataretentionpolicy (guid, objectVersion, name, `description`, systemOwned, minArtifacts, maxArtifacts, minArtifactAgeInDays, maxArtifactAgeInDays) VALUES ('__DATA_RETENTION_SYSTEM_DEFAULT_POLICY__', 1, 'Default data retention policy', 'This system-owned policy is used for an application when the feature is enabled and the application has not opted-out of the default policy.', 'Y', 1000, 1000, 3650, 3650);\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_10', 'fortify', 'dbF360_24.2.xml', NOW(), 839, '8:77cd5d81f011a8bb00198409b4426e49', 'insert tableName=dataretentionpolicy; insert tableName=dataretentionpolicy', 'In 24.2, admins cannot create new policies but can edit and\/or enable the system-owned default policy. Pre-create the system-owned default policy entity with benign properties.', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_24.2.xml::f360_24.2_11::fortify\n--  Remove orphaned records in auditcomment\nDELETE FROM db_ssc_2222.auditcomment WHERE NOT EXISTS (SELECT 1 FROM issue i WHERE i.id = auditcomment.issue_id);\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_11', 'fortify', 'dbF360_24.2.xml', NOW(), 840, '8:b51b666d6d2750e155ed01e122ecc726', 'delete tableName=auditcomment', 'Remove orphaned records in auditcomment', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_24.2.xml::f360_24.2_12::fortify\n--  Delete orphaned documentinfo and datablob rows that correspond to associatedDocIds of artifacts belonging to deleted PVs\nDELETE di, db FROM documentinfo di\n                JOIN datablob db ON di.fileBlob_id = db.id\n                LEFT JOIN artifact a ON a.associatedDocInfo_id = di.id\n                    WHERE a.id IS NULL AND di.documentType=16;\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_12', 'fortify', 'dbF360_24.2.xml', NOW(), 841, '8:2d4ee5abdfaed1ac0e860b301ca50472', 'sql; sql; sql', 'Delete orphaned documentinfo and datablob rows that correspond to associatedDocIds of artifacts belonging to deleted PVs', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset dbF360_24.2.xml::f360_24.2_13::fortify\n--  Remove orphaned records in applicationentity and related tables\nDELETE FROM db_ssc_2222.projectversion_alert WHERE projectVersion_id IN (SELECT id FROM applicationentity\n                WHERE NOT EXISTS (SELECT 1 FROM projectversion\n                WHERE applicationentity.id = projectversion.id));\n\nDELETE FROM db_ssc_2222.alerthistory WHERE projectVersion_id IN (SELECT id FROM applicationentity\n                WHERE NOT EXISTS (SELECT 1 FROM projectversion\n                WHERE applicationentity.id = projectversion.id));\n\nDELETE FROM db_ssc_2222.metavalue WHERE projectVersion_id IN (SELECT id FROM applicationentity\n                WHERE NOT EXISTS (SELECT 1 FROM projectversion\n                WHERE applicationentity.id = projectversion.id));\n\nDELETE FROM db_ssc_2222.report_applicationentity WHERE applicationEntity_id IN (SELECT id FROM applicationentity\n                WHERE NOT EXISTS (SELECT 1 FROM projectversion\n                WHERE applicationentity.id = projectversion.id));\n\nDELETE FROM db_ssc_2222.runtimeapplication;\n\nDELETE FROM db_ssc_2222.applicationentity WHERE NOT EXISTS (SELECT 1 FROM projectversion\n                WHERE applicationentity.id = projectversion.id);\n\nINSERT INTO db_ssc_2222.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('f360_24.2_13', 'fortify', 'dbF360_24.2.xml', NOW(), 842, '8:98d04489fa98f402050b6c32439a1afb', 'delete tableName=projectversion_alert; delete tableName=alerthistory; delete tableName=metavalue; delete tableName=report_applicationentity; delete tableName=runtimeapplication; delete tableName=applicationentity', 'Remove orphaned records in applicationentity and related tables', 'EXECUTED', NULL, NULL, '4.10.0', '0148965608');\n\n--  Changeset procs\/dbF360_updateExistingWithLatest.xml::updateExistingWithLatest_mysql::hp_main\nDROP PROCEDURE IF EXISTS updateExistingWithLatest;\n\nDELIMITER \/\/\nCREATE PROCEDURE updateExistingWithLatest    (p_scan_id INT,\n     p_projectVersion_id INT,\n     p_foundDate BIGINT,\n     p_folder_id INT,\n     p_customPriorityEnabled INT\n    )\nBEGIN\n    INSERT INTO issue_extension(issue_id, projectVersion_id, issueInstanceId, engineType, reintroducedDate)\n        SELECT i.id, p_projectVersion_id, i.issueInstanceId, i.engineType, p_foundDate\n        FROM issue i INNER JOIN scan_issue si ON i.engineType = si.engineType AND i.issueInstanceId = si.issueInstanceId AND i.projectVersion_id = si.projectVersion_id\n        WHERE si.scan_id = p_scan_id AND si.projectVersion_id = p_projectVersion_id AND i.scanStatus = 'REMOVED'\n    ON DUPLICATE KEY UPDATE issue_extension.reintroducedDate = p_foundDate;\n\n    insert into issue (lastScan_Id, scanStatus, issueInstanceId, projectVersion_Id, engineType\n        , foundDate, shortFileName, fileName, severity, confidence, kingdom\n        , issueType, issueSubtype, analyzer, lineNumber, taintFlag, packageName\n        , functionName, className, issueAbstract, issueRecommendation, friority\n        , replaceStore, ruleGuid, findingGuid, snippetId, contextId, category\n        , url, source, sourceContext, sink, sinkContext, sourceFile\n        , audienceSet, remediationConstant, likelihood, probability, impact\n        , accuracy, rtaCovered, requestIdentifier, requestHeader, requestParameter\n        , requestBody, requestMethod, cookie, httpVersion, attackPayload, attackType\n        , attackTriggerDefinition , response, triggerDefinition, triggerString\n        , triggerDisplayText, secondaryRequest, sourceLine, mappedCategory\n        , vulnerableParameter, reproStepDefinition, stackTrace, stackTraceTriggerDisplayText\n        , manual, minVirtualCallConfidence, hidden, folder_Id, objectVersion)\n\tselect scan_Id, 'NEW', issueInstanceId, projectVersion_Id, engineType, p_foundDate\n        , shortFileName, fileName, severity, confidence, kingdom, issueType, issueSubtype\n        , analyzer, lineNumber, taintFlag, packageName, functionName, className, issueAbstract\n        , issueRecommendation, friority, replaceStore, ruleGuid, findingGuid, snippetId, contextId\n        , category, url, source, sourceContext, sink, sinkContext, sourceFile, audienceSet\n        , remediationConstant, likelihood, probability, impact, accuracy, rtaCovered\n        , requestIdentifier, requestHeader, requestParameter, requestBody, requestMethod, cookie\n        , httpVersion, attackPayload, attackType, attackTriggerDefinition, response\n        , triggerDefinition, triggerString, triggerDisplayText, secondaryRequest, sourceLine\n        , mappedCategory, vulnerableParameter, reproStepDefinition, stackTrace\n        , stackTraceTriggerDisplayText, manual, minVirtualCallConfidence, 'N', p_folder_id, 0 from scan_issue si where si.projectVersion_id = p_projectVersion_id and si.scan_id = p_scan_id\n\t on duplicate key update issue.lastScan_Id= si.scan_id\n        , issue.shortFileName=si.shortFileName, issue.fileName=si.fileName, issue.severity=si.severity, issue.confidence=si.confidence, issue.kingdom=si.kingdom, issue.issueType=si.issueType\n        , issue.issueSubtype=si.issueSubtype, issue.analyzer=si.analyzer, issue.engineType=si.engineType, issue.lineNumber=si.lineNumber, issue.taintFlag=si.taintFlag, issue.packageName=si.packageName\n        , issue.functionName=si.functionName, issue.className=si.className, issue.issueAbstract=si.issueAbstract, issue.issueRecommendation=si.issueRecommendation\n        , issue.friority = (case when p_customPriorityEnabled = 0 or issue.enginePriority is null then si.friority else issue.friority end)\n        , issue.enginePriority = (case when p_customPriorityEnabled = 0 or issue.enginePriority is null then null else si.friority end)\n\t    , issue.replaceStore=si.replaceStore\n        , issue.ruleGuid=si.ruleGuid, issue.snippetId=si.snippetId, issue.contextId=si.contextId, issue.category=si.category, issue.url=si.url, issue.source=si.source, issue.sourceContext=si.sourceContext, issue.sink=si.sink\n        , issue.sinkContext=si.sinkContext, issue.sourceFile=si.sourceFile, issue.audienceSet=si.audienceSet\n        , issue.scanStatus = (CASE WHEN issue.scanStatus='REMOVED' THEN 'REINTRODUCED' ELSE 'UPDATED' END)\n        , issue.findingGuid = si.findingGuid, issue.remediationConstant=si.remediationConstant,issue.likelihood=si.likelihood,issue.impact=si.impact, issue.accuracy=si.accuracy\n        , issue.rtaCovered=si.rtaCovered,issue.probability=si.probability\n        , issue.requestIdentifier=si.requestIdentifier, issue.requestHeader=si.requestHeader, issue.requestParameter=si.requestParameter, issue.requestBody=si.requestBody, issue.requestMethod=si.requestMethod\n        , issue.cookie=si.cookie, issue.httpVersion=si.httpVersion, issue.attackPayload=si.attackPayload, issue.attackType=si.attackType, issue.response=si.response, issue.triggerDefinition=si.triggerDefinition, issue.triggerString=si.triggerString\n        , issue.triggerDisplayText=si.triggerDisplayText, issue.secondaryRequest=si.secondaryRequest, issue.sourceLine=si.sourceLine, issue.mappedCategory=si.mappedCategory\n        , issue.minVirtualCallConfidence=si.minVirtualCallConfidence;\n\n    insert into issue_ca (issue_id, projectVersion_id, issueInstanceId, engineType, dataVersion, metadataPluginId,\n\t\t\tintegerValue01, integerValue02, integerValue03, decimalValue01, decimalValue02, decimalValue03, decimalValue04, decimalValue05,\n\t\t\tdateValue01, dateValue02, dateValue03, dateValue04, dateValue05,\n\t\t\ttextValue01, textValue02, textValue03, textValue04, textValue05, textValue06, textValue07, textValue08, textValue09, textValue10, textValue11, textValue12, textValue13, textValue14, textValue15, textValue16,\n\t\t\tclobValue01, clobValue02, clobValue03, clobValue04, clobValue05, clobValue06)\n\tselect i.id, sica.projectVersion_id, sica.issueInstanceId, sica.engineType, sica.dataVersion, sica.metadataPluginId,\n\t\t\tsica.integerValue01, sica.integerValue02, sica.integerValue03, sica.decimalValue01, sica.decimalValue02, sica.decimalValue03, sica.decimalValue04, sica.decimalValue05,\n\t\t\tsica.dateValue01, sica.dateValue02, sica.dateValue03, sica.dateValue04, sica.dateValue05,\n\t\t\tsica.textValue01, sica.textValue02, sica.textValue03, sica.textValue04, sica.textValue05, sica.textValue06, sica.textValue07, sica.textValue08, sica.textValue09, sica.textValue10, sica.textValue11, sica.textValue12, sica.textValue13, sica.textValue14, sica.textValue15, sica.textValue16,\n\t\t\tsica.clobValue01, sica.clobValue02, sica.clobValue03, sica.clobValue04, sica.clobValue05, sica.clobValue06\n\t    from scan_issue_ca sica\n\t    join issue i ON i.engineType=sica.engineType AND i.issueInstanceId=sica.issueInstanceId where sica.scan_id = p_scan_id AND sica.projectVersion_id = p_projectVersion_id AND i.projectVersion_id=p_projectVersion_id\n\ton duplicate key update issue_ca.issueInstanceId=sica.issueInstanceId, issue_ca.engineType=sica.engineType, issue_ca.dataVersion=sica.dataVersion,\n\t\t\tissue_ca.integerValue01=sica.integerValue01, issue_ca.integerValue02=sica.integerValue02, issue_ca.integerValue03=sica.integerValue03, issue_ca.decimalValue01=sica.decimalValue01, issue_ca.decimalValue02=sica.decimalValue02, issue_ca.decimalValue03=sica.decimalValue03, issue_ca.decimalValue04=sica.decimalValue04, issue_ca.decimalValue05=sica.decimalValue05,\n\t\t\tissue_ca.dateValue01=sica.dateValue01, issue_ca.dateValue02=sica.dateValue02, issue_ca.dateValue03=sica.dateValue03, issue_ca.dateValue04=sica.dateValue04, issue_ca.dateValue05=sica.dateValue05,\n\t\t\tissue_ca.textValue01=sica.textValue01, issue_ca.textValue02=sica.textValue02, issue_ca.textValue03=sica.textValue03, issue_ca.textValue04=sica.textValue04, issue_ca.textValue05=sica.textValue05, issue_ca.textValue06=sica.textValue06, issue_ca.textValue07=sica.textValue07, issue_ca.textValue08=sica.textValue08, issue_ca.textValue09=sica.textValue09, issue_ca.textValue10=sica.textValue10,\n\t\t\tissue_ca.textValue11=sica.textValue11, issue_ca.textValue12=sica.textValue12, issue_ca.textValue13=sica.textValue13, issue_ca.textValue14=sica.textValue14, issue_ca.textValue15=sica.textValue15, issue_ca.textValue16=sica.textValue16,\n\t\t\tissue_ca.clobValue01=sica.clobValue01, issue_ca.clobValue02=sica.clobValue02, issue_ca.clobValue03=sica.clobValue03, issue_ca.clobValue04=sica.clobValue04, issue_ca.clobValue05=sica.clobValue05, issue_ca.clobValue06=sica.clobValue06;\n\nEND\/\/\nDELIMITER ;\n\nUPDATE db_ssc_2222.DATABASECHANGELOG SET COMMENTS = '', CONTEXTS = NULL, DATEEXECUTED = NOW(), DEPLOYMENT_ID = '0148965608', EXECTYPE = 'RERAN', LABELS = NULL, MD5SUM = '8:eb053150f34f5adc61b0b16f91762166', ORDEREXECUTED = 843 WHERE ID = 'updateExistingWithLatest_mysql' AND AUTHOR = 'hp_main' AND FILENAME = 'procs\/dbF360_updateExistingWithLatest.xml';\n\n--  Release Database Lock\nUPDATE db_ssc_2222.DATABASECHANGELOGLOCK SET `LOCKED` = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;<\/pre>\n","protected":false},"excerpt":{"rendered":"
1. MySQL Database \ubc31\uc5c5  mysqldump –single-transaction –routines –triggers –max_allowed_packet=2048M -u root -p<Password> db_ssc_2222 > 20240801_ssc_backup.sql 2. SSC \uc124\uce58 \uc9c4\ud589  Database \ud56d\ubaa9\uc5d0\uc11c DB Connection \ud14c\uc2a4\ud2b8 \ud6c4 \uc6b0\uce21\ud558\ub2e8 \uc2a4\ud06c\ub9bd\ud2b8 \ub2e4\uc6b4\ub85c\ub4dc\uc5d0\uc11c ssc-migration.sql \ub2e4\uc6b4\ub85c\ub4dc  (\ud558\ub2e8 \uadf8\ub9bc \ucc38\uace0) 3. ssc-migration.sql \uc2a4\ud06c\ub9bd\ud2b8\ub85c DB \ub9c8\uc774\uadf8\ub808\uc774\uc158 \uc9c4\ud589  mysql -u root -p<Password> db_ssc_2222 < ssc-migration.sql   \ub9cc\uc77c \uc624\ub958 \ubc1c\uc0dd \uc2dc \uc544\ub798\uc758 \uc2a4\ud06c\ub9bd\ud2b8\ub97c \uc2e4\ud589\ud558\uc5ec \uc6d0\uc0c1 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"set","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[66],"tags":[],"class_list":["post-8167","post","type-post","status-publish","format-standard","hentry","category-computing_fortify"],"_links":{"self":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/8167","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8167"}],"version-history":[{"count":0,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/8167\/revisions"}],"wp:attachment":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8167"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8167"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8167"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}