SAP HANA System Copy - CREATE UNIQUE INDEX: ERROR - duplicate key found

[Former Member]

Hi

I created a SWPM Database Instance Export of a "EHP7 FOR SAP ERP 6.0" system running on HANA.

On the Target System, on the Database Instance import, I'm getting these errors:

import_monitor.java.log:

Import Monitor jobs: running 7, waiting 7, completed 52, failed 0, total 66.

Loading of 'SAPSSEXC_3' import package: ERROR

Import Monitor jobs: running 2, waiting 7, completed 56, failed 1, total 66.

Loading of 'SAPSSEXC_5' import package: ERROR

  import_monitor.log:

ERROR: 2016-01-20 15:04:09 com.sap.inst.migmon.LoadTask run

Loading of 'SAPSSEXC_3' import package is interrupted with R3load error.

Process '/usr/sap/TDB/SYS/exe/uc/linuxx86_64/R3load -i SAPSSEXC_3.cmd -dbcodepage 4103 -l SAPSSEXC_3.log -c 100000 -loadprocedure dbsl -rowstorelist /tmp/sapinst_instdir/NW740SR2/HDB/COPY/SYSTEM/DISTRIBUTED/AS-ABAP/DB/rowstorelist.txt' exited with return code 2.

For mode details see 'SAPSSEXC_3.log' file.

ERROR: 2016-01-20 15:31:50 com.sap.inst.migmon.LoadTask run

Loading of 'SAPSSEXC_5' import package is interrupted with R3load error.

Process '/usr/sap/TDB/SYS/exe/uc/linuxx86_64/R3load -i SAPSSEXC_5.cmd -dbcodepage 4103 -l SAPSSEXC_5.log -c 100000 -loadprocedure dbsl -rowstorelist /tmp/sapinst_instdir/NW740SR2/HDB/COPY/SYSTEM/DISTRIBUTED/AS-ABAP/DB/rowstorelist.txt' exited with return code 2.

For mode details see 'SAPSSEXC_5.log' file.

SAPSSEXC_3.log:

ERROR exec_ddl_stmt: (DB) ERROR: DDL statement failed

(ALTER TABLE "ENHA_TMDIR" ADD CONSTRAINT "ENHA_TMDIR~0" PRIMARY KEY ( "CLASSNAME", "METHODINDX" ) )

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;SAPTDB:ENHA_TMDIR.$trexexternalkey$ content not unique, cannot define unique constraint. rowCount != distinctCount

DbSl Trace:   execute(), rc=1, rcSQL=349

DbSl Trace: EXECUTE on connection 0, rc=349

ERROR exec_ddl_stmt: (DB) ERROR: DDL statement failed

(CREATE UNIQUE INDEX "ENHA_TMDIR~MTD" ON "ENHA_TMDIR" ( "METHODNAME" , "CLASSNAME" ) )

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;SAPTDB:ENHA_TMDIR.$uc_ENHA_TMDIR~MTD$ content not unique, cannot define unique constraint. rowCount != distinctCount

SAPSSEXC_5.log:

DbSl Trace:   execute(), rc=1, rcSQL=349

DbSl Trace: EXECUTE on connection 0, rc=349

ERROR exec_ddl_stmt: (DB) ERROR: DDL statement failed

(ALTER TABLE "RODIR" ADD CONSTRAINT "RODIR~0" PRIMARY KEY ( "OBJECTTYPE", "OBJECT" ) )

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;SAPTDB:RODIR.$trexexternalkey$ content not unique, cannot define unique constraint. rowCount != distinctCount

Please assist in advising on a solution.

[former_member186845 · Accepted solution]

Hi Willem,

In the error you are facing, you can see that the Primary Key could not be created because duplicated entries was found in the table.

Using like example the PK RODIR~0. Check if there are 2 or more entries with the same value for columns "OBJECTTYPE" and "OBJECT". If so, then it is necessary to know why they are repeated on the database.

1. Source System

The issue could be happening because the source system is already incorrect. Search for duplicated entries on source table. Also, make sure the primary key does exist on database level;

How to solve the issue?

In this case, this is very complicated. I cannot say 100% sure that you can delete the duplicated entry of the table. We do not know what will be the impact. It will be necessary to check the cause this duplicated entry was added on the table.

2. Import

If there is no duplicated entries on source system, the issue could be happening because the import was executed twice for this table. Such error can only happen if there was a problem during the import and it was stopped. Then, when it returned to restart the import, the content of the table was not dropped prior restarting the import. This issue kind of issue is unusual and rare,but it can happen. In order to analyze it, you can check the number of rows that were exported in the <package>.TOC file and compare with the number of rows that exist on table of target database.

