Validate Database Runtime Error on 3.12

[mahithedula]

Occasionally when running validate database I will get a Runtime Error on Rule 3.12 even on gdbs that previously succeeded and were Level 3 compliant.

On closer look the problem seems to come from rule function 3_12 and the ds.ExecuteSQL which executes SELECT commands on _IDs that don't exist like on feature dataset GeologicMap or even tables like GeoMaterialDict (which from my understanding shouldn't have a GeoMaterialDict_ID, I am new to GEMS so I could be misinterpreting something).

ExecuteSQL seems to be raising exceptions sometimes rather than returning None.

I have worked around it with this try statement. But I think there is a better solution than that.

try:
    res = ds.ExecuteSQL(sql)
except RuntimeError:
    continue

[ethoms-usgs]

This issue has characteristics of a problem I have seen a few times that I have not been able to solve. If you look at the traceback, none of the line numbers, except the last one in the third-party library, correspond to lines that are actually running code; they are items in a list or comments or the closing round bracket. Also, except for line 994, the lines shown are not the same as the lines in the current script. As far as I can figure out, this mis-match between the error traceback and the current code usually happens when the source code is changed while the code is being run. Since I don't think that is what is really happening with users of the toolbox, it might be because Arc continues to run a cached version of the code even after the toolbox has been replaced.

Please try these steps and let me know

1. Close ArcGIS Pro
2. Delete the old toolbox and replace with the new one; don't just copy the contents of the downloaded toolbox folder into the same location as the old toolbox.
3. Open a new map and add only the items you are going to work on.
4. Run the tool from the toolbox, not from the Geoprocessing History

In fact, if you could run the tool after each of these steps, that could be helpful too.
All this is not to say that there isn't a real error, but I don't trust the current traceback to be pointing to the real cause.

[mahithedula]

I didn't follow your steps exactly since I am running this from a script but I did the following to make sure it wasn't running the cached version

1. Closed ArcGISPro (I am on 3.3.2)
2. Downloaded the latest release and put in a dummy folder.
3. Reopened ArcGISPro and put this path when importing the toolbox and ran my script

This initially succeeded without metadata then failed with metadata with the same runtime error on 3.12 including the weird line numbers. (I don't think the metadata caused the issue)

I then added the dummy folder toolbox into my project and ran it manually without metadata through the Catalog Pane it failed initially with the same error (left). I then closed ArcGIS Pro and reopened and ran it again and it succeeded (right). Below are those results.

There is probably something weird going on because I am running it on the same gdb (I don't think validate database edits the gdb) with the same parameters and environment on the same toolbox code and getting different results.

Putting the try statement on the ExecuteSQL line has consistently worked for me so far but it is probably not addressing the root issue.

[ethoms-usgs]

Ah! I apologize. Despite the odd traceback reporting, I think you are right about def rule3_12.
Try the new version by downloading from the green Code button on the main page, not the releases. I will publish a new release if this fix works.

[mahithedula]

Thanks. It fixes the issue with GeologicMap and GeoMaterialDict. But it does fail on geologyAnno, which is a nonstandard annotation feature class that our client requests.

I don't know if validate database should be able to handle that or if it's our responsibility to remove annotations before running validate database and add them back.

On a separate note, if rule3_12 was the issue I don't understand how I was getting different results (i.e seeing the same gdb fail then succeed on the same code). It should have failed every time.

[ethoms-usgs]

Yes! I am surprised I didn't get notice of this error earlier.
Try again. The function now explicitly checks first for fields associated with an item, so things like feature datasets and relationship classes are excluded, and then checks that a TableName_ID field exists before trying to collect values.
Again, download from the green code button.

[mahithedula]

Thank you validate database does work now!

Although DMUtoDocx raises the same error described in issue #111. If that change can be made to the master branch that would be really helpful.

[ethoms-usgs]

Good to hear and thanks for the reminder. Just pushed out a new release.