ERROR Insert Duplicate Key

customaware · Post by **customaware** » Tue Dec 31, 2013 1:44 am

I have a process that scans thru all employees (about 300 so far) and
creates a particular number of records in a different BO so that I can do calculations on the data for each Employee.

This has been working fine. However, for some reason I am now getting an error when the process creates a record. It is working ok for about a 1000 records and then crash.

Internal error. Error persisting business object LeaveAssignmentComponent Violation of PRIMARY KEY constraint 'PK__LMS_LEAV__3214EC2734642C8F'. Cannot insert duplicate key in object 'dbo.LMS_LEAVEASSIGNMENTCOMPONENT'. The duplicate key value is (26311).

Can anyone suggest why I might be getting this error as I would have expected the system to always generate a unique ID for a created record?

ACDC · Post by **ACDC** » Thu Jan 02, 2014 11:12 am

It sounds like you have a duplicate ID brought on by a database restore or table that was copied over or something similar. The system uses bas_idgen table in the DB to generate unique ID numbers. You might have to reset this number to a higher number outside the number sequence in your app to resolve the problem

customaware · Post by **customaware** » Thu Jan 02, 2014 11:58 pm

Thanx ACDC......

You learn a little something new every day.

customaware · Post by **customaware** » Tue Jan 07, 2014 9:22 am

Hi ACDC,

How can you tell which ID number in bas_idgen is for which BO?

Powerm · Post by **Powerm** » Tue Jan 07, 2014 12:12 pm

I believe the ID is distributed between the different BO's :

if the bas_idgen = 5 then Client.ID = 6
and if you create a new Invoice, Invoice.ID = 7
....and so on

ACDC · Post by **ACDC** » Tue Jan 07, 2014 9:32 pm

The only way to resolve this is to go through your tables and find out where the ID no's got out of sync with BAS_IDgen. You will get some clues when you see the error message pop up when BASID_gen is trying to create an ID No that already exists in the table its trying to write to.

Once you know the table you can start working through the data and see the number range that will be higher than the current BAS_IDGen Number.

The quick solution is to edit the BAS_IDGen number so that it is larger than any of the ID numbers in the false ID number range. This will let BAS_IDGen resume its number allocation . So if its blocked at 26311 make BAS_IDGen 36311 so it can resume (hopefully you don't have more than 1000 duplicates)

Also, you will have to look at maybe recreating the records that have duplicate ID numbers - otherwise you might have issues that creep up on you down the road - but that depends on the complexity of your table

rocketman · Post by **rocketman** » Wed Jan 08, 2014 10:10 am

ACDC is right BAS_idgen is one unique number covering ALL BSV's not just BO's so every record in every BO in every BSV has a unique ID (not sure if this still applies if you have specified to create a your BSV in it's own container)

So if you are able, you really need to find out what the last number was in the original installation and set this as the start number in the new installation.

I assume you've moved one of your BSV's onto a new server or imported the data into a testing server.

What happens is - when you do a fresh install of AwareIM, BAS_idgen is set to 0 and anything new that you enter will count from 0. If you import some data that you have previously exported - they will already have unique ID's generated from the old system. So if you have a record with an ID of 10, when BAS_idgen in the new system reaches 10 .... Whoops, duplicate key error.

You could try exporting without the ID keys and then importing to a new installation and let AwareIM assign new ID's but if you have a lot of related BO's this won't work because the relationship keys won't match the new keys.

You could just guestimate a very high number and set this into bas_idgen on the new installation

customaware · Post by **customaware** » Wed Jan 08, 2014 10:15 am

I understand that concept and it makes sense.

The bit that I do not understand is that I have 6 records in my BAS_idgen file.

How come there are so many? Or has something screwed up?

ACDC · Post by **ACDC** » Wed Jan 08, 2014 12:38 pm

bas_idgen has one attribute in the table called Max_ID and it contains the last allocated ID number

I have never seen more than one entry in this table, however there could be a reason for this. It may be a good idea to ask support for a confirmation of this

customaware · Post by **customaware** » Wed Jan 08, 2014 11:13 pm

I have checked and I have 5.

A watched them as I added data and they all change.

Maybe Support can add a little explanation or clarity here please?

Powerm · Post by **Powerm** » Fri Jan 10, 2014 9:37 am

You must have created duplicates entries during your import process. You must keep there only one entry...make a backup, keep one entry and set the value to 10000 just to try.