14 Replies Latest reply on Mar 19, 2019 2:37 AM by Misi Mladoniczky

    Delta Data Migration - Unique Index Violation

    Marek Ceizel
      Share:|

      Hello experts,

       

      I kindly ask you here for discussion / tips / ideas.

       

      We are doing a Delta Data Migration. Source is a Production Platform and the time difference of DB Dump on Destination Platform is about one month (expected lot of records). We use RRRchive (thank you Misi Mladoniczky) with setting is Copy and Overwrite, Qual is Modified_Date (6) and compare id is Request_ID (1) - this is fixed in RRRchive.

       

      A good example for our problem is form CTM:People Permission Groups. Of course we cannot ask the customer to freeze the Production Platform so much that the People Permission cannot be changed. But in this form the entries are deleted and created by the system always when you modify some Person Permissions. Therefore we got almost 800 unique index violations during Delta Data Migration on this form. Unique Index is InstanceID and Person_ID+PermissionGoupID.

       

      I understand what happened. Some Permissions were removed, some were added. For different Person or maybe for the same person. It could happen that for the same person permissions were removed and the same permission added again. why and however the Request_ID changed, but the Unique index values stayed the same (as PersonID was not changed and Permission Group ID either).

       

      Therefore I need to remove records which do not exist on Production (source) but existon on destination.

      - RRRchive syncdeleted crashes the tool on my server , I don't know why.

      - DeltaDataMigrator from BMC has no options for Deleting (or is this somehow possible).

       

       

      Is there some other tool or best practice I can use except truncating destination (because I don't want to truncate all andf the CTM:People Permission Groups from is not the only one where I got this problem)

       

       

      Thanks a lot for help/tips

       

      Best Regards

      Marek

        • 1. Re: Delta Data Migration - Unique Index Violation
          Ryan Nicosia

          When we ran DDM, there were some forms where we just used Import tool to import records and there were also a couple forms where we moved the data using Atrium Integrator.

           

          My recommendation would be to just delete the data in the target and then do a complete export and import of the values from your source.  You can use either of the 2 tools mentioned.

          • 2. Re: Delta Data Migration - Unique Index Violation
            Mohammad Rehman

            I use DDM with compare option, after few sync i run the SQL queries to get the difference on both systems and then run another set to mark the orphan/duplicate records to hard delete.

            Note:- I have Oracle database, its easier to create production db link remprod on staging db

            Here is the comparing query on Oracle DB

            Check Records missing on Staging:

            SELECT PEOPLE_PERMISSION_GROUP_ID, REMEDY_LOGIN_ID, PERMISSION_GROUP, PERMISSION_GROUP_ID, INSTANCEID, ASSIGNEE_GROUPS_PARENT, PERMISSION_TAG_NAME, PERSON_ID FROM CTM_PEOPLE_PERMISSION_GROUPS@REMPROD

            MINUS

            SELECT PEOPLE_PERMISSION_GROUP_ID, REMEDY_LOGIN_ID, PERMISSION_GROUP, PERMISSION_GROUP_ID, INSTANCEID, ASSIGNEE_GROUPS_PARENT, PERMISSION_TAG_NAME, PERSON_ID FROM CTM_PEOPLE_PERMISSION_GROUPS;

             

            Check Records on Staging but not on production:

            SELECT PEOPLE_PERMISSION_GROUP_ID, REMEDY_LOGIN_ID, PERMISSION_GROUP, PERMISSION_GROUP_ID, INSTANCEID, ASSIGNEE_GROUPS_PARENT, PERMISSION_TAG_NAME, PERSON_ID FROM CTM_PEOPLE_PERMISSION_GROUPS

            MINUS

            SELECT PEOPLE_PERMISSION_GROUP_ID, REMEDY_LOGIN_ID, PERMISSION_GROUP, PERMISSION_GROUP_ID, INSTANCEID, ASSIGNEE_GROUPS_PARENT, PERMISSION_TAG_NAME, PERSON_ID FROM CTM_PEOPLE_PERMISSION_GROUPS@REMPROD;

             

            then you can run this to mark these orphan records from staging:

            UPDATE CTM_PEOPLE_PERMISSION_GROUPS

            SET Submitter='DELETE'

            WHERE PEOPLE_PERMISSION_GROUP_ID IN ( Select a.PEOPLE_PERMISSION_GROUP_ID from

            (SELECT PEOPLE_PERMISSION_GROUP_ID, REMEDY_LOGIN_ID, PERMISSION_GROUP, PERMISSION_GROUP_ID, INSTANCEID, ASSIGNEE_GROUPS_PARENT, PERMISSION_TAG_NAME, PERSON_ID FROM CTM_PEOPLE_PERMISSION_GROUPS

            MINUS

            SELECT PEOPLE_PERMISSION_GROUP_ID, REMEDY_LOGIN_ID, PERMISSION_GROUP, PERMISSION_GROUP_ID, INSTANCEID, ASSIGNEE_GROUPS_PARENT, PERMISSION_TAG_NAME, PERSON_ID FROM CTM_PEOPLE_PERMISSION_GROUPS@REMPROD) a);

             

            You can adjust the queries according to your environment.

            2 of 2 people found this helpful
            • 3. Re: Delta Data Migration - Unique Index Violation
              Marek Ceizel

              Hello together,

               

              Thanks guys for the inputs.

              @Ryan, this is no go for us. We need to do a real delta data migration. We have no time for export, import, compare or check. now it is not prod environment but when we go live, we have only one hour reserved outage.

               

              @Mohammad: SQL is of course an option. Anyway we have no point in network where we can access both databases. we managed hardy to get a computer which can access both arsystems (so actual prod and new dev/test/prod).

               

              These two tools should be the easy solution but one has no features and another one crashes. I will ask Misi if there is some bug. The syncdeleted of rrrchive would be good option for us.

               

              I just thought there is some another tool or some hack for deltadatamigrator from bmc (so that it deletes too).

               

               

              Thanks again

              best regards

              Marek

              1 of 1 people found this helpful
              • 4. Re: Delta Data Migration - Unique Index Violation
                Ryan Nicosia

                This won't impact your outage window.  On day of your outage, 2 hours before you actually start, take a fresh export of that form and import it to your target.  It won't take you but 15 minutes.   You put a real admin freeze in place on people permissions the day before and you should be all set.

                • 5. Re: Delta Data Migration - Unique Index Violation
                  Misi Mladoniczky

                  Hi,

                   

                  You could take out a few forms and do a target_clearallrecords=YES which would delete all records prior to your execution.

                   

                  I presume you use the transfertype=SYNCTOTARGET, which might encounter these problems based on how the data was changed. Running it multiple times should usually decrease the number of violation errors and finally there would be none left.

                   

                  If a lot of records was deleted from the form, you might try the transfertype=SYNCDELETED first, which would delete all records no longer existing. After this is done you can run the transfertype=SYNCTOTARGET.

                   

                          Best Regards - Misi, RRR AB, http://rrr.se

                  5 of 5 people found this helpful
                  • 6. Re: Delta Data Migration - Unique Index Violation
                    Jameer Inamdar

                    Hi Marek,

                     

                    Migrator can help you here, Follow link How to handle record deletion in DDM

                     

                    HTH

                    1 of 1 people found this helpful
                    • 7. Re: Delta Data Migration - Unique Index Violation
                      Misi Mladoniczky

                      Hi,

                       

                      One of the good things with RRR|Chive compared to DDM, is that there will be no orphaned records. The SYNCTOTARGET will take care of everything.

                       

                      The problem with the duplicate index might not be one of orphaned records, it might be that of changed records as well.

                       

                      If you have three records for example, with data A, B, C.

                       

                      You can change this in the source system to X, B, A and then to C, B, A.

                       

                      The problem when you do a delta migration is that when you try to import the first record which now should contain C, the target server already has C in the last record resulting in a duplicate index error. When it comes to the last record that should change from C to A, it will also fail as the first record failed to update from A to C...

                       

                      In other words it is hard to do a migration of the following data without first deleting all records:

                      Source Server data to migrate to Target ServerTarget Server data prior to Delta Data MigrationComment
                      CAWill fail as C already exist in the last record
                      BB
                      ACWill fail as A already exists in the first record.

                       

                      So in this case you can opt for the target_clearallredords=YES in RRR|Chive to delete all records in the target prior to the transfer.

                       

                      The alternative would be to manually delete the failed records and run the SYNCTOTARGET operation again.

                       

                              Best Regards - Misi, RRR AB, http://rrr.se

                      4 of 4 people found this helpful
                      • 8. Re: Delta Data Migration - Unique Index Violation
                        Marek Ceizel

                        Hello Misi,

                         

                        Thanks a lot for you reply. Actually your example is also good anyway this is not our case. You compare field 1 in rrrchive and this is never reused after record deletion. If data are changed in a record, then almost never unique indexed fields.

                         

                        Our problems were data removed on source and recreated - like permissions

                        New data created on destination using requestids of different data from source (but we shouldnt create new data on dest, i agree)

                        Data removed from source but present on dest.

                         

                        As you wrote, deletion and synctotarget should be the right.

                        Btw-any idea why syncdeleted crashes the tool (leaving modified filters in system) ?

                         

                        Thank you

                        Best regards

                        marek

                        • 9. Re: Delta Data Migration - Unique Index Violation
                          Misi Mladoniczky

                          Hi,

                           

                          I do not know why it crashes, of course it should not.

                           

                          I think the SYNCDELETED might not be used that often. I recall a bug that made SYNCDELETED crash, but this was fixed.

                           

                          Did you use the version compiled against the 9.1 ARAPI? The 7.6.04 version is from 2013, and it might not have the SYNCDELETED bug fixed...

                           

                                  Best Regards - Misi, RRR AB, http://rrr.se

                          • 10. Re: Delta Data Migration - Unique Index Violation
                            Mohammad Rehman

                            Hi Marek,

                             

                            I just wanted to make you and other honorable community members who are upgrading to 9.1.04x from previous versions, please be aware if you are using DDM or using User tool to export and Import using Data Import tool or any utility which is written in C to export will lock out the users while sync of User form.

                            Work around is export the "login name" and "password" fields using midtier ar system report and then import using Data import tool with option update existing record and duplicate check using login name field with suppress filter check.

                            Misi Mladoniczky, please share your tool update whether it uses C API or Java API.

                            Here is the Defect ID SW00540999

                            Thanks

                            Mohammad

                            3 of 3 people found this helpful
                            • 11. Re: Delta Data Migration - Unique Index Violation
                              Raido Oja

                              I just tried both 9.1 and 7.6.04 API versions and SYNCDELETED crashes every time. Anyone out there who has been able to use SYNCDELETED option  successfully?

                              • 12. Re: Delta Data Migration - Unique Index Violation
                                Misi Mladoniczky

                                Hi,

                                 

                                I know there was a bug that made that option crash. It was some new stuff I introduced that affected it. But I thought this was fixed.

                                 

                                Maybe you can email me the config you are using, or share it here. And maybe give some more details on exactly when/where it crashes?

                                 

                                The SYNCDELETED is very seldom used nowadays, since SYNCTOTARGET was introduced. SYNCTOTARGET does both things in one go.

                                1. Copies updated/new records
                                2. Deletes records removed from the source server

                                 

                                        Best Regards - Misi, RRR AB, http://rrr.se

                                • 13. Re: Delta Data Migration - Unique Index Violation
                                  Raido Oja

                                  Here are the contents of my config file. It reads the file ok, analyzes the forms ok but then at the end crashes so seems like at the point where it starts the actual delete of records. SYNCTOTARGET works fine, including deleting records in target.

                                   

                                  # connection

                                  source_server   =

                                  source_tcp      =

                                  source_user     =

                                  source_password =

                                  target_server   =

                                  target_tcp      =

                                  target_user     =

                                  target_password =

                                   

                                  # options

                                  clientcharset   = UTF-8

                                  splitsearch     = YES

                                  transfertype    = SYNCDELETED

                                  target_disablemergefltr  = YES

                                  target_disableaudit      = YES

                                  progressbar     = YES

                                  logclearonrun   = YES

                                   

                                  # log

                                  logfile         = D:\Users\rrr_config\rrr_output.log

                                   

                                  # forms to sync

                                   

                                  multipleforms   = \

                                  HPD:Help Desk

                                  • 14. Re: Delta Data Migration - Unique Index Violation
                                    Misi Mladoniczky

                                    Strange. I tried to provoke this issue in house, but failed to do so...

                                    Do you have audit turned on for RPD:Help Desk?