topic Re: sde duplicate geometry being created (error cannot insert duplicate key on reconcile) in ArcGIS Enterprise Questions

sde duplicate geometry being created (error cannot insert duplicate key on reconcile)

DavidJenkins — Tue, 16 Apr 2013 19:18:19 GMT

I am managing a production workflow with about a half-dozen editors in a versioned environment set up against a single ArcSDE instance (SQL Server 2008). Every once in a while something will happen where the editors will begin getting errors on reconcile about duplicate keys. Upon looking into the issue, I discovered that though there are two physical records in the geometry (duplicates) but only one record in the business table and the system will not allow a delete of the object(s). My initial thought was "multi-part feature" so explode, of course the result was "no multipart features found...".

Since it would not let the editor reconcile, I have been able to delete the affected version and recreate it minimizing the losses but it's still a loss...

Here's the thing. I am using Excel to track and report editing progress. I do this by executing sql queries (built and tested in SQL Server) through Excel data connections to read the contents into a worksheet, pivot the results and create graphs and charts. This is nothing new. I've been doing this for years and have never had a problem before. However, I've surmised that the error happens when there are active editing sessions and I run the reports.

I have therefore suspended the use of tracking progress using Excel but now I am faced with reporting progress some other way.

If anyone understands what may be happening here I would appreciate some insight so I can plan a new path for reporting.

Thank you,

Dave Jenkins

