Duplicate attachment block publishing to Google Spreadsheets

[ggalmazor]

Software and hardware versions

Aggregate v1.4.15

Problem description

When a form with media fields has a duplicated row on its media tables, a Google Spreadsheet publisher will stop on that row and won't continue with the rest of rows that are OK

Steps to reproduce the problem

• Upload a form with media fields
• Send some submissions with Collect
• Duplicate a row on xyz_bn table for one of the submissions
• Create a publisher to Google Spreadsheets

You should see that the row with dupes doesn't get published. Any form with posterior submission dates will neither be published.

Expected behavior

Well, this depends.

• We could ignore the whole submission or
• We could take only the latest dupe row and ignore the rest duped rows

Other information

GAE Stacktrace:

org.opendatakit.aggregate.task.UploadSubmissionsWorkerImpl uploadAllSubmissions: org.opendatakit.aggregate.exception.ODKExternalServiceException: org.opendatakit.common.datamodel.ODKEnumeratedElementException: Attachment errors: (UploadSubmissionsWorkerImpl.java:208)
SELECT * FROM S4W_NEPAL_V1_01_WATER_LEVEL_IMAGE_BN WHERE _TOP_LEVEL_AURI = uuid:7858e223-2c89-4382-9809-8fc0914c67d3 AND _PARENT_AURI = uuid:7858e223-2c89-4382-9809-8fc0914c67d3 is missing an attachment instance OR has extra copies.
	at org.opendatakit.aggregate.externalservice.GoogleSpreadsheet.insertData(GoogleSpreadsheet.java:654)
	at org.opendatakit.aggregate.externalservice.AbstractExternalService.sendSubmission(AbstractExternalService.java:138)
	at org.opendatakit.aggregate.task.UploadSubmissionsWorkerImpl.sendSubmissions(UploadSubmissionsWorkerImpl.java:322)
	at org.opendatakit.aggregate.task.UploadSubmissionsWorkerImpl.uploadSubmissions(UploadSubmissionsWorkerImpl.java:282)
	at org.opendatakit.aggregate.task.UploadSubmissionsWorkerImpl.uploadAllSubmissions(UploadSubmissionsWorkerImpl.java:196)
	at org.opendatakit.aggregate.task.gae.servlet.UploadSubmissionsTaskServlet.doGet(UploadSubmissionsTaskServlet.java:109)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1166)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317)
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:114)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
	at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
	at org.opendatakit.common.security.spring.SecurityContextHolderAwareAuthPreservingRequestFilter.doFilter(SecurityContextHolderAwareAuthPreservingRequestFilter.java:66)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
	at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
	at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
	at org.springframework.security.web.authentication.www.DigestAuthenticationFilter.doFilter(DigestAuthenticationFilter.java:126)
	at org.opendatakit.common.security.spring.DigestAuthenticationFilter.doFilter(DigestAuthenticationFilter.java:40)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
	at org.opendatakit.common.security.spring.OutOfBandUserFilter.doFilter(OutOfBandUserFilter.java:105)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
	at org.opendatakit.common.security.spring.Oauth2ResourceFilter.doFilter(Oauth2ResourceFilter.java:352)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
	at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
	at org.springframework.security.web.access.channel.ChannelProcessingFilter.doFilter(ChannelProcessingFilter.java:157)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
	at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214)
	at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
	at com.google.apphosting.utils.servlet.ParseBlobUploadFilter.doFilter(ParseBlobUploadFilter.java:125)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
	at com.google.apphosting.runtime.jetty.SaveSessionFilter.doFilter(SaveSessionFilter.java:37)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
	at com.google.apphosting.utils.servlet.JdbcMySqlConnectionCleanupFilter.doFilter(JdbcMySqlConnectionCleanupFilter.java:60)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
	at com.google.apphosting.utils.servlet.TransactionCleanupFilter.doFilter(TransactionCleanupFilter.java:48)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
	at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:388)
	at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
	at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
	at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
	at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418)
	at com.google.apphosting.runtime.jetty.AppVersionHandlerMap.handle(AppVersionHandlerMap.java:257)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.Server.handle(Server.java:326)
	at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
	at org.mortbay.jetty.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:923)
	at com.google.apphosting.runtime.jetty.RpcRequestParser.parseAvailable(RpcRequestParser.java:76)
	at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
	at com.google.apphosting.runtime.jetty.JettyServletEngineAdapter.serviceRequest(JettyServletEngineAdapter.java:146)
	at com.google.apphosting.runtime.JavaRuntime$RequestRunnable.dispatchServletRequest(JavaRuntime.java:686)
	at com.google.apphosting.runtime.JavaRuntime$RequestRunnable.dispatchRequest(JavaRuntime.java:648)
	at com.google.apphosting.runtime.JavaRuntime$RequestRunnable.run(JavaRuntime.java:618)
	at com.google.tracing.TraceContext$TraceContextRunnable.runInContext(TraceContext.java:455)
	at com.google.tracing.TraceContext$TraceContextRunnable$1.run(TraceContext.java:462)
	at com.google.tracing.CurrentContext.runInContext(CurrentContext.java:320)
	at com.google.tracing.TraceContext$AbstractTraceContextCallback.runInInheritedContextNoUnref(TraceContext.java:321)
	at com.google.tracing.TraceContext$AbstractTraceContextCallback.runInInheritedContext(TraceContext.java:313)
	at com.google.tracing.TraceContext$TraceContextRunnable.run(TraceContext.java:459)
	at com.google.apphosting.runtime.ThreadGroupPool$PoolEntry.run(ThreadGroupPool.java:274)
	at java.lang.Thread.run(Thread.java:745)
