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


Article ID: 213093


Updated On:


CA Spectrum DX NetOps


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                                                                                                    |


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


Release : 20.2

Component : Spectrum Core / SpectroSERVER


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