(v1) MySQL

MySQL version 5.7 or greater is required.

Install Notes

Included in the installation package is a file named mysql-create.sql; log into the main database and execute these commands. Also included is a file named mysql-create-metadata.sql; log into the metadata database and execute these commands.

Installation

We highly recommend installing MySQL on a dedicated database server. To communicate with MySQL on that server completing this installation will require the username, password, hostname and port (e.g. 3306).

Configuration

Configuring MySQL can be necessary to ensure optimal performance. These options are not required but could help with performance.

vi /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

max_allowed_packet = 32M
query_cache_size = 67108864
innodb_buffer_pool_size=1024M
sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
innodb_fast_shutdown=0

thread_cache_size = 8
max_connections = 30
key_buffer_size = 4M
query_cache_size = 256M
log_queries_not_using_indexes = 0
slow_query_log = 1
slow_query_log_file=/var/log/mysqld-slow.log
long_query_time = 7
table_open_cache = 810
table_definition_cache = 800
query_cache_min_res_unit = 2048
query_cache_limit = 2M
join_buffer_size = 16M
innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 1M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Run the Create Script MySQL 8

-- Create Test databases
CREATE DATABASE pcr360_test DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE pcr360_test_archive DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE pcr360_test_metadata DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create the users
CREATE USER 'pcr360_test'@'%' IDENTIFIED WITH mysql_native_password BY '<password>';
CREATE USER 'pcr360_test'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';
GRANT USAGE ON *.* TO 'pcr360_test'@'%';
GRANT USAGE ON *.* TO 'pcr360_test'@'localhost';

CREATE USER 'pcr360_test_archive'@'%' IDENTIFIED WITH mysql_native_password BY '<password>';
CREATE USER 'pcr360_test_archive'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';
GRANT USAGE ON *.* TO 'pcr360_test_archive'@'%';
GRANT USAGE ON *.* TO 'pcr360_test_archive'@'localhost';

CREATE USER 'pcr360_test_metadata'@'%' IDENTIFIED WITH mysql_native_password BY '<password>';
CREATE USER 'pcr360_test_metadata'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';
GRANT USAGE ON *.* TO 'pcr360_test_metadata'@'%';
GRANT USAGE ON *.* TO 'pcr360_test_metadata'@'localhost';

CREATE USER 'dmpusr'@'localhost' IDENTIFIED WITH mysql_native_password BY '<dmpuser-password>';
GRANT USAGE ON *.* TO 'dmpusr'@'localhost';
GRANT PROCESS ON *.* TO 'dmpusr'@'localhost';
 -- Grant database privileges
GRANT ALL PRIVILEGES ON pcr360_test.* TO 'pcr360_test'@'%';
GRANT ALL PRIVILEGES ON pcr360_test.* TO 'pcr360_test'@'localhost';
GRANT ALL PRIVILEGES ON pcr360_test_archive.* TO 'pcr360_test'@'%';
GRANT ALL PRIVILEGES ON pcr360_test_archive.* TO 'pcr360_test'@'localhost';
GRANT ALL PRIVILEGES ON pcr360_test_metadata.* TO 'pcr360_test_metadata'@'%';
GRANT ALL PRIVILEGES ON pcr360_test_metadata.* TO 'pcr360_test_metadata'@'localhost';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_test.* TO 'dmpusr'@'localhost';  
GRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_test_archive.* TO 'dmpusr'@'localhost';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_test_metadata.* TO 'dmpusr'@'localhost';
 

-- Create Production databases 
CREATE DATABASE pcr360_prod DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE pcr360_prod_archive DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE pcr360_prod_metadata DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create the users
CREATE USER 'pcr360_prod'@'%' IDENTIFIED WITH mysql_native_password BY '<password>';
CREATE USER 'pcr360_prod'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';
GRANT USAGE ON *.* TO 'pcr360_prod'@'%';
GRANT USAGE ON *.* TO 'pcr360_prod'@'localhost';

CREATE USER 'pcr360_prod_archive'@'%' IDENTIFIED WITH mysql_native_password BY '<password>';
CREATE USER 'pcr360_prod_archive'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';
GRANT USAGE ON *.* TO 'pcr360_prod_archive'@'%';
GRANT USAGE ON *.* TO 'pcr360_prod_archive'@'localhost';

