SAP HANA – unique constraint violation

In last time in our HANA traces I encountered problem like this:

[115248]{233308}[35/1791487910] 2021-XX-04 12:16:44.190701 e EmbeddedAPI      ExternalStatement.cc(01360) : [external statement] unhandled exception (external statement execution): ERROR [SQL-301] unique constraint violated: Table(sap.hana.xs.dt.base.server.persistency.userpreferences::UserPreferencesIntern) with error: unique constraint violation on pos=0 for table SID::SAP_REST_API:sap.hana.xs.dt.base.server.persistency.userpreferences::UserPreferencesIntern$delta_1$en, key: $trexexternalkey$=8,USERNAME,sap.watt.common.content.service.ContentServicePersistence already exists as udiv=108 at SQLUpdate/Storage/Impl/ColumnStorageChange.cc:208exception  1: no.71000301  (SQLUpdate/Storage/Impl/ColumnStorageChange.cc:208) TID: 115248    Table(sap.hana.xs.dt.base.server.persistency.userpreferences::UserPreferencesIntern) with error: unique constraint violation on pos=0 for table SID::SAP_REST_API:sap.hana.xs.dt.base.server.persistency.userpreferences::UserPreferencesIntern$delta_1$en, key...

The above entries appeared in the logs every few seconds. HANA practically created another trace file every few minutes. Probably the reason were disadvantages of primary keys being defined on statistics server tables:

They allocate space and increase the size of the statistics server tables and the memory allocation of the statistics server.
Errors are thrown when duplicate keys are inserted in the history tables and collection of history data is no longer possible.

Solution

Please see 2147247 – FAQ: SAP HANA Statistics Server, section 16. What can I do if the history collection fails with “301: unique constraint violated”?

As a workaround the primary keys of the involved histories can be dropped as described in SAP Note 2143679.

You will need to follow the steps as provided in the SAP Note. Remember: please take the backup before implementing the SAP Note.

In my system: SQL: “HANA_StatisticsServer_PrimaryKeysOnHistoryTables” returns 48 records, like below:

ALTER TABLE "_SYS_STATISTICS"."GLOBAL_DEC_EXTRACTOR_STATUS_BASE" DROP CONSTRAINT "_SYS_TREE_CS_#6807182_#0_#P0";
ALTER TABLE "_SYS_STATISTICS"."GLOBAL_DISKS_BASE" DROP CONSTRAINT "_SYS_TREE_CS_#6807214_#0_#P0";
ALTER TABLE "_SYS_STATISTICS"."GLOBAL_INTERNAL_EVENTS_BASE" DROP CONSTRAINT "_SYS_TREE_CS_#6808604_#0_#P0";
etc.

I Executed the commands generated in the column DROP_COMMAND in order to remove the primary keys of critical tables.

At the end If you are not aware off regarding for certain records, I would advise You to rebuild for all the records as per the process provided in the SAP Note 2143679 .

Solution

Cancel Reply