Welcome Serena Central users! CLICK HERE
The migration of the Serena Central community is currently underway. Be sure to read THIS MESSAGE to get your new login set up to access your account.
Highlighted
Micro Focus Frequent Contributor
Micro Focus Frequent Contributor
216 views

<(CM) Support tip> Different revisions of one record has duplicate Revision Numbers

Issue:

Different revisions of one record shares the same revision number.

Cause:

The Database (DB) index evRecElecUri on table TSERECVSN is non-unique, hence it allows the values for both columns evRecElecUri and evVersionNbr between two or more rows are the same, which can result in the same record (value of evRecElecUri are the same ) to have two or more revisions sharing the same revision ID (evVersionNbr are the same too). Due to the index evRecElecUri being non-unique if anything unexpected occurs that potentially can result in the TSERECVSN table being updated twice when a revision is added it could result in duplicated revision numbers being added for different revisions.

Solution :

**Please test this in a Test environment before taking any action against your PROD DB**

1. Take back up for table TSERECVSN before taking any further action.

For SQL Sever

select * into TSERECVSN_BAK from TSERECVSN;

For Oracle

create TABLE TSERECVSN_BAK AS select * from TSERECVSN;

2. Identify the duplicated rows, and have the duplicated rows removed, execute the SQL below against the DB, it will find the duplicated revisions.

SELECT uri, evRecElecUri, evVersionNbr FROM TSERECVSN MAIN WHERE EXISTS ( SELECT * FROM TSERECVSN SUB WHERE SUB. evRecElecUri = MAIN.evRecElecUri AND SUB.evVersionNbr = MAIN.evVersionNbr AND SUB.uri <> MAIN.uri) ORDER BY evRecElecUri DESC;

The following is an example of the output from the SQL :

Untitled.png

3. Use the SQL below to delete the duplicated rows.

DELETE FROM TSERECVSN WHERE URI IN (XXXX,XXXXX,XXXXX…..);
//***XXXX are the uris for the duplicated rows that needs to be removed ***)

The following is an example of the SQL to clean up the duplicates shown above. As a rule of thumb delete the older (lower) uri’s of the output

DELETE FROM TSERECVSN WHERE URI IN (293541,291241,291524,292812,293108,292309,289608,288732,284463);

4. Execute the SQL below to confirm there is no longer any duplicated revisions (the SQL should return 0 row).

SELECT uri, evRecElecUri, evVersionNbr FROM TSERECVSN MAIN WHERE EXISTS ( SELECT * FROM TSERECVSN SUB WHERE SUB. evRecElecUri = MAIN.evRecElecUri AND SUB.evVersionNbr = MAIN.evVersionNbr AND SUB.uri <> MAIN.uri) ORDER BY evRecElecUri DESC;

5. Execute the SQL below to drop the Non-Unique index evRecElecUri on table TSERECVSN


For SQL Sever
DROP INDEX [evRecElecUri] ON [dbo].[TSERECVSN];

For Oracle
DROP INDEX evRecElecUri;

6. Use the SQL below to create the index evRecElecUri on table TSERECVSN as Unique

For SQL Sever
CREATE UNIQUE NONCLUSTERED INDEX [evRecElecUri] ON [dbo].[TSERECVSN]
([evRecElecUri] ASC,
[evVersionNbr] DESC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

For Oracle
CREATE UNIQUE INDEX EVRECELECURI ON TSERECVSN (EVRECELECURI ASC, EVVERSIONNBR DESC);

7. Check again via HPE RM/CM Client, and the duplicated revisions should no longer appear

1 Reply
Contributor.. Japps3 Contributor..
Contributor..

Re: <(CM) Support tip> Different revisions of one record has duplicate Revision Numbers

Hi Ethan,

We have noticed the same issue and are looking to run your proposed fix. Just confirming, does the step to re-create the index prevent the duplicates occurring thereafter?

Thanks

Josh

The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.