Select to view content in your preferred language

Republishing Survey123 with a new field corrupts the associated hosted feature service and causes negative ObjectIDs (ArcGIS Enterprise)

929
8
04-06-2023 03:10 PM
RyanUthoff
Regular Contributor

I am having a problem updating a couple of my Survey123 forms. The original S123 form was published a few months ago. Today, I tried adding a new text question/field to the survey. When republishing it in S123 Connect, it gets stuck when trying to add the new field to the hosted feature service. The dialog box saying that it is adding the new field just keeps spinning and spinning. After waiting 20+ minutes, I just close out of S123 Connect because it's clear nothing is happening. When I look in server manager, I see the following error message: Error executing tool: Manage Feature Service Job ID: (some random ID): ERROR: Adding field to the table failed. Failed to execute (Manage Feature Service).

Unfortunately, after doing that, it corrupts the associated hosted feature service. We get a generic error message when submitting new S123 forms and we can't create new points in the hosted feature service directly through Portal.

One thing I noticed after that is that there was a record in the hosted feature service with an ObjectID of -220. There is also a corresponding error message in server manager stating that it fails to create a new record because of a duplicate key issue. This is the error message: 0 : ERROR: duplicate key value violates unique constraint "gdb_ct2_54" Detail: Key (objectid, gdb_to_date)=(-220, 9999-12-31 23:59:59) already exists. Where: SQL statement (SQL statement here) PL/pgSQL function nvv_update_54() line 11 at SQL statement.

Note that the record was created after attempting to republish the form. And I am able to delete the record. After deleting, I am able to create a new record, but it gives it an ObjectID of -220 and then I can't create any new records after that until I delete it again.

I was able to replicate this issue with a different, but very similar form/feature service. We're running ArcGIS Enterprise 10.9.1. However, I was able to publish a new S123 form (the same as the one causing issues) and add a new field to it and it was fine.

Does anyone know why this issue is happening, and where/why in the world negative ObjectIDs are being created? That shouldn't even be possible.

8 Replies
ChristopherCounsell
MVP Regular Contributor

Version of Survey123 Connect?

Does this occur in a new, sample survey or just this specific survey you are trying to update? Can you share the xlsform?

Other hosted feature layers in your portal working as expected?

Put in a support case if you haven't already. They'll be able to search s123 and enterprise for similar reports.

0 Kudos
RyanUthoff
Regular Contributor

I'm not sure what version Connect was when it was originally published, but now, it is 3.17.54.

This has happened on two specific surveys that I have tried to update, although I'm afraid it could happen to others. It's one of those situations where I won't know until I try. But, I was able to publish a brand new survey using the same xlsform and it worked fine. I was even able to add a field to it.

I plan on submitting a support case, just wanted to see if anyone else here has experienced that problem.

0 Kudos
ChristopherCounsell
MVP Regular Contributor

Maybe you'll get lucky and someone knows this scenario but I'm not confident. E.g.

https://community.esri.com/t5/arcgis-field-maps-questions/field-maps-new-features-created-with-negat...

This could be a broader issue with your enterprise, a result of data interactions with your service (fme, python etc), an upgrade to 10.9.1  issue, or maybe it's the original xlsform and version being a bit troublesome after upgraded.

I see internal server error at times (e.g. reserved names and removal of conditional formatting). But this is a bit unusual and I'm not sure republishing would result in this.

What I'm saying is, if you can share the xlsforms, we can identify if there is anything unusual with the original surveys used to create the service.

Beyond that, support will have the time and resources to investigate the workflow, environment and related reports to help fast track the issue.

If you have more you can share that could help identify potential issues. Suggest the forms to start. Otherwise some more history on the service and environment. Anything you can share.

0 Kudos
RyanUthoff
Regular Contributor

Yeah, I figured this was a pretty rare issue that not many people would be familiar with, but thought I'd ask. As far as our environment, this is a fresh install of 10.9.1 and the only interaction being done with the service (outside of writing S123 submissions into it) is reading from it. As far as the history of the form, we have been able to add a field to same form/service one time in the past with no issues. Then, it suddenly caused issues this time.

I forgot to say in my previous message, but sharing the forms publicly isn't something I'm too comfortable doing, but I can share them directly with Esri support if I need to (but I appreciate your suggestion and offer at looking at it!).

I'll open a case with Esri and see what they say. In the meantime, I was just curious if anyone else had seen this issue before.

0 Kudos
Andrew_Bartshire
New Contributor II

Hi Ryan, not sure on the negative objectID's but did you pause the service in server manager before modifying the table schema? Our workflow for adding fields to existing forms or services is to pause the service, turn off archiving (if offline), then make the field change, re-enable archiving, then republish/turn on service. I know all deployments are different but the archiving detail was one we learned from trial and error.

RyanUthoff
Regular Contributor

No, we never paused the service in server manager. That has never been necessary for us in the past. Although, that is a good idea. I was wondering if other people were connected to the service, which locked it, causing the S123 update to fail and corrupt it. Just a theory, but I can see a world where that's possible.

0 Kudos
BrennanSmith1
Occasional Contributor

I unfortunately can't offer any help, but wanted to chime in to say we recently had a similar issue with -220 ObjectIDs on a hosted feature layer in Enterprise 10.9.1.  Ours was not related to S123, since we published directly from Arc Pro. One day we were unable to add any new points, and got the same errors you described. We ended up having to completely republish the entire service from scratch.  I think ours was also related to adding a new field to a feature layer that had a joined hosted view derived from it. I had to tweak some JSON to allow for the schema change and probably broke everything in the process.

0 Kudos
RyanUthoff
Regular Contributor

Thanks for the information. That's interesting and something I'll keep in mind. In our case, we're not using any hosted views or anything derived from it, but I'll make sure to remember that for the future.

0 Kudos