Unable to delete files after Migration from 3.6

[Bikramjit Singh]

We at Scholars Portal have recently upgraded Dataverse 3.6 to 4.5. We have encountered an issue where users are unable to delete files from migrated unpublished datasets. Checked the logs and its database exception due to foreign key constraint on datafilecategory table and is not happening for new files added in v4. 

Caused by: 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_datafilecategory_dataset_id" on table "datafilecategory"
  Detail: Key (id)=(38325) is still referenced from table "datafilecategory".
Error Code: 0
Call: DELETE FROM DVOBJECT WHERE (ID = ?)
 bind => [1 parameter bound]
Query: DeleteObjectQuery([DataFile id:38325 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)
 at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
 at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804)
 at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1786)
 at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1737)
 at org.eclipse.persistence.internal.sessions.CommitManager.deleteAllObjects(CommitManager.java:336)
 at org.eclipse.persistence.internal.sessions.CommitManager.deleteAllObjects(CommitManager.java:290)
 at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1444)
 at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithPreBuiltChangeSet(UnitOfWorkImpl.java:1587)
 at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.writeChanges(RepeatableWriteUnitOfWork.java:452)
 at org.eclipse.persistence.internal.jpa.EntityManagerImpl.flush(EntityManagerImpl.java:863)
 ... 177 more
Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "dvobject" violates foreign key constraint "fk_datafilecategory_dataset_id" on table "datafilecategory"
  Detail: Key (id)=(38325) is still referenced from table "datafilecategory".
 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
 at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
 at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173)
 at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:618)
 at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:468)
 at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:414)
 at sun.reflect.GeneratedMethodAccessor1358.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:498)
 at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(AbstractJdbc23PooledConnection.java:453)
 at com.sun.proxy.$Proxy227.executeUpdate(Unknown Source)
 at com.sun.gjc.spi.base.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:125)
 at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:890)
 ... 201 more



Regards,

Bikramjit Singh

Systems support specialist

OCUL Scholars Portal

[Philip Durbin]

Hi Bikramjit,

Thanks for opening https://help.hmdc.harvard.edu/Ticket/Display.html?id=242947 by emailing sup...@dataverse.org

I looked through the ticket briefly and it seems like you are getting some help. Once it's resolved, please feel free to summarize for the list.

Also, next time you might want to target the "Dataverse Migration Working Group" mailing list. As https://groups.google.com/forum/#!forum/dataverse-migration-wg says, "We've created this working group to assist with migrating Dataverse installations from v3.x to v4.x. This is a complex process that can vary based on local content. All are welcome and hopefully we can help each other address issues and improve the process."

Thanks,

Phil

--
You received this message because you are subscribed to the Google Groups "Dataverse Users Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dataverse-community+unsubscribe...@googlegroups.com.
To post to this group, send email to dataverse-community@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dataverse-community/27131279-cc72-4b8e-99bb-204ad6707937%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Philip Durbin
Software Developer for http://dataverse.org
http://www.iq.harvard.edu/people/philip-durbin

[Bikramjit Singh]

My apologies, I didn't see much activity in Migration group so thought to post here. 

and Yes, Gustavo is helping me with issue. I will brief this thread once get the solution.

Regards,

Bikram

[Bikramjit Singh]

This issue has been resolved with help of Gustavo. It was due to small bug in migration scripts. 

[Durand, Gustavo]

Some more detail for the community:

the issue is in the files_source_ script. We will be correcting this and merging this to dev soon.

In the meanwhile, if you do a migration before we complete this OR if you've done a migration, you should check to see the current state of your installation.

Run the following select script:

SELECT count(*) FROM datafilecategory where dataset_id in (SELECT id FROM dvobject where dtype='DataFile');

If it returns anything other than 0, then run the follwoing update script*:

update datafilecategory set dataset_id=dvo.owner_id

from dvobject dvo

where datafilecategory.dataset_id=dvo.id

and dvo.dtype='DataFile';

* technically you can run the update without the select, it's just good practice to get the number from the select, then run the update in a transaction and confirm the same number of rows are updated.

Thanks, and please let me know if you have any questions about this,

Gustavo

On Thu, Oct 27, 2016 at 12:42 PM, Bikramjit Singh <bic...@gmail.com> wrote:

This issue has been resolved with help of Gustavo. It was due to small bug in migration scripts. 

On Wednesday, October 26, 2016 at 4:04:37 PM UTC-4, Bikramjit Singh wrote:

--
You received this message because you are subscribed to the Google Groups "Dataverse Users Community" group.

To unsubscribe from this group and stop receiving emails from it, send an email to dataverse-community+unsub...@googlegroups.com.

To post to this group, send email to dataverse-community@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/dataverse-community/d48ec977-b97d-4255-aa71-63ea549e9ff3%40googlegroups.com.