cannot CREATE UNIQUE INDEX; duplicate key found

[Former Member]

Dear expert,

I have meet a problem during System Copy-import phase.the info and error is bellow

source system

OS:LINUX 11 SP3

DB:ORACLE 11.2.0.4

APP:ERP 6.0 EHP 7

target:

OS:LINUX 11 SP3

DB:HDB 1.0 SPS 11

APP:ERP 6.0 EHP 7

error is

ERROR exec_ddl_stmt: (DB) ERROR: DDL statement failed

(ALTER TABLE "BSIS" ADD CONSTRAINT "BSIS~0" PRIMARY KEY ( "MANDT", "BUKRS", "HKONT", "AUGDT", "AUGBL", "ZUONR", "GJAHR", "BELNR", "BUZEI" ) )

DbSlExecute: rc = 99

  (SQL error 349)

  error message returned by DbSl:

cannot CREATE UNIQUE INDEX; duplicate key found:  [5] Several documents with the same ID exist in the index;SAPABAP1:BSIS.$trexexternalkey$ content not unique, cannot define unique constraint. rowCount != distinctCount

(DB) INFO: disconnected from DB

and  also log is attached.

and I have check on source system,there is no duplicate key using the following SQL

select alldata.rowid, alldata.*

  from SAPRPE.BSIS alldata,

      (SELECT MANDT,BUKRS,HKONT,AUGDT,AUGBL,ZUONR,GJAHR,BELNR,BUZEI FROM SAPRPE.BSIS GROUP BY MANDT,BUKRS,HKONT,AUGDT,AUGBL,ZUONR,GJAHR,BELNR,BUZEI

                      HAVING COUNT(*) > 1) keydata

  where alldata.MANDT=keydata.MANDT

    and alldata.BUKRS=keydata.BUKRS

  and alldata.HKONT=keydata.HKONT

  and alldata.AUGDT=keydata.AUGDT

  and alldata.AUGBL=keydata.AUGBL

  and alldata.ZUONR=keydata.ZUONR

  and alldata.GJAHR=keydata.GJAHR

  and alldata.BELNR=keydata.BELNR

  and alldata.BUZEI=keydata.BUZEI;

and cann't find the the table BSIS ON the target system.

can any one help me out?

BSIS__DT.txt.zip

[Pritesh]

Hi Huang,

We are facing same nature of error during upgrade. Could you please paste the solution proposed by SAP ?

Regards,

Pritesh

[manish_singh13]

Hi Huang,

Can you please check same table (BSIS) in your target HANA DB (whether it exists or not). If exists then please compare number of rows also with your source database.

Also if you restarted import during import phase? It might be possible that this table with primary key index were already imported earlier with data and now during retry it's giving error due to previous existence.

Thanks,

Manish

[Former Member]

the query in target hana db data back and duplicate keys occurs

and in source system  no duplicate keys

the frist time when I imported the data this error occured.when I retry ,the same error.

could I remove(delete) the duplicate key.

as before, I do system copy  on oracle 11.2 linux x86_64 and met  ORA-1452 error.

and I delete the duplicate key as per SAPNOTE 11369 and SAPNOTE 23237.

but I am note sure on HANA DB

[manish_singh13]

Hi Huang,

Please check below SCN discussion about similar error on HANA.

SAP HANA System Copy - CREATE UNIQUE INDEX: ERR... | SCN

You can delete/drop table from HANA DB similar to Oracle.

DROP TABLE - SAP HANA SQL and System Views Reference - SAP Library

Thanks,

Manish

[Former Member]

Dear Manish,

The first thread I have check. they finally use backup/restore method  to solve the problem.

but it doesn't suite me.

and the second link you post I  have also check

I am not sure It does work.by the way I want to consult if I could delete the one of duplicate key rows.

not drop tables.

could you find related note  about this ?

I have found note 2262573 - Error in Phase EU_CLONE_MIG_DT_RUN cannot CREATE UNIQUE INDEX; duplicate key found and note 2159430 - cannot CREATE UNIQUE INDEX in SEA_DIMENSIONS table

but not give process procedure!

[manish_singh13]

Hi Huang,

Since the primary key which is being created on this table includes multiple column, I am trying to build query first to identify the duplicate records. Could you please try executing below query on HANA DB to check if it produces duplicate record rows:

select ROW_ID from

(SELECT ROW_NUMBER() OVER (PARTITION BY MANDT,BUKRS,HKONT,AUGDT,AUGBL,ZUONR,GJAHR,BELNR,BUZEI) as RN,

"$rowid$" as ROW_ID,

MANDT,BUKRS,HKONT,AUGDT,AUGBL,ZUONR,GJAHR,BELNR,BUZEI FROM BSIS)

where RN>1;

Thanks,

Manish

[Former Member]

Dear Manish,

the table is created on HANA DB,now create unique index on union primary key.but duplicate primary key existed.then error appears

I query using the SQL script you provided.the data back as below

select ROW_ID from

(SELECT ROW_NUMBER() OVER (PARTITION BY MANDT,BUKRS,HKONT,AUGDT,AUGBL,ZUONR,GJAHR,BELNR,BUZEI) as RN,

"$rowid$" as ROW_ID,

MANDT,BUKRS,HKONT,AUGDT,AUGBL,ZUONR,GJAHR,BELNR,BUZEI FROM BSIS)

where RN>1;

could I delete these record?

If do delete opration.will it result in data corruption?

[manish_singh13]

Hi Huang,

As per this query output, it shows multiple duplicate records in table BSIS.

I will not suggest deletion of these records as this might create database inconsistency.

At this point, I suggest you to please raise message with SAP so that they can check and provide solution. This issue you are facing is not a common issue and should better be consulted with SAP HANA team before taking any action.

Thanks,

Manish

[Former Member]

Dear Manish,

Thank you for sugesstion,I have already send a incident to SAP and I am waitting for SAP response.

As soon as I solved the issue. I will post the solution