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?
ERROR Insert Duplicate Key
-
- Posts: 2413
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
ERROR Insert Duplicate Key
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
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
-
- Posts: 2413
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
-
- Posts: 2413
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
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
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
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
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
Rocketman
V8.7 Developer Edition. Server 2016 Standard edition. MySql 5.5
V8.7 Developer Edition. Server 2016 Standard edition. MySql 5.5
-
- Posts: 2413
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
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?
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?
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
-
- Posts: 2413
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia