Incorrect datetime value during upgrade to 23.4 using MySQL 8.x

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

Error from the /tmp/ca_uim/uimserver_ia_install.log:

2025-09-12 10:00:47,448 DEBUG util.NimDBScriptRunner:executeSQLCommand:103 [main]  - Executing SQL command: -- -----------------------------------------------------
-- Table `cm_computer_system_update`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `cm_computer_system_update` (
  `change_ts` DATETIME NULL DEFAULT NULL
) ENGINE = InnoDB
2025-09-12 10:00:47,450 DEBUG util.NimDBScriptRunner:executeSQLCommand:106 [main]  - Execution complete. result_flag: false
2025-09-12 10:00:47,450 DEBUG util.NimDBScriptRunner:executeSQLCommand:103 [main]  - Executing SQL command: INSERT INTO `cm_computer_system_update` (change_ts)
select now() from `cm_computer_system_update` having count(*) = 0
2025-09-12 10:00:47,453 ERROR impl.UIMServerDBSchemaController:run:180 [main]  - NimException caught
(503) , java.sql.SQLSyntaxErrorException: (conn=112500) Incorrect datetime value: '0000-00-00 00:00:00' for column 'change_ts' at row 1: (conn=112500) Incorrect datetime value: '0000-00-00 00:00:00' for column 'change_ts' at row 1

Environment

• 23.4
• MySQL 8.x database

Resolution

This issue was related to the versions around MySQL 8.0.26.

The problem does not exist with MySQL 8.0.39 and above.

MySQL version 8.4.4 is also supported. The database should be set to use:

character-set-server = utf8mb3
collation-server = utf8mb3_unicode_ci

per: 

Install or Upgrade MySQL to v8.x

UIM is not supported with utf8mb4.