ISSUE DESCRIPTION: When restoring MYSQL data in MSSQL database getting error like Java.sql.BatchupdateException: Violation of UNIQUE KEY Constraint ‘usersitemapping_UK’. Cannot insert duplicate key in object ‘dbo.usersitemapping’.
CAUSE: In the above case some users have no site assigned and that creates problem when migrating from MYSQL to MSSQL. Because MYSQL allows more than one NULL for the unique columns whereas MSSQL will not, that is the reason why its throwing Unique Key Violation in MSSQL.
Steps to fix.
Step 1: Need to find the list of users with no sites assigned. Below query will show the number of duplicate entries (i.e) user without sites.
select userid, siteid, count(userid) from usersitemapping where siteid is NULL group by userid,siteid having count(userid)>1;
(Connect to the MYSQL database and run the above query)
Step 2: We can delete those users without sites or merge those users and then migrate to MSSQL.
To merge users please follow the instructions from the link below.
Step 3: Now take a new backup and try to restore it.