(v1) MySQL

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

-- 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';

-- 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 ;

sudo /sbin/chkconfig --add mysqld

sudo /sbin/chkconfig --list mysqld

sudo /sbin/chkconfig mysqld on