AIQ 5.0 Database Upgrade

AIQ 5.0 Database Upgrade

MostUse ofthis thepage primaryas keysa ofcontrolled checklist for upgrading the AIQ schema have been migrated to a long data type. (from INT datatype to BIGINT datatype) This change will prevent you from reaching the limit of an integer value (-2147483648 to 2147483647) in the tables of the AIQ schema.

As part of this update additional changes were madedatabase to the AIQ architecture including stored procedures and functions.

Upgradeintegrations, Considerations

Youjobs, shouldand completereporting adependencies fullthat backupmay beforerely startingon database content.

Validation workflow

1. Run the upgrade process. This is highly recommended in case the upgrade process fails or if you need to revert to a pre-AIQlower 5.0.0environment releasefirst forwhen any version. Oncepossible.
2. Apply the database hasupgrade beensteps upgradedexactly itonce cannotand berecord usedany forwarnings.
3. Start a pre-5.0.0 release of AIQ.

   Checking the Max Values in Columns

   Running the attached script will find the max value in a column with data type INT. This tells you if the value is close to reaching the limit.

   Download script: checkMaxValues.sql

   AIQ 5.0.0services Databaseand Configuration

   verify

   Youlogin, havescenario twoexecution, optionsreports, toand implementintegrations.

4. Monitor thelogs and database changes.health You can create a new database or upgrade an existing database. Below are the requirements and process for each option.

   There is a project tracking spreadsheet attached for each option. Use the spreadsheet to track the steps in the process. See the “Project Tracking Information” sub section for each scenario.

   Project Tracking Information

   Define the following data in the project tracking spreadsheet (attached below) before starting the process in your environment:

5. Start time - Start time ofafter the first step.

   post-upgrade executions.

EndMaintenance timenote: -Do Endnot timeretry ofschema eachchanges step.

Responsiblefailed -upgrade. PersonPreserve responsible for each step.

Appvance Support Contact - Appvance support person namelogs and email that will help with the process.

Scenario 1: Create a New Database

Requirements

MySQL client (E.g. MySQLWorkbench)

Project Tracking Spreadsheet

AIQ-5.0.0 - New Datbase - Minute By Minute.xlsx

Upgrade Process

Stop service of the current database (If exists).

Stop AIQ Test Nodes.

Stop AIQ Controller.

Prepare the new database. See Installing and Configuring MySQL 8 Database (Windows) for instructions.

In your MySQL client open a new session as a root user.

Open and execute the following script: MySQLInitial.sql

Verify env var running show variables like 'log_bin_trust_function_creators'; . It should be ON.

Verify that the user aiq was created with two locations: localhost and %.

Verify that the schema aiq was created with two locations: localhost and %.

Close session with the root user.

In your MySQL client open a new session as user aiq with password aiq.

Open and execute the following script: MySQLAppvance.sql

Close session with the aiq user.

Start AIQ controller.

Check database connection. See Results.

Troubleshooting

Error Code: 1419. You do not have the SUPER privilege and binary logging is enabled

If you get the above error while running the MySQLAppvance.sql script on AWS RDS, make sure that the RDS parameter group has the value 1 (ON) for the log_bin_trust_function_creators parameter. The RDS database MUST be rebooted for parameter changes to take effect.

Scenario 2: Existing Database

This process is intended to be executed by Devops/DBA personnel with enough privileges to manage AWS/RDS instances or with access to server hosting the mysql database.

Before upgrade, make sure that the database is backed up.

• AWS - Snapshot the RDS instance.
• Azure - Snapshot volume of the database instance.

Requirements:

MySQL client (E.g. MySQLWorkbench).

Preparation

You must gather the following data is required before executing the upgrade process:

Current Database Space Provisioned/Available. Gather this informationrestore from the appropriateapproved source(s):

Server Hardware

Current Database Size: Column “Total_Db_Allocated_Space_Mb“ from below script

Expected Grow: Column “Estimated_Data_Storage_Grow_Mb“ from below script

Minimum space required for the upgrade process: Column “Minimum_Free_Space_Required_For_Upgrade_Mb“ from below script

Script to gather information

select t.TABLE_SCHEMA DB_Schema,
       sum(t.table_rows) Total_Rows,
       max(t.table_rows) Maximum_Rows_In_Table,
       max(t.AUTO_INCREMENT) Maximum_Identity_Value,
       sum(t.data_length)/1024/1024 +
       sum(t.index_length)/1024/1024 Total_Db_Allocated_Space_Mb,
        sum(CASE t.TABLE_NAME
            WHEN 'SummaryAttribute' THEN t.table_rows * 8
            WHEN 'SummaryStatsOnTime' THEN t.table_rows * 4
            WHEN 'DefaultPercentil' THEN t.table_rows * 4
            WHEN 'TypeSuccessFailure' THEN t.table_rows * 4
            WHEN 'Definition' THEN t.table_rows * 4
            WHEN 'TypeLong' THEN t.table_rows * 4
            WHEN 'SummaryElement' THEN t.table_rows * 4
            WHEN 'FailSummaryElement' THEN t.table_rows * 12
            WHEN 'DetailedTypeRequest' THEN t.table_rows * 16
            WHEN 'DetailedElementAttribute' THEN t.table_rows * 12
            WHEN 'DetailedElement' THEN t.table_rows * 4
            WHEN 'TypeDouble' THEN t.table_rows * 4
            WHEN 'ResultOnTime' THEN t.table_rows * 4
            WHEN 'TypeCharacter' THEN t.table_rows * 4
            WHEN 'TypeDefinition' THEN t.table_rows * 8
            WHEN 'TypeNode' THEN t.table_rows * 4
            WHEN 'TypeInt' THEN t.table_rows * 4
            WHEN 'TypeBinary' THEN t.table_rows * 4
            WHEN 'TypeExternal' THEN t.table_rows * 8
            WHEN 'TypeString' THEN t.table_rows * 4
            WHEN 'SummaryChildElement' THEN t.table_rows * 12
            WHEN 'TypeChildOf' THEN t.table_rows * 8
            WHEN 'DetailedDuration' THEN t.table_rows * 4
            WHEN 'DetailedElementChild' THEN t.table_rows * 12
            WHEN 'DetailedTypeBlob' THEN t.table_rows * 4
            WHEN 'DetailedTypeCharacter' THEN t.table_rows * 4
            WHEN 'DetailedTypeChildOf' THEN t.table_rows * 8
            WHEN 'NodePerScenario' THEN t.table_rows * 4
            WHEN 'DetailedTypeDefinition' THEN t.table_rows * 8
            WHEN 'TypeStringArray' THEN t.table_rows * 4
            WHEN 'DetailedTypeDouble' THEN t.table_rows * 4
            WHEN 'DetailedTypeInt' THEN t.table_rows * 4
            WHEN 'DetailedTypeLongBlob' THEN t.table_rows * 4
            WHEN 'DetailedTypeMediumBlob' THEN t.table_rows * 4
            WHEN 'DetailedTypeNode' THEN t.table_rows * 4
            WHEN 'DetailedTypeStep' THEN t.table_rows * 16
            WHEN 'DetailedTypeString' THEN t.table_rows * 4
            WHEN 'DetailedTypeTestcase' THEN t.table_rows * 8
            WHEN 'DetailedTypeTinyBlob' THEN t.table_rows * 4
            WHEN 'CustomDetailedAttribute' THEN t.table_rows * 16
            WHEN 'CustomSummaryAttribute' THEN t.table_rows * 4
            WHEN 'UTPIterationPerExecution' THEN t.table_rows * 12
            WHEN 'UTPScenarioExecution' THEN t.table_rows * 24
        END*2)/1024/1024 AS Estimated_Data_Storage_Grow_Mb,
        max(t.data_length)/1024/1024 + max(t.index_length)/1024/1024 Minimum_Free_Space_Required_For_Upgrade_Mb

from information_schema.tables t
where  t.table_TYPE = 'BASE TABLE'
group by DB_Schema;

Maintenance Window Calculation

From the Previous script use the columns Total_Rows and Maximum_Rows_In_Table to have a brief estimation of time required:

A Database with Maximum_Rows_In_Table close to 2000 million rows:

Between 10 to 12 hours in a xlarge rds instance for the database modifications.

Between 30 minutes to 1 hour for space allocationbackup if needed.

Around 24 hours to reach normal performance after the upgrade. (Systemrollback is fully usable during this period be expect slowness in report generation.)

A database with less than 20 million rows in Maximum_Rows_In_Table:

Between 30 minutes to 1 hour in a xlarge rds instance for the database modifications.

Between 30 minutes to 1 hour for space allocation if needed (not expected to be needed).

Around 24 hours to reach normal performance after the upgrade. (System is fully usable during this period be expect minor slowness in report generation.)

The time relationship is not proportional, it obeys more an exponential curve, expected to take 25% of the time for 1 billion rows.

Server Preparation

Pre-allocate space for rds instance to ensure the Minimum space required for the upgrade process is available before running the script.

Space Provisioned for RDS must be greater than Total_Db_Allocated_Space_Mb + Minimum_Free_Space_Required_For_Upgrade_Mb + Estimated_Data_Storage_Grow_Mb

Upgrade RDS instance temporary to xlarge/2xlarge just for the upgrade process, instance can be downgraded after finishing the process.

Upgrade Process

Stop AIQ Test Nodes.

Stop AIQ Controller.

Create a DB Snapshot/Backup before Starting the process

In your MySQL client open a new session as a root user.

Open and execute the script selecting the schema for aiq database: AIQ Schema Upgrade Process.sql.required.

If any error of failure encountered, review the cause and re-run the script. The script can be run multiple times without risk of damaging data or database.

Open and execute the script selecting the schema for aiq database: MySQLAppvance_ViewsAndProcs.sql

If any error of failure encountered, review the cause and re-run the script. The script can be run multiple times without risk of damaging data or database.

Start AIQ controller.

Check database connection. See Results.