ISSUE WHILE MIGRATING MYSQL DATA OVER MSSQL.

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’.

 1

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.

http://helpdesk.manageengine.com/AddSolution.do?mode=viewSol&solID=131102

Step 3: Now take a new backup and try to restore it.

 

 

 

Need More Help?

Are you looking for a personalized customer support? Submit your question or Call our Toll Free Number to speak with ServiceDesk Plus MSP Expert who can offer a better solution for your requirements.

Submit a Ticket
Email : sdpmsp@manageengine.com
Toll Free : 1-888-720-9500