After upgrading mysql to 8.x DX UIM DB is growing

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

After the upgrade of MySQL 8 from 5.7, the data_engine is no longer able to maintain the data, partition and purge old data, causing increased amount of data stored on the DB. 

Environment

• DX UIM 20.4CU8 (20.4.8) and Later
• MySQL 5.7 > 8.0

Cause

The cause of this issue can be a data collition issue in the DB. The collision issue is caused by a DB Configuration issue as one of the pre-requirements might not have been followed. 

As per: Upgrade MySQL to v8.x (From DX UIM 20.4 CU8) (broadcom.com)

character_set_server = utf8mb4 should be utfmb3

 

 

***Faulty Upgrade example***

my.cnf character is not cofigured as per pre-requirements before the upgrade:

character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
default_week_format = 1
lower_case_table_names = 1
explicit_defaults_for_timestamp = 1

Notes: 

•  Upgrading mysql to 8 from 5.7 and leaving "character-set-server = "utf8mb4" causes a collition issue. The DX UIM DB mysql schema is built with "utf8mb3". After the upgrade, it overriden this to utf8mb4 and this causes data directories and tablespaces to have mixed data. This is not compatible with data_management and purging operations. (Affected stored procedure existsspn_de_BasicDataMaintenance__Shrink)

•  If before the upgrade of mySQL to 8 character-set-server may be set to utf8mb4 while running mysql 5.7, however, This does not represent an issue in 5.7. Instead, it is an issue starting mySQL 8 and this is why we document this is in the prerequirements. 

 

Resolution

In case of runinng the upgrade of mysql from 5.7 to 8 without having met the pre-requirements the following store procedure must be updated to recover from the faulty upgrade. Run the following statements against your DX UIM MySQL DB: 


DELIMITER $$
drop procedure if existsspn_de_BasicDataMaintenance__Shrink
$$

create procedure spn_de_BasicDataMaintenance__Shrink (
   in  pQosDefId        int, -- if null, all sample tables
   in  pTableType       varchar(255), -- RN or HN or DN
   in  pCurrentTime     datetime,
   in  pLogLevel        int,
   out pRowsAffected    int,
   out pReturnCode       int
) begin
   declare lStartTime    datetime;
   declare lMsg          text;
   declare lMsg2         text;
   declare lRC           int default 0;

   declare lTableName    varchar(255);
   declare daysToKeep    int;
   declare lCmd          text;
   declare lErrMsg       text;
   declare lRowsChanged  int default 0;
   declare lRowsScanned  int default 0;
   declare lShrinkTimeLimit datetime;
   declare lDeleteLimit  int default 10000;
   declare lTimeColumn   text;

   if (pLogLevel >= 4) then
      set lStartTime = CURRENT_TIMESTAMP();
      set lMsg = concat('spn_de_BasicDataMaintenance__Shrink(', ifnull(pQosDefId,'null'), ',', ifnull(pTableType,'null'), ',', ifnull(pCurrentTime,'null'), ')' );
      if (pLogLevel >= 5) then
         call spn__LogEvent('>', lMsg, lStartTime,NULL);
      end if;
   end if;

   if (pQosDefId is null) then
      set lRC = 33331;
      set lErrMsg = concat('Invalid value for argument pQosDefId: ', 'null');
   end if;
   if (pTableType not in ('RN', 'HN', 'DN', 'BN') ) then
      set lRC = 33332;
      set lErrMsg = concat('Invalid valid for argument pTableType: ', ifnull(pTableType,'null'));
   end if;
   if (pCurrentTime is null) then
      set lRC = 33333;
      set lErrMsg = concat('Invalid value for argument pCurrentTime: ', 'null');
   end if;

   if (lRC = 0) then

    select
         case
            when pTableType = 'RN' then RawAge
            when pTableType = 'HN' then HistoryAge
            when pTableType = 'BN' then HistoryAge
                                   else DailyAvgAge
         end
      into
        daysToKeep
      from
         tbn_de_DataMaintConfig;

    select
         case
            when pTableType = 'RN' then ifnull(raw_age, daysToKeep)
            when pTableType = 'HN' then ifnull(history_age, daysToKeep)
            when pTableType = 'BN' then ifnull(history_age, daysToKeep)
                                   else ifnull(dailyavg_age, daysToKeep)
         end
      into
        daysToKeep
      from
        s_qos_definition
      where
        qos_def_id = pQosDefId;

      set daysToKeep = daysToKeep;

      if (pLogLevel >= 5) then
        set lMsg2 = concat(lMsg,': daysToKeep=',ifnull(daysToKeep,'null'));
        call spn__LogEvent('-', lMsg2, lStartTime, lRC);
      end if;

      set lShrinkTimeLimit =     DATE_ADD(pCurrentTime, interval -daysToKeep day);

      set lTableName = concat(pTableType,'QOS_DATA',right(concat('0000', pQosDefId),4));

      if exists(select * from information_schema.tables where table_schema = schema() and table_name = lTableName) then
         if (pTableType = 'BN') then
            set lTimeColumn = 'starttime';
        else
            set lTimeColumn = 'sampletime';
        end if;

         set lCmd = concat('delete from ', lTableName,  ' where ', lTimeColumn,' < ''', lShrinkTimeLimit, ''' limit ', lDeleteLimit, '');
         set pRowsAffected = 0;
         repeat
            call spn_utl__ExecuteSql(lCmd, pLogLevel, lRowsScanned, lRowsChanged, lRC);
            if (lRC = 0) then
               set pRowsAffected = pRowsAffected + lRowsChanged;
            else
               set lErrMsg = concat('Failed executing: ', lCmd );
            end if;
         until lRowsChanged < lDeleteLimit
         end repeat;
      else
         set lRC = 33334;
         set lErrMsg = concat('Could not find table to be shrunk: ', lTableName);
      end if;
   end if;

   if (pLogLevel >= 4) then
      set lMsg = concat(lMsg,': Rows shrunk=',pRowsAffected);
      call spn__LogEvent('<', lMsg, lStartTime, lRC);
   end if;
   set pReturnCode = lRC;
end;
$$