How to solve the issue?

If the issue happened during the import, it can simply solved by repeating the import of the table. You can delete the content of the table, manually change the <package>.TSK from 'ok' to 'xeq' in the entry of the table and run again the SWPM tool.

In case you find out the cause for this issue, let me know if you still need any help.

Regards,
Henrique Girardi

SAP Product Support

[Former Member · Accepted solution]

Dear Henrique

Thanks for the assistance.

The table seems fine on the source system, Constraint exists and no duplicate rows:

select count(*) from saptdb.RODIR

COUNT(*)

8,982

SELECT DISTINCT * from saptdb.RODIR

Fetched 8982 row(s)

I restarted the import, and experienced same issue.  I will try to manually change the <package>.TSK and give feedback.

Thanks

Willem

[former_member186845 · Accepted solution]

Hello Williem,

Have you checked how many rows the table has on target database after the import? If it had the same number of rows, you could check if you find any duplicated entry on target database as well, in order to compare with source system.

Anyway, let me know how it goes. If the issue remains, I will need to analyze the following fresh log files:

From target system installation directory:

- SAPSSEXC_5.log

- SAPSSEXC_5.TSK

- SAPSSEXC_5.cmd

- import_state.properties

- import_monitor.log

- sapinst_dev.log

From <dump directory>/DATA:

- SAPSSEXC_5.STR and SAPSSEXC_5.TOC

Regards,
Henrique Girardi

SAP Product Support

[Former Member · Accepted solution]

Dear Girardi

I started the import from scratch, and encountered exactly the same error.  Please find the attached log files.

These are the row counts on the source system:

select count(*) from saptdb.ENHA_TMDIR

COUNT(*)

0

select count(*) from saptdb.RODIR

COUNT(*)

8,982

These are the row counts on the target system when error occurred:

select count(*) from saptdb.ENHA_TMDIR

COUNT(*)

4,245

select count(*) from saptdb.RODIR

COUNT(*)

77,550

These were the jobs in TSK files with status err: 

In SAPSSEXC_5.TSK:

P RODIR~0 C err

SAPSSEXC_3.TSK:

P ENHA_TMDIR~0 C err

I ENHA_TMDIR~MTD C err

When I marked these as 'xeq', the import did not continue, but when I marked them as 'ok', and retried, the import continued and completed. I will follow up by exporting the RODIR table from source and importing.

Best Regards

Willem

log_files.RENAME_TO_zip.txt.zip

[former_member186845 · Accepted solution]

Hello Willem,

When you changed the .TSK from 'err' to 'ok', you just skipped the import of content for both tables. Therefore, these tables are inconsistent now on the target database.

This is very weird the content of the tables on source system are so different, Are you still using the source system? If so, I strongly recommend you to repeat the export and import, in order to guarantee the database consistency.

From the export you executed, I can tell you how many rows were exported by checking the files SAPSSEXC_5.STR and SAPSSEXC_5.TOC from directory /<dump directory>/DATA/.

However, now you have changed the .TSK to 'ok', which is not good, once you did not solve the issues during the import and probably are data missing in these tables.

Regards,
Henrique Girardi

SAP Product Support

[Former Member · Accepted solution]

Hi Henrique

Thanks, I understand your concern.

Table RODIR still has 8,982 rows on Source - table content seem not to have changed since export. (Table content explained in http://www.se80.co.uk/saptables/r/rodi/rodir.htm)

Table ENHA_TMDIR has 0 rows on Source.

Would the database table export and import that I have completed of table RODIR not have solved this inconsistency?

From the SAPSSEXC_5.TOC file I can see that the correct amount of rows were executed:

tab: RODIR

fil: SAPSSEXC_5.001 1024

42368 43880

eot: #8982 rows 20160114011342

Best Regards

Willem

[Former Member · Accepted solution]

Hello Willem

i ahd the same symptom and exporting from HANA to MSSQL. I ahd to do the same to overcome the problem.

Did you find any issue after this choice? I have checked the table and is 0 records for me too.

It seems a bug of the kernel (maybe R3load?).

[Former Member · Accepted solution]

Hi Bruno

Thanks, I had no issues as to yet.  But since this is a demo system, I'm busy redoing the export/import using a "SAP HANA-specific Backup/Recovery" method instead, will update on the outcome.

[Former Member · Accepted solution]

Hi

I used the "SAP HANA-specific Backup/Recovery" method, and had no issues