Destroy Dataset: Cannot destroy a dataset that has a mapped shape file.

[kcondon]

This is currently in production, v4.7.1

Server log error:

Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: update or delete on table "dvobject" violates foreign key constraint "fk_maplayermetadata_datafile_id" on table "maplayermetadata"
Detail: Key (id)=(3047705) is still referenced from table "maplayermetadata".
Error Code: 0
Call: DELETE FROM DVOBJECT WHERE (ID = ?)
bind => [1 parameter bound]
Query: DeleteObjectQuery([DataFile id:3047705 name:null])
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.processExceptionForCommError(DatabaseAccessor.java:1611)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:898)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:962)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:631)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2002)
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:298)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.deleteObject(DatasourceCallQueryMechanism.java:203)
at org.eclipse.persistence.internal.queries.StatementQueryMechanism.deleteObject(StatementQueryMechanism.java:104)
at org.eclipse.persistence.queries.DeleteObjectQuery.executeDatabaseQuery(DeleteObjectQuery.java:218)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899)
at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:798)
at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
at org.eclipse.persistence.queries.DeleteObjectQuery.executeInUnitOfWorkObjectLevelModifyQuery(DeleteObjectQuery.java:119)
at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85

[pdurbin]

We talked about this on Wednesday during sprint planning and I think we agreed we should fix it with a cascade delete. A related post from TDL just came in: https://groups.google.com/d/msg/dataverse-community/84beQsFuC9w/LZF_dHn-AAAJ

[kcondon]

What about deleting the file from WorldMap?

[pdurbin]

Hmm. Well, they should start with the check command at http://guides.dataverse.org/en/4.7.1/admin/geoconnect-worldmap.html#removing-dead-explore-links I guess.

[pdurbin]

Once we fix this bug, we'd like to destroy this dataset: https://dataverse.harvard.edu/dataset.xhtml?persistentId=doi:10.7910/DVN/T2VIXT

[djbrooke]

Another one, good test case in prod: https://dataverse.harvard.edu/dataset.xhtml?persistentId=doi:10.7910/DVN/4WL81Z

[pdurbin]

I just made pull request #4245 and am moving this to Code Review at https://waffle.io/IQSS/dataverse

[matthew-a-dunlap]

Moving this into develop to take a stab at utilizing cascade / jpa

[matthew-a-dunlap]

In addition to the currently committed code, I have an extra small piece that deletes the map from worldmap itself, as that does not seem to happen in the current flows (I may have missed it tho). The piece of code I have still hits an error in tests tho, so it'll have to wait.

[djbrooke]

Thanks for the continued work on this. I mentioned in Sprint Planning earlier today that it's OK if we move this to the backlog instead of continuing to spend time here. If there's some value here (seems to work for non-restricted files?), we should consider merging what we have instead of trying to track down all cases.

[matthew-a-dunlap]

Below is my summary of the state of this branch. Without a local dev environment for this it is hard to move forward. I've tried following the guides and working with Kevin to point to the dev geoconnect from my local, but the connection fails. I could try setting up geoconnect locally but that seems out of scope. I could also keep sending builds off to Kevin and testing remotely but that's extremely slow and a poor use of time.

The branch as-is could be pulled into development but I don't like how little improvement it causes.

Current status

Works:

• Deletion of dataset triggers delete of mapLayerMetadata and worldmapauth_token for unrestricted datasets.

Broken:

• Deletion of restricted dataset fails because worldmapauth_token is says it still referenced the datafile. Unsure why restriction makes this delete flow different.
• Deletion of map off worldmap during dataset deletion fails with null pointer. This worked at some point on the feature branch, possibly when we used JPA to delete the local map layer. I am unsure why this change would cause problems... is it possible the external delete is started, then the database deletes are called before it completes? Seems like a bit of a black hole, but it still may be possible to get a better log message for the null pointer.
  • When restricting a data file, the map data is deleted off the external server via the same command. But judging by the other restriction issue it may not clean itself up correctly either

Not in scope but broken:

• Deleting an individual data file connected to worldmap does not trigger deletion of the data off worldmap. There is no code yet for this. Restricting the file does trigger this external delete.

[matthew-a-dunlap]

One other option to move forward would be to just rely on manual steps to do this cleanup. This would mean:

For unrestricted datasets

• Manually call a curl(?) command to trigger deletion of the external map

For restricted datasets

• Manually run sql commands to delete the mapLayerMetadata and worldmapauth_token
• Manually call a curl(?) command to trigger deletion of the external map

This does not work for deletion of individual DataFiles because this action can be performed by the end user. We have yet to try fixing this one with code tho, and may be a simple code fix.

[djbrooke]

Thanks for putting a bow on this. Moving to backlog, we can pick it up in the future.

[kcondon]

Need to remove this dataset from prod when fixed: doi:10.7910/DVN/SCYBXS

[djbrooke]

• We suspect this may be easier since our work on the delete user API. There may be some investigation needed.

[jggautier]

@kcondon You wrote in this issue "Need to remove this dataset from prod when fixed: doi:10.7910/DVN/SCYBXS".

Do you mind if I use the destroy endpoint to try to remove it? I just used it to destroy two other test datasets with mapped shape files, but Harvard Dataverse sent me an email about how it "failed to delete the WorldMap layer associated with the restricted file". I don't remember seeing any restricted files in the two datasets, and I'm wondering if I'd get the same message after trying to destroy doi:10.7910/DVN/SCYBXS, which has no restricted files.

[kcondon]

@jggautier I'm not responsible for destroying any datasets. I believe people recorded some problem ones in this ticket so if you are destroying any of those, just note it, thanks.

[jggautier]

Okay, these datasets were mentioned in this thread:

1. https://dataverse.harvard.edu/dataset.xhtml?persistentId=doi:10.7910/DVN/T2VIXT
2. https://dataverse.harvard.edu/dataset.xhtml?persistentId=doi:10.7910/DVN/4WL81Z
3. https://dataverse.harvard.edu/dataset.xhtml?persistentId=doi:10.7910/DVN/SCYBXS

The first was already destroyed. I just destroyed the other two.

Neither had restricted files but each time I used the endpoint, an email like the one below was still sent to my superuser account email address:

Subject line: Harvard Dataverse: Failed to delete WorldMap layer

Hello,
We failed to delete the WorldMap layer associated with the restricted file boston_trash_bytract.tab, and any related data that may still be publicly available on the WorldMap site. Please try again, or contact WorldMap and/or Dataverse support. (Dataset: https://dataverse.harvard.edu/dataset.xhtml?persistentId=doi:10.7910/DVN/4WL81Z)

You may contact us for support at support@dataverse.harvard.edu.

Thank you,
Harvard Dataverse Support

I guess I'm just including this here "for the record" since it doesn't matter much: these are test files so we don't need to make an effort to delete them from the internet (like we would if they contained PII) and the WorldMap integration is going away eventually.