CREATE USER 'pcr360_prod_metadata'@'%' IDENTIFIED WITH mysql_native_password BY '<password>';
CREATE USER 'pcr360_prod_metadata'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';
GRANT USAGE ON *.* TO 'pcr360_prod_metadata'@'%';
GRANT USAGE ON *.* TO 'pcr360_prod_metadata'@'localhost';
CREATE USER 'dmpusr'@'localhost' IDENTIFIED WITH mysql_native_password BY '<dmpuser-password>';
GRANT USAGE ON *.* TO 'dmpusr'@'localhost';
GRANT PROCESS ON *.* TO 'dmpusr'@'localhost';

 -- Grant database privileges
GRANT ALL PRIVILEGES ON pcr360_prod.* TO 'pcr360_prod'@'%';
GRANT ALL PRIVILEGES ON pcr360_prod.* TO 'pcr360_prod'@'localhost';
GRANT ALL PRIVILEGES ON pcr360_prod_archive.* TO 'pcr360_prod'@'%';
GRANT ALL PRIVILEGES ON pcr360_prod_archive.* TO 'pcr360_prod'@'localhost';
GRANT ALL PRIVILEGES ON pcr360_prod_metadata.* TO 'pcr360_prod_metadata'@'%';
GRANT ALL PRIVILEGES ON pcr360_prod_metadata.* TO 'pcr360_prod_metadata'@'localhost';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_prod.* TO 'dmpusr'@'localhost';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_prod.* TO 'dmpusr'@'localhost';

Run the Create Script MySQL 5.7

-- Create Test databases
CREATE DATABASE pcr360_test DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE pcr360_test_archive DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE pcr360_test_metadata DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create the users
CREATE USER 'pcr360_test'@'%' IDENTIFIED BY '<prod-password>';
CREATE USER 'pcr360_test'@'localhost' IDENTIFIED BY '<prod-password>'; 
GRANT USAGE ON *.* TO 'pcr360_test'@'%';
GRANT USAGE ON *.* TO 'pcr360_test'@'localhost';

CREATE USER 'pcr360_test_archive'@'%' IDENTIFIED BY '<prod-password>';
CREATE USER 'pcr360_test_archive'@'localhost' IDENTIFIED BY '<prod-password>';
GRANT USAGE ON *.* TO 'pcr360_test_archive'@'%';
GRANT USAGE ON *.* TO 'pcr360_test_archive'@'localhost';

CREATE USER 'pcr360_test_metadata'@'%' IDENTIFIED BY '<metadata-password>';
CREATE USER 'pcr360_test_metadata'@'localhost' IDENTIFIED BY '<metadata-password>'; 
GRANT USAGE ON *.* TO 'pcr360_test_metadata'@'%';
GRANT USAGE ON *.* TO 'pcr360_test_metadata'@'localhost';

CREATE USER 'dmpusr'@'localhost' IDENTIFIED BY '<dmpuser-password>';
GRANT USAGE ON *.* TO 'dmpusr'@'localhost';
-- Grant database privileges
GRANT ALL PRIVILEGES ON pcr360_test.* TO 'pcr360_test'@'%' IDENTIFIED BY '<prod-password>';
GRANT ALL PRIVILEGES ON pcr360_test.* TO 'pcr360_test'@'localhost' IDENTIFIED BY '<prod-password>';
GRANT ALL PRIVILEGES ON pcr360_test_metadata.* TO 'pcr360_test_metadata'@'%' IDENTIFIED BY '<metadata-password>';
GRANT ALL PRIVILEGES ON pcr360_test_metadata.* TO 'pcr360_test_metadata'@'localhost' IDENTIFIED BY '<metadata-password>';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_test.* TO 'dmpusr'@'localhost' IDENTIFIED BY '<dmpuser-password>'; 
GRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_test_archive.* TO 'dmpusr'@'localhost' IDENTIFIED BY '<dmpuser-password>';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_test_metadata.* TO 'dmpusr'@'localhost' IDENTIFIED BY '<dmpuser-password>';

-- Create Production databases
CREATE DATABASE pcr360_prod DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE pcr360_prod_metadata DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;  
CREATE DATABASE pcr360_prod_archive DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create the users
CREATE USER 'pcr360_prod'@'%' IDENTIFIED BY '<prod-password>';
CREATE USER 'pcr360_prod'@'localhost' IDENTIFIED BY '<prod-password>';
GRANT USAGE ON *.* TO 'pcr360_prod'@'%';
GRANT USAGE ON *.* TO 'pcr360_prod'@'localhost';

