{"type":"doc","content":[{"type":"paragraph","content":[{"text":"MySQL version 5.7 or greater is required.","type":"text","marks":[{"type":"strong"}]}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"toc","parameters":{"macroParams":{"outline":{"value":"true"},"style":{"value":"none"}},"macroMetadata":{"macroId":{"value":"6a1c1213-0a8b-4fb3-9d20-e1f723a257f0"},"schemaVersion":{"value":"1"},"title":"Table of Contents"}},"localId":"8e853aea-0b03-4306-b522-614ed97e49b6"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Install Notes","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"paragraph","content":[{"text":"Note","type":"text","marks":[{"type":"strong"}]},{"text":": For MySQL the database ","type":"text"},{"text":"must be configured with the UTF8 character set","type":"text","marks":[{"type":"strong"}]},{"text":". For MySQL 8 ","type":"text"},{"text":"utf8mb4","type":"text","marks":[{"type":"code"}]},{"text":" and ","type":"text"},{"text":"utf8mb4_unicode_ci","type":"text","marks":[{"type":"code"}]},{"text":" are supported as well. Currently, other character sets and collations are not supported.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Installation","type":"text"}]},{"type":"paragraph","content":[{"text":"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).","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Configuration","type":"text"}]},{"type":"paragraph","content":[{"text":"Configuring MySQL can be necessary to ensure optimal performance. These options are not required but could help with performance.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"shell"},"content":[{"text":"vi /etc/my.cnf\n","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"codeBlock","attrs":{"language":"plaintext"},"content":[{"text":"[mysqld]\ndatadir=/var/lib/mysql\nsocket=/var/lib/mysql/mysql.sock\nuser=mysql\n# Disabling symbolic-links is recommended to prevent assorted security risks\nsymbolic-links=0\n\nmax_allowed_packet = 32M\nquery_cache_size = 67108864\ninnodb_buffer_pool_size=1024M\nsql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'\ninnodb_fast_shutdown=0\n\nthread_cache_size = 8\nmax_connections = 30\nkey_buffer_size = 4M\nquery_cache_size = 256M\nlog_queries_not_using_indexes = 0\nslow_query_log = 1\nslow_query_log_file=/var/log/mysqld-slow.log\nlong_query_time = 7\ntable_open_cache = 810\ntable_definition_cache = 800\nquery_cache_min_res_unit = 2048\nquery_cache_limit = 2M\njoin_buffer_size = 16M\ninnodb_buffer_pool_size = 4G\ninnodb_log_buffer_size = 1M\n\n[mysqld_safe]\nlog-error=/var/log/mysqld.log\npid-file=/var/run/mysqld/mysqld.pid","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Run the Create Script MySQL 8","type":"text"}]},{"type":"paragraph","content":[{"text":"Note","type":"text","marks":[{"type":"strong"}]},{"text":": For MySQL the database ","type":"text"},{"text":"must be configured with the UTF8 character set","type":"text","marks":[{"type":"strong"}]},{"text":". For MySQL 8 ","type":"text"},{"text":"utf8mb4","type":"text","marks":[{"type":"code"}]},{"text":" and ","type":"text"},{"text":"utf8mb4_unicode_ci","type":"text","marks":[{"type":"code"}]},{"text":" are supported as well. Currently, other character sets and collations are not supported.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"-- Create Test databases\nCREATE DATABASE pcr360_test DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\nCREATE DATABASE pcr360_test_archive DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\nCREATE DATABASE pcr360_test_metadata DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\n-- Create the users\nCREATE USER 'pcr360_test'@'%' IDENTIFIED WITH mysql_native_password BY '';\nCREATE USER 'pcr360_test'@'localhost' IDENTIFIED WITH mysql_native_password BY '';\nGRANT USAGE ON *.* TO 'pcr360_test'@'%';\nGRANT USAGE ON *.* TO 'pcr360_test'@'localhost';\n\nCREATE USER 'pcr360_test_archive'@'%' IDENTIFIED WITH mysql_native_password BY '';\nCREATE USER 'pcr360_test_archive'@'localhost' IDENTIFIED WITH mysql_native_password BY '';\nGRANT USAGE ON *.* TO 'pcr360_test_archive'@'%';\nGRANT USAGE ON *.* TO 'pcr360_test_archive'@'localhost';\n\nCREATE USER 'pcr360_test_metadata'@'%' IDENTIFIED WITH mysql_native_password BY '';\nCREATE USER 'pcr360_test_metadata'@'localhost' IDENTIFIED WITH mysql_native_password BY '';\nGRANT USAGE ON *.* TO 'pcr360_test_metadata'@'%';\nGRANT USAGE ON *.* TO 'pcr360_test_metadata'@'localhost';\n\nCREATE USER 'dmpusr'@'localhost' IDENTIFIED WITH mysql_native_password BY '';\nGRANT USAGE ON *.* TO 'dmpusr'@'localhost';\nGRANT PROCESS ON *.* TO 'dmpusr'@'localhost';\n -- Grant database privileges\nGRANT ALL PRIVILEGES ON pcr360_test.* TO 'pcr360_test'@'%';\nGRANT ALL PRIVILEGES ON pcr360_test.* TO 'pcr360_test'@'localhost';\nGRANT ALL PRIVILEGES ON pcr360_test_archive.* TO 'pcr360_test'@'%';\nGRANT ALL PRIVILEGES ON pcr360_test_archive.* TO 'pcr360_test'@'localhost';\nGRANT ALL PRIVILEGES ON pcr360_test_metadata.* TO 'pcr360_test_metadata'@'%';\nGRANT ALL PRIVILEGES ON pcr360_test_metadata.* TO 'pcr360_test_metadata'@'localhost';\nGRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_test.* TO 'dmpusr'@'localhost'; \nGRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_test_archive.* TO 'dmpusr'@'localhost';\nGRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_test_metadata.* TO 'dmpusr'@'localhost';\n \n\n-- Create Production databases \nCREATE DATABASE pcr360_prod DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\nCREATE DATABASE pcr360_prod_archive DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\nCREATE DATABASE pcr360_prod_metadata DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\n\n-- Create the users\nCREATE USER 'pcr360_prod'@'%' IDENTIFIED WITH mysql_native_password BY '';\nCREATE USER 'pcr360_prod'@'localhost' IDENTIFIED WITH mysql_native_password BY '';\nGRANT USAGE ON *.* TO 'pcr360_prod'@'%';\nGRANT USAGE ON *.* TO 'pcr360_prod'@'localhost';\n\nCREATE USER 'pcr360_prod_archive'@'%' IDENTIFIED WITH mysql_native_password BY '';\nCREATE USER 'pcr360_prod_archive'@'localhost' IDENTIFIED WITH mysql_native_password BY '';\nGRANT USAGE ON *.* TO 'pcr360_prod_archive'@'%';\nGRANT USAGE ON *.* TO 'pcr360_prod_archive'@'localhost';\n\nCREATE USER 'pcr360_prod_metadata'@'%' IDENTIFIED WITH mysql_native_password BY '';\nCREATE USER 'pcr360_prod_metadata'@'localhost' IDENTIFIED WITH mysql_native_password BY '';\nGRANT USAGE ON *.* TO 'pcr360_prod_metadata'@'%';\nGRANT USAGE ON *.* TO 'pcr360_prod_metadata'@'localhost';\nCREATE USER 'dmpusr'@'localhost' IDENTIFIED WITH mysql_native_password BY '';\nGRANT USAGE ON *.* TO 'dmpusr'@'localhost';\nGRANT PROCESS ON *.* TO 'dmpusr'@'localhost';\n\n -- Grant database privileges\nGRANT ALL PRIVILEGES ON pcr360_prod.* TO 'pcr360_prod'@'%';\nGRANT ALL PRIVILEGES ON pcr360_prod.* TO 'pcr360_prod'@'localhost';\nGRANT ALL PRIVILEGES ON pcr360_prod_archive.* TO 'pcr360_prod'@'%';\nGRANT ALL PRIVILEGES ON pcr360_prod_archive.* TO 'pcr360_prod'@'localhost';\nGRANT ALL PRIVILEGES ON pcr360_prod_metadata.* TO 'pcr360_prod_metadata'@'%';\nGRANT ALL PRIVILEGES ON pcr360_prod_metadata.* TO 'pcr360_prod_metadata'@'localhost';\nGRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_prod.* TO 'dmpusr'@'localhost';\nGRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_prod.* TO 'dmpusr'@'localhost';","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Run the Create Script MySQL 5.7","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"-- Create Test databases\nCREATE DATABASE pcr360_test DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\nCREATE DATABASE pcr360_test_archive DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\nCREATE DATABASE pcr360_test_metadata DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\n-- Create the users\nCREATE USER 'pcr360_test'@'%' IDENTIFIED BY '';\nCREATE USER 'pcr360_test'@'localhost' IDENTIFIED BY ''; \nGRANT USAGE ON *.* TO 'pcr360_test'@'%';\nGRANT USAGE ON *.* TO 'pcr360_test'@'localhost';\n\nCREATE USER 'pcr360_test_archive'@'%' IDENTIFIED BY '';\nCREATE USER 'pcr360_test_archive'@'localhost' IDENTIFIED BY '';\nGRANT USAGE ON *.* TO 'pcr360_test_archive'@'%';\nGRANT USAGE ON *.* TO 'pcr360_test_archive'@'localhost';\n\nCREATE USER 'pcr360_test_metadata'@'%' IDENTIFIED BY '';\nCREATE USER 'pcr360_test_metadata'@'localhost' IDENTIFIED BY ''; \nGRANT USAGE ON *.* TO 'pcr360_test_metadata'@'%';\nGRANT USAGE ON *.* TO 'pcr360_test_metadata'@'localhost';\n\nCREATE USER 'dmpusr'@'localhost' IDENTIFIED BY '';\nGRANT USAGE ON *.* TO 'dmpusr'@'localhost';\n-- Grant database privileges\nGRANT ALL PRIVILEGES ON pcr360_test.* TO 'pcr360_test'@'%' IDENTIFIED BY '';\nGRANT ALL PRIVILEGES ON pcr360_test.* TO 'pcr360_test'@'localhost' IDENTIFIED BY '';\nGRANT ALL PRIVILEGES ON pcr360_test_metadata.* TO 'pcr360_test_metadata'@'%' IDENTIFIED BY '';\nGRANT ALL PRIVILEGES ON pcr360_test_metadata.* TO 'pcr360_test_metadata'@'localhost' IDENTIFIED BY '';\nGRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_test.* TO 'dmpusr'@'localhost' IDENTIFIED BY ''; \nGRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_test_archive.* TO 'dmpusr'@'localhost' IDENTIFIED BY '';\nGRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_test_metadata.* TO 'dmpusr'@'localhost' IDENTIFIED BY '';\n\n-- Create Production databases\nCREATE DATABASE pcr360_prod DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\nCREATE DATABASE pcr360_prod_metadata DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; \nCREATE DATABASE pcr360_prod_archive DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\n\n-- Create the users\nCREATE USER 'pcr360_prod'@'%' IDENTIFIED BY '';\nCREATE USER 'pcr360_prod'@'localhost' IDENTIFIED BY '';\nGRANT USAGE ON *.* TO 'pcr360_prod'@'%';\nGRANT USAGE ON *.* TO 'pcr360_prod'@'localhost';\n\nCREATE USER 'pcr360_prod_archive'@'%' IDENTIFIED BY '';\nCREATE USER 'pcr360_prod_archive'@'localhost' IDENTIFIED BY '';\nGRANT USAGE ON *.* TO 'pcr360_prod_archive'@'%';\nGRANT USAGE ON *.* TO 'pcr360_prod_archive'@'localhost';\n\nCREATE USER 'pcr360_prod_metadata'@'%' IDENTIFIED BY '';\nCREATE USER 'pcr360_prod_metadata'@'localhost' IDENTIFIED BY ''; \nGRANT USAGE ON *.* TO 'pcr360_prod_metadata'@'%';\nGRANT USAGE ON *.* TO 'pcr360_prod_metadata'@'localhost';\n\nCREATE USER 'dmpusr'@'localhost' IDENTIFIED BY '';\nGRANT USAGE ON *.* TO 'dmpusr'@'localhost';\n-- Grant database privileges\nGRANT ALL PRIVILEGES ON pcr360_prod.* TO 'pcr360_prod'@'%' IDENTIFIED BY '';\nGRANT ALL PRIVILEGES ON pcr360_prod.* TO 'pcr360_prod'@'localhost' IDENTIFIED BY '';\nGRANT ALL PRIVILEGES ON pcr360_prod_metadata.* TO 'pcr360_prod_metadata'@'%' IDENTIFIED BY '';\nGRANT ALL PRIVILEGES ON pcr360_prod_metadata.* TO 'pcr360_prod_metadata'@'localhost' IDENTIFIED BY '';\nGRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_prod.* TO 'dmpusr'@'localhost' IDENTIFIED BY '';\nGRANT SELECT, SHOW VIEW, LOCK TABLES ON pcr360_prod.* TO 'dmpusr'@'localhost' IDENTIFIED BY '';\n ","type":"text"}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"-- needs to be run on prod and test\n-- use pcr360_test;\nuse pcr360_prod;\n \nDROP FUNCTION IF EXISTS FN_FISCAL_YEAR;\nDELIMITER $$\nCREATE FUNCTION FN_FISCAL_YEAR (\n check_date DATETIME,\n rollover_month INT,\n rollover_day INT,\n roll_forward TINYINT(1)\n) RETURNS INT DETERMINISTIC\n BEGIN\n DECLARE cur_year INT DEFAULT EXTRACT(YEAR FROM CURRENT_DATE);\n # Adjust the current year: if FISCAL year rolls forward as the next ACTUAL year, this adds 1 to current.\n IF (roll_forward = 1) THEN\n SET cur_year := cur_year + 1;\n END IF;\n #\n RETURN cur_year - CAST(\n (DATEDIFF(\n check_date,\n CONCAT_WS('-', EXTRACT(YEAR FROM CURRENT_DATE), rollover_month, rollover_day)\n ) < 0)\n AS UNSIGNED\n );\n END$$\n# End of Function\nDELIMITER ;\n \nDROP PROCEDURE IF EXISTS AUDIT_CLEANUP;\nDELIMITER $$\nCREATE PROCEDURE AUDIT_CLEANUP(IN tableName varchar(40))\nBEGIN\n DECLARE done INT DEFAULT 0;\n DECLARE i INT;\n DECLARE tableRecId INT;\n DECLARE auditRecId INT;\n DECLARE fromData LONGTEXT;\n DECLARE toData LONGTEXT;\n DECLARE currRecId INT DEFAULT 0;\n DECLARE currKey VARCHAR(32);\n DECLARE deleteFlag INT DEFAULT 0;\n DECLARE keysMatch INT;\n \n -- Query the data from the view\n DECLARE curs CURSOR FOR\n SELECT RECID, AUDIT_RECID, FROM_DATA, TO_DATA FROM V_AUDIT_CLEANUP;\n \n DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;\n \n -- 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\n SET @v = concat(\n \"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 \",\n tableName, \" WHERE FROM_DATA IS NOT NULL ORDER BY JSON_EXTRACT(FROM_DATA, '$.RECID'), MODIFIED_DATE\");\n PREPARE stm FROM @v;\n EXECUTE stm;\n DEALLOCATE PREPARE stm;\n \n OPEN curs;\n \n SET done = 0;\n \n getRecsLoop:\n loop\n \n FETCH curs INTO tableRecId, auditRecId, fromData, toData;\n \n SET keysMatch = 1;\n \n -- 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\n IF auditRecId != currRecId\n THEN\n SET currRecId = auditRecId;\n SET deleteFlag = 0;\n END IF;\n \n -- So let's first figure out if the number of keys match in FROM_DATA and TO_DATA\n -- If the number of keys match then we need to check each individual value\n -- If the number of keys do not match then we can skip checking the individual values\n IF JSON_LENGTH(JSON_KEYS(fromData)) = JSON_LENGTH(JSON_KEYS(toData))\n THEN\n SET i = 0;\n jsonLoop:\n WHILE i < JSON_LENGTH(JSON_KEYS(fromData))\n DO\n SET currKey = JSON_EXTRACT(JSON_KEYS(fromData), CONCAT(\"$[\", i, \"]\"));\n \n -- 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\n -- https://pcr360.myjetbrains.com/youtrack/issue/PCR360-3188\n IF TRIM(BOTH '\"' FROM CAST(JSON_EXTRACT(fromData, CONCAT(\"$.\", currKey)) AS CHAR)) !=\n TRIM(BOTH '\"' FROM CAST(JSON_EXTRACT(toData, CONCAT(\"$.\", currKey)) as CHAR))\n THEN\n -- values don't match this is a valid record, unset the flag and leave the loop\n SET keysMatch = 0;\n LEAVE jsonLoop;\n END IF;\n SET i = i + 1;\n END WHILE jsonLoop;\n -- The number of keys don't match so this is a valid record\n ELSE\n SET keysMatch = 0;\n END IF;\n \n -- We have the same number of keys and all of the values match let's check if this is the oldest record\n IF keysMatch\n THEN\n -- We have ordered by modified date so the first record we come across will be the oldest so let's leave it alone\n IF deleteFlag = 0\n THEN\n SET deleteFlag = 1;\n -- We have already found the oldest record so let's delete the rest\n ELSE\n SET @sql = CONCAT(\"DELETE FROM \", tableName, \" WHERE RECID = ?\");\n PREPARE stm from @sql;\n SET @a = tableRecId;\n EXECUTE stm using @a;\n DEALLOCATE PREPARE stm;\n END IF;\n END IF;\n \n COMMIT;\n IF done\n THEN\n LEAVE getRecsLoop;\n END IF;\n \n END LOOP;\n \n CLOSE curs;\nEND$$\nDELIMITER ;\n \nDROP FUNCTION IF EXISTS fnStripTags;\nDELIMITER $$\ncreate function fnStripTags(Dirty varchar(4000)) returns varchar(4000) deterministic\nBEGIN\n DECLARE iStart, iEnd, iLength INT;\n WHILE Locate('<', Dirty) > 0 AND Locate('>', Dirty, Locate('<', Dirty)) > 0\n DO\n BEGIN\n SET iStart = Locate('<', Dirty), iEnd = Locate('>', Dirty, Locate('<', Dirty));\n SET iLength = (iEnd - iStart) + 1;\n IF iLength > 0\n THEN\n BEGIN\n SET Dirty = Insert(Dirty, iStart, iLength, '');\n END;\n END IF;\n END;\n END WHILE;\n RETURN Dirty;\nEND$$\nDELIMITER ;","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Set MySQL to Start on Boot","type":"text"}]},{"type":"paragraph","content":[{"text":"1. Add it to chkconfig","type":"text"}]},{"type":"codeBlock","attrs":{"language":"shell"},"content":[{"text":"sudo /sbin/chkconfig --add mysqld","type":"text"}]},{"type":"paragraph","content":[{"text":"2. Make sure it is in the chkconfig.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"shell"},"content":[{"text":"sudo /sbin/chkconfig --list mysqld","type":"text"}]},{"type":"paragraph","content":[{"text":"3. Set it to autostart","type":"text"}]},{"type":"codeBlock","attrs":{"language":"shell"},"content":[{"text":"sudo /sbin/chkconfig mysqld on","type":"text"}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"children","parameters":{"macroParams":{"style":{"value":"h6"},"sort":{"value":"title"}},"macroMetadata":{"macroId":{"value":"49fc0618-4e3d-40c8-92c0-5e0b87f43c92"},"schemaVersion":{"value":"2"},"title":"Child pages (Children Display)"}},"localId":"52ebce45-939a-4929-861c-a6228dde9e03"}}],"version":1}