Spectrum Report Manager (SRM) "optimize table event" failing with Duplicate Entry error
Article ID: 213093
Updated On:
Products
Issue/Introduction
When running "optimize table event" on the Spectrum Report Manager (SRM) reporting database 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 |
+-----------------+----------+----------+---------------------------------------------------------------------------------------------------------------------+
Environment
Release : Any
Component : Spectrum Applications
Cause
This is a known MySQL Bug that is not common for most customers (DE482633)
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
Feedback