CREATE USER 'pcr360_prod_archive'@'%' IDENTIFIED BY '<prod-password>';
CREATE USER 'pcr360_prod_archive'@'localhost' IDENTIFIED BY '<prod-password>';
GRANT USAGE ON *.* TO 'pcr360_prod_archive'@'%';
GRANT USAGE ON *.* TO 'pcr360_prod_archive'@'localhost';

CREATE USER 'pcr360_prod_metadata'@'%' IDENTIFIED BY '<metadata-password>';
CREATE USER 'pcr360_prod_metadata'@'localhost' IDENTIFIED BY '<metadata-password>'; 
GRANT USAGE ON *.* TO 'pcr360_prod_metadata'@'%';
GRANT USAGE ON *.* TO 'pcr360_prod_metadata'@'localhost';

CREATE USER 'dmpusr'@'localhost' IDENTIFIED BY '<dmpuser-password>';
GRANT USAGE ON *.* TO 'dmpusr'@'localhost';
-- Grant database privileges
GRANT ALL PRIVILEGES ON pcr360_prod.* TO 'pcr360_prod'@'%' IDENTIFIED BY '<prod-password>';
GRANT ALL PRIVILEGES ON pcr360_prod.* TO 'pcr360_prod'@'localhost' IDENTIFIED BY '<prod-password>';
GRANT ALL PRIVILEGES ON pcr360_prod_metadata.* TO 'pcr360_prod_metadata'@'%' IDENTIFIED BY '<metadata-password>';
GRANT ALL PRIVILEGES ON pcr360_prod_metadata.* TO 'pcr360_prod_metadata'@'localhost' IDENTIFIED BY '<metadata-password>';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_prod.* TO 'dmpusr'@'localhost' IDENTIFIED BY '<dmpuser-password>';
GRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_prod.* TO 'dmpusr'@'localhost' IDENTIFIED BY '<dmpuser-password>';

-- needs to be run on prod and test
-- use pcr360_test;
use pcr360_prod;
 
DROP FUNCTION IF EXISTS FN_FISCAL_YEAR;
DELIMITER $$
CREATE FUNCTION FN_FISCAL_YEAR (
    check_date DATETIME,
    rollover_month INT,
    rollover_day INT,
    roll_forward TINYINT(1)
) RETURNS INT DETERMINISTIC
    BEGIN
        DECLARE cur_year INT DEFAULT EXTRACT(YEAR FROM CURRENT_DATE);
        # Adjust the current year: if FISCAL year rolls forward as the next ACTUAL year, this adds 1 to current.
        IF (roll_forward = 1) THEN
            SET cur_year := cur_year + 1;
        END IF;
        #
        RETURN cur_year - CAST(
            (DATEDIFF(
                 check_date,
                 CONCAT_WS('-', EXTRACT(YEAR FROM CURRENT_DATE), rollover_month, rollover_day)
             ) < 0)
            AS UNSIGNED
        );
    END$$
# End of Function
DELIMITER ;
 
