Initial Load error due duplicate attributes

[Former Member]

Hi Gurus,

I am facing below error during initial load of one our system with further investigation & pointers from SAP , we have found that every privileges uploaded from back-end system contains attributes with duplicate values

Initial Load Error screen shot.

Query result.

Query result detail.

Pherphas , Can some one guide best approach to clear duplicate enteries.

Regards,

Ali.

[Former Member · Accepted solution]

Hi,

I have seen this before, IdM creates duplicates in the mxi_values table.

Please check this in your Development system:

The following query should list all these duplicates:

select mv.mskey, mv.attr_id, mv.searchvalue, count(1) from mxi_values mv

inner join mxi_attributes ma on mv.attr_id = ma.attr_id

where ma.multivalue = 0

group by mv.mskey, mv.attr_id, mv.searchvalue

having count(1) > 1

If you want to remove the duplicate rows, this should work:

delete from mxi_values where rowid not in

(

   select min(mv.rowid) from mxi_values mv

   inner join mxi_attributes ma on mv.attr_id = ma.attr_id

   where ma.multivalue = 0

   group by mv.mskey, mv.attr_id, mv.searchvalue

)

These queries are fir Oracle.

Regards,

Ole K.

[Former Member · Accepted solution]

Hi Ole,

Many thanks for the queries , I have one quick question regarding deleted query ,is this necessary to deleted depending newest of changenumber or Latest Modifytime , just to avoid inconsistency.

Regards,

Ali.

[Former Member · Accepted solution]

Hi Ali,

I believe that depends on which attribute it is. In your case where it is  MX_PROVISIONTASK with value -1 it should not matter.

Regards,

Ole K.

[Former Member · Accepted solution]

Hi OLe,

Below are the duplicate attributes with value -1 , is it going to matter ?

MX_PROVISIONTASK

MX_DEPROVISIONTASK

MX_ADD_MEMBER_TASK

MX_DEL_MEMBER_TASK

MX_VALIDATE_ADD_TASK

MX_VALIDATE_DEL_TASK

Regards,

Ali.

[Former Member · Accepted solution]

Hi,

It should be OK to remove these duplicates.

Regards,

Ole K.

[Former Member]

Hi,

Did this solve your problem?

Regards,

Ole K.

[Former Member]

Hi Ole,

Yes indeed apart from your query , I have also run below query from Note : 2030931

delete from mxi_values where changenumber in

(select max(changenumber) from mxi_values group by mskey,attr_id,searchvalue,bChecksum having count(searchvalue)>1);

I was planning to perform complete initial load again that is why cant able to respond to you.

Many Thanks

Ali.

[terovirta]

Hello,

if I understood correctly from your message you have open dialogue / OSS-message with SAP(?), so then in case like this it's best to trust to the reply you get from them.

Are those task-attributes been re-defined as multi-value in IdM schema or is just fundamental problem in the Sp-level you have?

What Sp-level of design time do you have? Is it fresh install or upgrade?

regards, Tero