Caused by: org.opendatakit.common.datamodel.ODKEnumeratedElementException: Attachment errors:
SELECT * FROM S4W_NEPAL_V1_01_WATER_LEVEL_IMAGE_BN WHERE _TOP_LEVEL_AURI = uuid:7858e223-2c89-4382-9809-8fc0914c67d3 AND _PARENT_AURI = uuid:7858e223-2c89-4382-9809-8fc0914c67d3 is missing an attachment instance OR has extra copies.
	at org.opendatakit.common.datamodel.BinaryContentManipulator.updateAttachments(BinaryContentManipulator.java:607)
	at org.opendatakit.common.datamodel.BinaryContentManipulator.getAttachmentCount(BinaryContentManipulator.java:242)
	at org.opendatakit.aggregate.submission.type.BlobSubmissionType.getAttachmentCount(BlobSubmissionType.java:58)
	at org.opendatakit.aggregate.format.element.LinkElementFormatter.formatBinary(LinkElementFormatter.java:80)
	at org.opendatakit.aggregate.submission.type.BlobSubmissionType.formatValue(BlobSubmissionType.java:179)
	at org.opendatakit.aggregate.submission.SubmissionSet.populateFormattedValuesInRow(SubmissionSet.java:785)
	at org.opendatakit.aggregate.submission.SubmissionSet.getFormattedValuesAsRow(SubmissionSet.java:813)
	at org.opendatakit.aggregate.externalservice.GoogleSpreadsheet.createAppendCellsRequest(GoogleSpreadsheet.java:681)
	at org.opendatakit.aggregate.externalservice.GoogleSpreadsheet.insertData(GoogleSpreadsheet.java:616)
	... 73 more

[yanokwa]

@ggalmazor And to be clear you have been able to reproduce this outside the place where the problem was found? Do you have a sense of how the initial duplicate occurred?

[ggalmazor]

I haven't reproduced it yet. I need to make a testbench for this.

I'm guessing that writing on the DataStore timed out (which doesn't mean the data hasn't been committed) and Collect retried sending the image, but we can't be 100% sure...

[yanokwa]

I guess I'm not so worried about how it happened, but let's see if we can at least reproduce the state of the DB where the problem exists so we have some evidence when it's fixed.

My bias as far as expected behavior is to return the latest dupe record. Feels like that's better for users.