Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025

 


Article

Avoiding Duplicate entry errors in RB_RELATIONSHIP and RB_TRANSLATION tables after upgrading to Rollbase 5.3

« Go Back

Information

 
Article Number000091861
EnvironmentProduct: Rollbase
Version: 5.3.0.0
Question/Problem Description
The update_5_3_0.sql file has the following additions to ensure unique records.
 
ALTER TABLE RB_RELATIONSHIP ADD PRIMARY KEY (CUST_ID, RELATIONSHIP_ID, OBJ1_ID, OBJ2_ID);
ALTER TABLE RB_TRANSLATION  ADD PRIMARY KEY (CUST_ID, LANGUAGE, GLOBAL_ID, FIELD_NAME);

Earlier versions of Rollbase did not have this constraint and occasionally duplicate records were found in the database, which now leads to errors regarding Duplicate entry / Primary Key Constraint violation when the update script is executed.
 
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
When an error regarding the Primary Key violation occurs when running the update_5_3_0.sql as part of the upgrade process, note down the table on which the error is reported.
This could be either the RB_RELATIONSHIP or the RB_TRANSLATION (or both tables).

To resolve the error, follow these steps:
  1. Create a database backup (snapshot) as we will be making changes to data in these tables in the database.
  2. If timeout errors are thrown when working the following steps, adjust the query execution timeout parameter as per the size of the table where the error is seen. (the specific timeout depends on the database type).
  3. Clone the table on which the error is thrown
  • For OpenEdge:
--RB_RELATIONSHIP
CREATE TABLE RB_RELATIONSHIP_BACKUP AS SELECT * FROM RB_RELATIONSHIP;
commit;

--RB_TRANSLATION
See Notes section for more information.
  • For MySQL:
--RB_RELATIONSHIP
CREATE TABLE RB_RELATIONSHIP_BACKUP LIKE RB_RELATIONSHIP;
INSERT RB_RELATIONSHIP_BACKUP SELECT * FROM RB_RELATIONSHIP;
commit;

--RB_TRANSLATION
CREATE TABLE RB_TRANSLATION_BACKUP LIKE RB_TRANSLATION; 
INSERT RB_TRANSLATION_BACKUP SELECT * FROM RB_TRANSLATION;
commit;

  • For SQL Server:
--RB_RELATIONSHIP
SELECT TOP 0 * INTO RB_DBO.dbo.RB_RELATIONSHIP_BACKUP from RB_DBO.dbo.RB_RELATIONSHIP;
INSERT INTO RB_DBO.dbo.RB_RELATIONSHIP_BACKUP SELECT * FROM RB_DBO.dbo.RB_RELATIONSHIP;
go

--RB_TRANSLATION
SELECT TOP 0 * INTO RB_DBO.dbo.RB_TRANSLATION_BACKUP from RB_DBO.dbo.RB_TRANSLATION;
INSERT INTO RB_DBO.dbo.RB_TRANSLATION_BACKUP SELECT * FROM RB_DBO.dbo.RB_TRANSLATION;
go
  1. Truncate existing data:
  • For OpenEdge:
--RB_RELATIONSHIP
DELETE FROM RB_RELATIONSHIP;
commit;

--RB_TRANSLATION
DELETE FROM RB_TRANSLATION;
commit;
  • For MySQL
--RB_RELATIONSHIP
TRUNCATE TABLE RB_RELATIONSHIP;
commit;

--RB_TRANSLATION
TRUNCATE TABLE RB_TRANSLATION;
commit;
  • For SQL Server:
--RB_RELATIONSHIP 
TRUNCATE TABLE RB_DBO.dbo.RB_RELATIONSHIP; 
go

--RB_TRANSLATION 
TRUNCATE TABLE RB_DBO.dbo.RB_TRANSLATION; 
go
 
  1. Add the new Primary Key (required only if this table did not have the PRIMARY key defined).
  • For OpenEdge and MySQL:
--RB_RELATIONSHIP
ALTER TABLE RB_RELATIONSHIP ADD PRIMARY KEY (CUST_ID, RELATIONSHIP_ID, OBJ1_ID, OBJ2_ID);
commit;

--RB_TRANSLATION
ALTER TABLE RB_TRANSLATION  ADD PRIMARY KEY (CUST_ID, LANGUAGE, GLOBAL_ID, FIELD_NAME);
commit;
  • For SQL Server:
--RB_RELATIONSHIP
ALTER TABLE RB_DBO.dbo.RB_RELATIONSHIP ADD PRIMARY KEY (CUST_ID, RELATIONSHIP_ID, OBJ1_ID, OBJ2_ID);
go

--RB_TRANSLATION
ALTER TABLE RB_DBO.dbo.RB_TRANSLATION  ADD PRIMARY KEY (CUST_ID, LANGUAGE, GLOBAL_ID, FIELD_NAME);
go
 
  1. Insert back only distinct records:
  • For OpenEdge
--RB_RELATIONSHIP
--create a new table to store only the duplicate primary key.
CREATE TABLE RB_RELATIONSHIP_DUPLICATE (
CUST_ID BIGINT NOT NULL,
RELATIONSHIP_ID BIGINT NOT NULL,
OBJ1_ID BIGINT NOT NULL,
OBJ2_ID BIGINT NOT NULL ) AREA "Relate Data";