Re: sde duplicate geometry being created (error cannot insert duplicate key on reconc

nicogis — Wed, 17 Apr 2013 18:34:20 GMT

are you installed available patches for sql server and arcgis?

Re: sde duplicate geometry being created (error cannot insert duplicate key on reconc

DavidJenkins — Wed, 17 Apr 2013 18:40:43 GMT

are you installed available patches for sql server and arcgis?

Yes, I'm somewhat a stickler for such things. I try to pushed forward as much as possible with all patches unless there's a problem.

Since my last post I've completely disassociated my reports from the database but we occasionally have the same issue. The editors will be working when all of the sudden one of them will have the error "cannot insert duplicate key" upon reconcile. Then another one will get it and another... and so on. Upon monitoring the issue more closely we're closing in on the fact that the issues seem to occur "around" the same time of day. I'm working with our IT department to see if they're performing any maintenance during these times but it seems unlikely. I'm wondering if it may be related to communication disruptions with the server.

Dave Jenkins

Re: sde duplicate geometry being created (error cannot insert duplicate key on reconcile)

TerryAdams — Thu, 21 Aug 2014 01:49:53 GMT

David,

Saw your post.

We are having exactly the same issue and cannot figure out why it is happening. Same thing as you report, every now and again we're getting duplicate primary key errors when GIS officers attempt to reconcile. ESRI support have thus far been stumped as well. We can go for months without issue - then boom, we cannot get rid of the issue no matter what we try.

Did you have any luck at all in figuring this out?

Re: sde duplicate geometry being created (error cannot insert duplicate key on reconcile)

AsrujitSengupta — Thu, 21 Aug 2014 02:20:03 GMT

did "sdegdbrepair -o daignose_table" report any inconsistency?

Re: sde duplicate geometry being created (error cannot insert duplicate key on reconcile)

TerryAdams — Thu, 21 Aug 2014 02:48:50 GMT

Unsure of the command used I admit (not done by me), but yes, after the last reconcile / post we have duplicates reported in each of the editors' (3 of) versions when we run a command line check. We do have a command line repair tool ESRI alerted us to which fixes the problem. However, this requires all users to be disconnected from the system which has implications in our environment.

To perhaps make the situation more clear.

Editor 1 reconcile/post with admin version

Editor 2 reconcile/post with admin version

Editor 3 reconcile/post with admin version

Editor 2 reconcile with admin version

Editor 1 reconcile with admin version

This is to get all 3 editors versions the same.

Then....

Admin Reconcile / Post with SDE default.

Admin compress the database.

If editor1, 2 or 3 then tries to reconcile again....... duplicate primary Key is reported. This would - upon some research - appear to be a standard SQL duplicate primary key error.

Now.....

Load in the suspect dataset into arcmap.

open the attribute table.

What you find is that when you select one of the 'duplicated' features,the attribute table will report 2 selected features, I assume because there are two entries in the database using the same primary key. You can also try highlighting one feature from the attribute table and you will also get two features being reported as selected.

Anyone who's had this happen and can share info about it we'd like to hear from you. We are stumped.

Re: sde duplicate geometry being created (error cannot insert duplicate key on reconcile)

AsrujitSengupta — Thu, 21 Aug 2014 02:57:59 GMT

Run the "sdegdbrepair -o daignose_table" and report the output here. it may provide a better insight.

ArcSDE Administration Command Reference‌

Examples:

SQL Server: sdegdbrepair -o diagnose_tables -d SQLSERVER -V ALL -s sqlserver_instance_name -p sa -u sa_password -D test -i sde:sqlserver:sqlserver_instance_name

Oracle: sdegdbrepair -o diagnose_tables -d ORACLE11G -V ALL -u sde -p sde_password@oracle_netservice_name -s server_name

Re: sde duplicate geometry being created (error cannot insert duplicate key on reconcile)

TerryAdams — Thu, 21 Aug 2014 03:41:12 GMT

Asrujit - thanks for your help. Appreciated!

Output from the command is as follows. This *was* the command we were using as it turns out.

***

402 duplicate rows found in
ARCSDE.GISDBA.COB_HOUSE_NOS

Versions:

SPATIALOFFICER1.SpatialOfficer1

Diagnose Tables: 235
multiversioned tables examined,

1 multiversioned tables had orphaned, duplicate, missing or redundant rows.

Re: sde duplicate geometry being created (error cannot insert duplicate key on reconcile)

AsrujitSengupta — Thu, 21 Aug 2014 03:55:08 GMT

So as the output indicates,there are duplicate entries present in the geodatabase which needs to be taken care off.

Now take a full database backup and then run the "sdegdrepair -o repair_tables" command to remove these duplicate records. Make sure that nobody is connected to the geodatabase and working on it when you execute this command.

Re: sde duplicate geometry being created (error cannot insert duplicate key on reconcile)

AsrujitSengupta — Thu, 21 Aug 2014 03:59:17 GMT

This will take care of the errors for now, however if this is a regular/frequent observation, I would suggest looking into your workflow once more. Maybe discussing with ESRI Tech-Support will give you a better idea.

Also is all the editing done using ArcGIS applications? Or 3rd Party Applications are used to edit\update the data as well?

Re: sde duplicate geometry being created (error cannot insert duplicate key on reconcile)

AsrujitSengupta — Thu, 21 Aug 2014 04:14:19 GMT

Upon taking a closer look at your workflow, why don't you suggest all the 3 editors to delete and recreate their versions after reconcile/post with the Admin version is done. That is also a recommended workflow to recreate your versions from time to time.

You can automate this as a script as well if needed, to be executed at the desired day/time.

Re: sde duplicate geometry being created (error cannot insert duplicate key on reconcile)

TerryAdams — Thu, 21 Aug 2014 04:14:33 GMT

Thanks for your help again Asrujit.

Ok - so the "sdegdrepair -o repair_tables" command is one we're familiar with, and we've run that just now. So problem resolved once again for now. But the underlying cause we're still no closer to solving. The reconcile and post procedure I detailed above is indeed what we were instructed to do by ESRI support after we opened no less than two tickets with them. Can you suggest email contact with someone who could assist further?

All the editing is done within ESRI ArcMap ver 10.1. ArcGIS 10.1 SP1 for Desktop. We use no 3rd party applications to edit our data.

So at this point, if anyone can suggest, or see a problem with our workflow then we'd like to hear it.

Re: sde duplicate geometry being created (error cannot insert duplicate key on reconcile)

TerryAdams — Thu, 21 Aug 2014 06:29:22 GMT

We have gone down the path of recreating versions before, and have actually done that again in this instance as well. It really needs to be said though, that you shouldn't have to do this. The issue needs to be resolved instead of using a series of workarounds to temporarily get past these errors.

Could you provide us an example or template of the script you'd use to recreate versions?

Re: sde duplicate geometry being created (error cannot insert duplicate key on reconcile)

BillFox — Thu, 21 Aug 2014 15:51:27 GMT

I'm not sure if this will prevent this issue but we use a weekly maintenance window to compress our protected DEFAULT version to state zero. This requires the editors to reconcile and post before the maintenance kicks off. All versions are deleted that night. Versions needed for applications are recreated with a batch file of sde commands after compression to state zero. The desktop editors create a fresh private version when they begin work the following morning. The same maintenance workflow is required for service packs, upgrades, etc.This has helped maintain the best performance and reduce strange errors like orphaned records.

Re: sde duplicate geometry being created (error cannot insert duplicate key on reconcile)

DavidColey — Thu, 21 Aug 2014 16:05:23 GMT

Hey Dave, I had something like this awhile back before we moved over to sql geometry, only in reverse to what you describe, i.e. more business records than geometry. It was happening because certain editors were copying and pasting records from spreadsheets in a way that was violating the key constraints.

Basically I set up sql scripts to remove the dups and clean any and all dependencies in both directions. I've got some scripts somewhere, I'll see if I can round them up if you don't have this solved-

Re: sde duplicate geometry being created (error cannot insert duplicate key on reconcile)

TerryAdams — Fri, 22 Aug 2014 02:01:30 GMT

Hi David. Am interested in what you found about your editors copying data from spreadsheets causing the duplicate primary key errors. This is something we theorized could be happening in our situation, but our errors weren't consistent enough to confirm or disprove. I admit though that I still can't fathom how copying data into attribute fields from spreadsheets could cause database primary key errors. Could you elaborate?

Re: sde duplicate geometry being created (error cannot insert duplicate key on reconcile)

DavidColey — Mon, 25 Aug 2014 16:51:35 GMT

Well, there's not much to tell. Prior to our move to SQL Geometry at 10.1, I found that for certain utilities feature classes like hydrants, there would be from time to time more rows in the business table than in the feature (f) table. I discovered that nugget as I was transforming data into Intergraph format via FME.

I didn't even think it was possible to create or otherwise load rows into a feature class using standard ArcGIS front-end methods and not create geometry, but it happened. So it's not a primary key error per say, it's more of a key-constraint violation I would guess.

What I suspect had happened is that hydrants data, coming in from an xy event table created from a spreadsheet perhaps had inverted x,y's or were perhaps in a differenent coordinate system, or had differenent units. But because we use well-defined x,y domains, resolution and tolerances I think the information was added to the A and F table but upon, reconcile, post and/or compress, the invalid values in the F table were dropped, but the business rows remained. That's my theory.

Re: sde duplicate geometry being created (error cannot insert duplicate key on reconcile)

DavidWendelken — Tue, 11 Apr 2017 14:29:27 GMT

I know this answer is a good bit after the question was asked, but it might help someone else.

Scenario: Attempting to add a new record to a geometry table via the arcgis rest service associated with it.   We get a duplicate primary key id.   The admin log for the arcgis website says the objectid value it's trying to use. We look in the table and there is not only a record in the table with that id, there are more records with objectid values higher than that record.

As far as I can tell, the sde database schema was designed back when DBase II was the bee's knees and it was ported as was to sql server. So, instead of using the built-in increment id feature available in sql server, the existing code was used.   There is a next id table per geometry table and that table was out of whack.

The sql server database has a stored procedure that you can execute if you're in that situation. It will increment the next objectid value in that next id table by 1.    Obviously, if the next id is 100 less than the highest objectid in the table, you'll need to do so 100 times. (Or open up the stored procedure, figure out what it does, and hack the next id table directly, but that's almost certainly an unsupported behavior.)

Here's a script to invoke it:

USE [YourDatabaseName]
GO
DECLARE @return_value int,
                  @rowid int
EXEC @return_value = [dbo].[next_rowid]
            @owner = N'YourSchemaName',
            @table = N'YourTableName',
            @rowid = @rowid OUTPUT

SELECT @rowid AS N'@rowid'
SELECT 'Return Value' = @return_value

That doesn't solve the problem of HOW the next objectid value got out of synch with the data in the table.   There appear to be a host of ways to make that happen.