AIQ 5.0 Database Upgrade

AIQ 5.0 Database Upgrade

Most of the primary keys of 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 made to the AIQ architecture including stored procedures and functions.

Upgrade Considerations

You should complete a full backup before starting the upgrade process. This is highly recommended in case the upgrade process fails or if you need to revert to a pre-AIQ 5.0.0 release for any version. Once the database has been upgraded it cannot be used for 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.0 Database Configuration

You have two options to implement the database changes. 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:

• Start time - Start time of the first step.

• End time - End time of each step.

• Responsible - Person responsible for each step.

• Appvance Support Contact - Appvance support person name 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

1. Stop service of the current database (If exists).

2. Stop AIQ Test Nodes.

3. Stop AIQ Controller.

4. Prepare the new database. See href="https://docs.appvance.net/Content/AIQ/AIQ_Install/Windows/Install_WindowsMySQL.htm"Installing and Configuring MySQL 8 Database (Windows) for instructions.

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

6. Open and execute the following script: MySQLInitial.sql

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

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

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

10. Close session with the root user.

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

12. Open and execute the following script: MySQLAppvance.sql

13. Close session with the aiq user.

14. Start AIQ controller.

15. Check database connection. See href="https://docs.appvance.net/Content/AIQ/Global_Options/AdminOptions/Results/GlobalOptions_Admin_Results.htm"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 information from the appropriate source(s):

  • AWS RDS Console
  • Instance provisioned Space

  • 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 allocation if needed.

  • Around 24 hours to reach normal performance after the upgrade. (System 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

1. Stop AIQ Test Nodes.

2. Stop AIQ Controller.

3. Create a DB Snapshot/Backup before Starting the process

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

5. Open and execute the script selecting the schema for aiq database: AIQ Schema Upgrade Process.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.

6. 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.

7. Start AIQ controller.

8. Check database connection. See href="https://docs.appvance.net/Content/AIQ/Global_Options/AdminOptions/Results/GlobalOptions_Admin_Results.htm"Results.