DROP PROCEDURE IF EXISTS AUDIT_CLEANUP;
DELIMITER $$
CREATE PROCEDURE AUDIT_CLEANUP(IN tableName varchar(40))
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE i INT;
    DECLARE tableRecId INT;
    DECLARE auditRecId INT;
    DECLARE fromData LONGTEXT;
    DECLARE toData LONGTEXT;
    DECLARE currRecId INT DEFAULT 0;
    DECLARE currKey VARCHAR(32);
    DECLARE deleteFlag INT DEFAULT 0;
    DECLARE keysMatch INT;
 
    -- Query the data from the view
    DECLARE curs CURSOR FOR
        SELECT RECID, AUDIT_RECID, FROM_DATA, TO_DATA FROM V_AUDIT_CLEANUP;
 
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
 
    -- We can't have a dynamic table name in a cursor so we create a view with the dynamic table name and the cursor queries the view
    SET @v = concat(
            "CREATE OR REPLACE VIEW V_AUDIT_CLEANUP AS SELECT RECID, JSON_EXTRACT(FROM_DATA, '$.RECID') AS AUDIT_RECID, JSON_REMOVE(FROM_DATA, '$.MODIFIED_DATE', '$.MODIFIED_BY') AS FROM_DATA, JSON_REMOVE(TO_DATA, '$.MODIFIED_DATE', '$.MODIFIED_BY') AS TO_DATA FROM ",
            tableName, " WHERE FROM_DATA IS NOT NULL ORDER BY JSON_EXTRACT(FROM_DATA, '$.RECID'), MODIFIED_DATE");
    PREPARE stm FROM @v;
    EXECUTE stm;
    DEALLOCATE PREPARE stm;
 
    OPEN curs;
 
    SET done = 0;
 
    getRecsLoop:
    loop
 
        FETCH curs INTO tableRecId, auditRecId, fromData, toData;
 
        SET keysMatch = 1;
 
        -- We need to keep track of the current recid so that we know when we get to a new set of records and reset the delete flag
        IF auditRecId != currRecId
        THEN
            SET currRecId = auditRecId;
            SET deleteFlag = 0;
        END IF;
 
        -- So let's first figure out if the number of keys match in FROM_DATA and TO_DATA
        -- If the number of keys match then we need to check each individual value
        -- If the number of keys do not match then we can skip checking the individual values
        IF JSON_LENGTH(JSON_KEYS(fromData)) = JSON_LENGTH(JSON_KEYS(toData))
        THEN
            SET i = 0;
            jsonLoop:
            WHILE i < JSON_LENGTH(JSON_KEYS(fromData))
                DO
                    SET currKey = JSON_EXTRACT(JSON_KEYS(fromData), CONCAT("$[", i, "]"));
 
                    -- I am casting the values to CHAR and trimming double quotes as there is an issue with ints being stored in the JSON as both ints and strings
                    -- https://pcr360.myjetbrains.com/youtrack/issue/PCR360-3188
                    IF TRIM(BOTH '"' FROM CAST(JSON_EXTRACT(fromData, CONCAT("$.", currKey)) AS CHAR)) !=
                       TRIM(BOTH '"' FROM CAST(JSON_EXTRACT(toData, CONCAT("$.", currKey)) as CHAR))
                    THEN
                        -- values don't match this is a valid record, unset the flag and leave the loop
                        SET keysMatch = 0;
                        LEAVE jsonLoop;
                    END IF;
                    SET i = i + 1;
                END WHILE jsonLoop;
            -- The number of keys don't match so this is a valid record
        ELSE
            SET keysMatch = 0;
        END IF;
 
        -- We have the same number of keys and all of the values match let's check if this is the oldest record
        IF keysMatch
        THEN
            -- We have ordered by modified date so the first record we come across will be the oldest so let's leave it alone
            IF deleteFlag = 0
            THEN
                SET deleteFlag = 1;
                -- We have already found the oldest record so let's delete the rest
            ELSE
                SET @sql = CONCAT("DELETE FROM ", tableName, " WHERE RECID = ?");
                PREPARE stm from @sql;
                SET @a = tableRecId;
                EXECUTE stm using @a;
                DEALLOCATE PREPARE stm;
            END IF;
        END IF;
 
        COMMIT;
        IF done
        THEN
            LEAVE getRecsLoop;
        END IF;
 
    END LOOP;
 
    CLOSE curs;
END$$
DELIMITER ;
 
DROP FUNCTION IF EXISTS fnStripTags;
DELIMITER $$
create function fnStripTags(Dirty varchar(4000)) returns varchar(4000) deterministic
BEGIN
    DECLARE iStart, iEnd, iLength INT;
    WHILE Locate('<', Dirty) > 0 AND Locate('>', Dirty, Locate('<', Dirty)) > 0
        DO
            BEGIN
                SET iStart = Locate('<', Dirty), iEnd = Locate('>', Dirty, Locate('<', Dirty));
                SET iLength = (iEnd - iStart) + 1;
                IF iLength > 0
                THEN
                    BEGIN
                        SET Dirty = Insert(Dirty, iStart, iLength, '');
                    END;
                END IF;
            END;
        END WHILE;
    RETURN Dirty;
END$$
DELIMITER ;

Set MySQL to Start on Boot

1. Add it to chkconfig

sudo /sbin/chkconfig --add mysqld

2. Make sure it is in the chkconfig.

sudo /sbin/chkconfig --list mysqld

3. Set it to autostart

sudo /sbin/chkconfig mysqld on

PCR-360 (Cloud-Archive)