-- Populate the table with duplicate keys from RB_RELATIONSHIP_BACKUP:
INSERT INTO RB_RELATIONSHIP_DUPLICATE (CUST_ID, RELATIONSHIP_ID,OBJ1_ID,OBJ2_ID) SELECT CUST_ID,RELATIONSHIP_ID,OBJ1_ID,OBJ2_ID FROM RB_RELATIONSHIP_BACKUP group by CUST_ID,RELATIONSHIP_ID,OBJ1_ID, OBJ2_ID having count(*) > 1;
commit work;

-- Delete the duplicate records from RB_RELATIONSHIP_BACKUP:
DELETE FROM RB_RELATIONSHIP_BACKUP WHERE rowid IN (select MAX(B.ROWID) FROM RB_RELATIONSHIP_BACKUP B INNER JOIN RB_RELATIONSHIP_DUPLICATE D ON B.CUST_ID = D.CUST_ID AND B.RELATIONSHIP_ID = D.RELATIONSHIP_ID AND B.OBJ1_ID = D.OBJ1_ID AND B.OBJ2_ID = D.OBJ2_ID group by D.CUST_ID, D.RELATIONSHIP_ID, D.OBJ1_ID, D.OBJ2_ID);

-- The number of records in "Populate the table with duplicate keys" must be the same as "Delete the duplicate records". If they are different, do not commit the transaction and contact Technical Support.
commit work;

-- insert back the remaining records
INSERT INTO RB_RELATIONSHIP SELECT * FROM RB_RELATIONSHIP_BACKUP;
commit work;

-- drop the backup and duplicate temporary table
DROP TABLE RB_RELATIONSHIP_BACKUP;
DROP TABLE RB_RELATIONSHIP_DUPLICATE;
commit work;

--RB_TRANSLATION 
-- See Notes section for more information.
  • For MySQL:
--RB_RELATIONSHIP
INSERT INTO RB_RELATIONSHIP (CUST_ID,RELATIONSHIP_ID,OBJ_DEF1_ID,OBJ1_ID,OBJ_DEF2_ID,OBJ2_ID) SELECT DISTINCT CUST_ID,RELATIONSHIP_ID,OBJ_DEF1_ID,OBJ1_ID,OBJ_DEF2_ID,OBJ2_ID FROM RB_RELATIONSHIP_BACKUP GROUP BY CUST_ID,RELATIONSHIP_ID,OBJ1_ID,OBJ2_ID;
commit;

--RB_TRANSLATION
INSERT INTO RB_TRANSLATION (CUST_ID,LANGUAGE,GLOBAL_ID,FIELD_NAME,TEXT_VALUE) SELECT DISTINCT CUST_ID,LANGUAGE,GLOBAL_ID,FIELD_NAME,TEXT_VALUE FROM RB_TRANSLATION_BACKUP GROUP BY CUST_ID,LANGUAGE,GLOBAL_ID,FIELD_NAME;
​commit;
  • For SQL Server:  
--RB_RELATIONSHIP
INSERT INTO RB_DBO.dbo.RB_RELATIONSHIP SELECT DISTINCT * FROM RB_DBO.dbo.RB_RELATIONSHIP_BACKUP;
go

--RB_TRANSLATION
INSERT INTO RB_DBO.dbo.RB_TRANSLATION SELECT DISTINCT CUST_ID, LANGUAGE,GLOBAL_ID,FIELD_NAME, CAST(text_value AS NVARCHAR(MAX)) FROM RB_DBO.dbo.RB_TRANSLATION_BACKUP;
go
 
  1. Double check if there are no duplicates:
  •   For OpenEdge and MySQL:
--RB_RELATIONSHIP
SELECT COUNT(*) as DUP_COUNT_AFTER_FIX FROM RB_RELATIONSHIP GROUP BY CUST_ID,RELATIONSHIP_ID,OBJ1_ID,OBJ2_ID HAVING COUNT(*) > 1;

--RB_TRANSLATION
SELECT COUNT(*) as DUP_COUNT_AFTER_FIX FROM RB_TRANSLATION GROUP BY CUST_ID,LANGUAGE,GLOBAL_ID,FIELD_NAME HAVING COUNT(*) > 1;
  • For SQL Server:
--RB_RELATIONSHIP
SELECT COUNT(*) as DUP_COUNT_AFTER_FIX FROM RB_DBO.dbo.RB_RELATIONSHIP GROUP BY CUST_ID,RELATIONSHIP_ID,OBJ1_ID,OBJ2_ID HAVING COUNT(*) > 1;

--RB_TRANSLATION
SELECT COUNT(*) as DUP_COUNT_AFTER_FIX FROM RB_DBO.dbo.RB_TRANSLATION GROUP BY CUST_ID,LANGUAGE,GLOBAL_ID,FIELD_NAME HAVING COUNT(*) > 1;
 
  1. If step 7 returns 0, drop the backup table.
  • For OpenEdge and MySQL:
--RB_RELATIONSHIP
DROP TABLE RB_RELATIONSHIP_BACKUP;
commit;

--RB_TRANSLATION
DROP TABLE RB_TRANSLATION_BACKUP;
commit;
  • For SQL Server:
--RB_RELATIONSHIP
DROP TABLE RB_DBO.dbo.RB_RELATIONSHIP_BACKUP;
go

--RB_TRANSLATION
DROP TABLE RB_DBO.dbo.RB_TRANSLATION_BACKUP;
go

 
Workaround
Notes
References to Other Documentation:

Progress Article(s):
000090764, Avoiding Duplicate entry errors in RB_TRANSLATION using OpenEdge Database
Attachment 
Last Modified Date4/4/2019 3:39 AM