maintenance_window probe down after upgrading UIM to 23.4 and MySQL to 8.0

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

The maintenance_window probe could not create 2 tables in the MySQL database due to the following error : 

error executing query:
#HY000Referencing column 'DEV_ID' and referenced column 'DEV_ID' in foreign key constraint 'maintenance_window_ibfk_2' are incompatible.
SQL:
CREATE TABLE IF NOT EXISTS `MAINTENANCE_WINDOW` (
    WINDOW_ID INT(10) NOT NULL AUTO_INCREMENT,
    SCHEDULE_ID INT(10),
    DEV_ID VARCHAR(33) DEFAULT NULL,
    START_TIME DATETIME NOT NULL,
    END_TIME DATETIME NOT NULL,
    PRIMARY KEY (WINDOW_ID),
    INDEX (SCHEDULE_ID), 
    INDEX (DEV_ID),       
    FOREIGN KEY (SCHEDULE_ID) REFERENCES MAINTENANCE_SCHEDULE(SCHEDULE_ID) ON DELETE SET NULL,
    FOREIGN KEY (DEV_ID) REFERENCES CM_DEVICE(DEV_ID) ON DELETE CASCADE
)

 

 

Environment

DX UIM 23.4.x

MySQL 8.0 

maintenance_window probe any version

Cause

 

the data type for dev_id was correct (varchar(33)) but it turned out that the default collection for the table column was different than the one for the dev_id column in the already existing cm_device table: 

 

 

Resolution

To resolve the issue create the tables manually, and provide the same charset and collection as used by the already existing tables:

 

 

Mainetnance_window table :

CREATE TABLE IF NOT EXISTS `MAINTENANCE_WINDOW` (
    WINDOW_ID INT(10) NOT NULL AUTO_INCREMENT,
    SCHEDULE_ID INT(10),
    DEV_ID VARCHAR(33) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
    START_TIME DATETIME NOT NULL,
    END_TIME DATETIME NOT NULL,
    PRIMARY KEY (WINDOW_ID),
    INDEX (SCHEDULE_ID),
    INDEX (DEV_ID),
    FOREIGN KEY (SCHEDULE_ID) REFERENCES MAINTENANCE_SCHEDULE(SCHEDULE_ID) ON DELETE SET NULL,
    FOREIGN KEY (DEV_ID) REFERENCES CM_DEVICE(DEV_ID) ON DELETE CASCADE

 

 

and maintenance_window_history table : 

 

CREATE TABLE if not exists `MAINTENANCE_WINDOW_HISTORY` (
WINDOW_ID int(10) NOT NULL AUTO_INCREMENT, PRIMARY KEY (WINDOW_ID),
SCHEDULE_ID int(10),
DEV_ID varchar(33)  CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL, 
START_TIME DATETIME NOT NULL,
END_TIME DATETIME NOT NULL,
    foreign key (SCHEDULE_ID) references MAINTENANCE_SCHEDULE(SCHEDULE_ID) ON DELETE SET NULL,
    foreign key (DEV_ID) references CM_DEVICE(DEV_ID) ON DELETE CASCADE);