ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Spectrum SRM "optimize table event" failing with Duplicate Entry error

book

Article ID: 213093

calendar_today

Updated On:

Products

CA Spectrum DX NetOps

Issue/Introduction

when running "optimize table event" on SRM reporting dbase I see the following error

+-----------------+----------+----------+---------------------------------------------------------------------------------------------------------------------+
| Table           | Op       | Msg_type | Msg_text                                                                                                            |
+-----------------+----------+----------+---------------------------------------------------------------------------------------------------------------------+
| reporting.event | optimize | note     | Table does not support optimize, doing recreate + analyze instead                                                   |
| reporting.event | optimize | error    | Duplicate entry '`}\x8D(,\x98\x11\xB7\x03:\x00\x80\x10\x90\x9BJ-108320-2021-04-19' for key 'event_id_model_key_idx' |
| reporting.event | optimize | status   | Operation failed                                                                                                    |
+-----------------+----------+----------+---------------------------------------------------------------------------------------------------------------------+

Cause

This is a known MySQL Bug that is not common for most customers (DE482633)

Environment

Release : 20.2

Component : Spectrum Core / SpectroSERVER

Resolution

With MyISAM optimize was used to reclaim unused space but InnoDB has partitioned the event table so there is no need to run it against the event table.  

Once the partitioned table is older than the retention period, we truncate it.

In Spectrum 10.3, the event table of DDM database has been enhanced (InnoDB engine).

Each partition of the event table contains one day of data

The number of partitions will be equal to Archive Manager event retention period + 1 (future date)

In case of fresh install you will see two partitions – one partition for installation date and another for future date

Each day depending upon retention period Archive Manager

o   Creates a new partition

o   Deletes the oldest partition during the events purge