Re: [Treebase-devel] Deleting Childless treeblocks

[Hilmar L. <hl...@ne...>]

Bill -

two questions first: 1) Do you still have the original data file that  
would clearly prove that these treeblocks must be spurious? and 2) why  
would we not delete only the spurious treeblocks and sub_treeblocks,  
rather than the entire study? Are you suspicious that the entire study  
is corrupt in the way it is represented in the database?

Note that your join below won't work the way I think you intend it to  
work. Either put the outer join last, or parenthesize the serious of  
inner joins that you join with.

If the WHERE clause below were really correct (i.e., only hit  
treeblock records that do not have any phylotree dependent on them)  
then you should not get any foreign key violation, should you? Or are  
there other tables that have a foreign key to treeblock, and which  
have rows pointing to the so-called orphan treeblocks (which might not  
be so orphan after all then?).

	-hilmar

On Jan 8, 2010, at 11:15 AM, William Piel wrote:

>
> One of the unit tests that is giving Youjun trouble is one that  
> checks for orphans (or in this case "childless" records) in the  
> following chain of tables:
>
> study --- submission --- sub_treeblock --- treeblock >-- phylotree
>
> Frankly, I'm not clear why we have sub_* tables to begin with, but  
> it turns out that there are about 4,500 orphaned treeblocks (i.e.  
> treeblock records that lack any related phylotree records) that each  
> have a sub_treeblock id, and almost all of them belong to a  
> submission that belongs to a study with study_id 22. So for some  
> strange reason, study_id 22 has thousands of sub_treeblock records  
> that each have a treeblock record, yet they lack phylotrees. Looks  
> to me like these were created as part of Mark Jason's last migration  
> effort -- probably purely spurious.
>
> I tried deleting these simply by deleting study_id 22, but after  
> many hours that resulted in the "data access failure" (below). Don't  
> know if anyone has some ideas re. why they doesn't work.
>
> The other approach is to run a SQL query like so:
>
> DELETE FROM treeblock WHERE treeblock_id IN (
>   SELECT tb.treeblock_id
>   FROM phylotree pt RIGHT JOIN treeblock tb ON (pt.treeblock_id =  
> tb.treeblock_id)
>   JOIN sub_treeblock stb ON (tb.treeblock_id = stb.treeblock_id)
>   JOIN submission sub ON (stb.submission_id = sub.submission_id)
>   JOIN study st ON (sub.study_id = st.study_id)
>   WHERE pt.phylotree_id IS NULL
>   AND st.study_id = 22
> )
>
> ... but the trouble is that foreign key constraints don't allow  
> these to be deleted (and postgres does not support CASCADE DELETE  
> unless the tables are altered to do this automatically). One way  
> around this would be to first build a list of orphaned  
> sub_treeblock, treeblock, etc, records, and then delete them from  
> the list of IDs flowing with the cascade.
>
> Anyway... I wanted to touch base with everyone in case there's an  
> easy way to do this the proper way -- i.e. to have our java code &  
> hibernate delete study_id 22 without running into the  
> "DataIntegrityViolationException" below.
>
> bp
>
>
>
>
> Data Access Failure
>
> could not delete:  
> [org.cipres.treebase.domain.taxon.TaxonLabel#67521]; nested  
> exception is org.hibernate.exception.ConstraintViolationException:  
> could not delete: [org.cipres.treebase.domain.taxon.TaxonLabel#67521]
>
> org.springframework.dao.DataIntegrityViolationException: could not  
> delete: [org.cipres.treebase.domain.taxon.TaxonLabel#67521]; nested  
> exception is org.hibernate.exception.ConstraintViolationException:  
> could not delete:  
> [org.cipres.treebase.domain.taxon.TaxonLabel#67521] Caused by:  
> org.hibernate.exception.ConstraintViolationException: could not  
> delete: [org.cipres.treebase.domain.taxon.TaxonLabel#67521] at  
> org 
> .hibernate 
> .exception.SQLStateConverter.convert(SQLStateConverter.java:71) at  
> org 
> .hibernate 
> .exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)  
> at  
> org 
> .hibernate 
> .persister 
> .entity.AbstractEntityPersister.delete(AbstractEntityPersister.java: 
> 2546) at  
> org 
> .hibernate 
> .persister 
> .entity.AbstractEntityPersister.delete(AbstractEntityPersister.java: 
> 2702) at  
> org 
> .hibernate.action.EntityDeleteAction.execute(EntityDeleteAction.java: 
> 77) at org.hibernate.engine.ActionQueue.execute(ActionQueue.java: 
> 279) at  
> org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java: 
> 263) at  
> org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java: 
> 172) at  
> org 
> .hibernate 
> .event 
> .def 
> .AbstractFlushingEventListener 
> .performExecutions(AbstractFlushingEventListener.java:298) at  
> org 
> .hibernate 
> .event 
> .def 
> .DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java: 
> 27) at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)  
> at org.springframework.orm.hibernate3.HibernateTemplate 
> $27.doInHibernate(HibernateTemplate.java:811) at  
> org 
> .springframework 
> .orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java: 
> 372) at  
> org 
> .springframework 
> .orm.hibernate3.HibernateTemplate.flush(HibernateTemplate.java:809)  
> at org.cipres.treebase.dao.AbstractDAO.flush(AbstractDAO.java:158)  
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at  
> sun 
> .reflect 
> .NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)  
> at  
> sun 
> .reflect 
> .DelegatingMethodAccessorImpl 
> .invoke(DelegatingMethodAccessorImpl.java:43) at  
> java.lang.reflect.Method.invoke(Method.java:616) at  
> org 
> .springframework 
> .aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java: 
> 304) at  
> org 
> .springframework 
> .aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java: 
> 198) at $Proxy50.flush(Unknown Source) at  
> org 
> .cipres 
> .treebase 
> .service 
> .study 
> .SubmissionServiceImpl.deleteSubmission(SubmissionServiceImpl.java: 
> 428) at  
> org 
> .cipres 
> .treebase 
> .service 
> .study 
> .SubmissionServiceImpl.deleteSubmission(SubmissionServiceImpl.java: 
> 900) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  
> at  
> sun 
> .reflect 
> .NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)  
> at  
> sun 
> .reflect 
> .DelegatingMethodAccessorImpl 
> .invoke(DelegatingMethodAccessorImpl.java:43) at  
> java.lang.reflect.Method.invoke(Method.java:616) at  
> org 
> .springframework 
> .aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java: 
> 304) at  
> org 
> .springframework 
> .aop 
> .framework 
> .ReflectiveMethodInvocation 
> .invokeJoinpoint(ReflectiveMethodInvocation.java:182) at  
> org 
> .springframework 
> .aop 
> .framework 
> .ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java: 
> 149) at  
> org 
> .springframework 
> .transaction 
> .interceptor 
> .TransactionInterceptor.invoke(TransactionInterceptor.java:106) at  
> org 
> .springframework 
> .aop 
> .framework 
> .ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java: 
> 171) at  
> org 
> .springframework 
> .aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java: 
> 204) at $Proxy80.deleteSubmission(Unknown Source) at  
> org 
> .cipres 
> .treebase 
> .web 
> .controllers 
> .DeleteStudyController.onSubmit(DeleteStudyController.java:71) at  
> org.springframework.web.servlet.mvc.SimpleFormController.processFormSubmission 
> (SimpleFormController.java:267) at org.springframework.web.servlet.mvc.CancellableFormController.processFormSubmission 
> (CancellableFormController.java:140) at org.springframework.web.servlet.mvc.AbstractFormController.handleRequestInternal 
> (AbstractFormController.java:265) at org.springframework.web.servlet.mvc.AbstractController.handleRequest 
> (AbstractController.java:153) at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle 
> (SimpleControllerHandlerAdapter.java:48) at org.springframework.web.servlet.DispatcherServlet.doDispatch 
> (DispatcherServlet.java:858) at org.springframework.web.servlet.DispatcherServlet.doService 
> (DispatcherServlet.java:792) at org.springframework.web.servlet.FrameworkServlet.processRequest 
> (FrameworkServlet.java:476) at org.springframework.web.servlet.FrameworkServlet.doPost 
> (FrameworkServlet.java:441) at  
> javax.servlet.http.HttpServlet.service(HttpServlet.java:710) at  
> javax.servlet.http.HttpServlet.service(HttpServlet.java:803) at  
> org 
> .apache 
> .catalina 
> .core 
> .ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java: 
> 269) at  
> org 
> .apache 
> .catalina 
> .core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java: 
> 188) at  
> org 
> .displaytag 
> .filter.ResponseOverrideFilter.doFilter(ResponseOverrideFilter.java: 
> 125) at  
> org 
> .apache 
> .catalina 
> .core 
> .ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java: 
> 215) at  
> org 
> .apache 
> .catalina 
> .core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java: 
> 188) at  
> org 
> .springframework 
> .orm 
> .hibernate3 
> .support 
> .OpenSessionInViewFilter 
> .doFilterInternal(OpenSessionInViewFilter.java:198) at  
> org 
> .springframework 
> .web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java: 
> 75) at  
> org 
> .apache 
> .catalina 
> .core 
> .ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java: 
> 215) at  
> org 
> .apache 
> .catalina 
> .core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java: 
> 188) at  
> com 
> .opensymphony 
> .module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:119) at  
> com 
> .opensymphony 
> .module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:55) at  
> org 
> .apache 
> .catalina 
> .core 
> .ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java: 
> 215) at  
> org 
> .apache 
> .catalina 
> .core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java: 
> 188) at org.acegisecurity.util.FilterChainProxy 
> $VirtualFilterChain.doFilter(FilterChainProxy.java:264) at  
> org 
> .acegisecurity 
> .intercept 
> .web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java: 
> 107) at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter 
> (FilterSecurityInterceptor.java:72) at  
> org.acegisecurity.util.FilterChainProxy 
> $VirtualFilterChain.doFilter(FilterChainProxy.java:274) at  
> org 
> .acegisecurity 
> .ui 
> .ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java: 
> 110) at org.acegisecurity.util.FilterChainProxy 
> $VirtualFilterChain.doFilter(FilterChainProxy.java:274) at  
> org 
> .acegisecurity 
> .wrapper 
> .SecurityContextHolderAwareRequestFilter 
> .doFilter(SecurityContextHolderAwareRequestFilter.java:81) at  
> org.acegisecurity.util.FilterChainProxy 
> $VirtualFilterChain.doFilter(FilterChainProxy.java:274) at  
> org 
> .acegisecurity 
> .ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java: 
> 217) at org.acegisecurity.util.FilterChainProxy 
> $VirtualFilterChain.doFilter(FilterChainProxy.java:274) at  
> org 
> .acegisecurity 
> .context 
> .HttpSessionContextIntegrationFilter 
> .doFilter(HttpSessionContextIntegrationFilter.java:191) at  
> org.acegisecurity.util.FilterChainProxy 
> $VirtualFilterChain.doFilter(FilterChainProxy.java:274) at  
> org 
> .acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java: 
> 148) at  
> org 
> .acegisecurity 
> .util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:90) at  
> org 
> .apache 
> .catalina 
> .core 
> .ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java: 
> 215) at  
> org 
> .apache 
> .catalina 
> .core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java: 
> 188) at  
> org 
> .apache 
> .catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java: 
> 210) at  
> org 
> .apache 
> .catalina.core.StandardContextValve.invoke(StandardContextValve.java: 
> 172) at  
> org 
> .apache 
> .catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)  
> at  
> org 
> .apache 
> .catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)  
> at  
> org 
> .apache 
> .catalina.core.StandardEngineValve.invoke(StandardEngineValve.java: 
> 108) at  
> org 
> .apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java: 
> 151) at  
> org 
> .apache.coyote.http11.Http11Processor.process(Http11Processor.java: 
> 870) at org.apache.coyote.http11.Http11BaseProtocol 
> $Http11ConnectionHandler.processConnection(Http11BaseProtocol.java: 
> 665) at  
> org 
> .apache 
> .tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java: 
> 528) at  
> org 
> .apache 
> .tomcat 
> .util 
> .net 
> .LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java: 
> 81) at org.apache.tomcat.util.threads.ThreadPool 
> $ControlRunnable.run(ThreadPool.java:685) at  
> java.lang.Thread.run(Thread.java:636) Caused by:  
> java.sql.BatchUpdateException: Batch entry 13 delete from TAXONLABEL  
> where TAXONLABEL_ID=71834 and VERSION=2 was aborted. Call  
> getNextException to see the cause. at  
> org.postgresql.jdbc2.AbstractJdbc2Statement 
> $BatchResultHandler.handleError(AbstractJdbc2Statement.java:2537) at  
> org 
> .postgresql 
> .core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java: 
> 1328) at  
> org 
> .postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java: 
> 351) at  
> org 
> .postgresql 
> .jdbc2 
> .AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java: 
> 2674) at  
> com 
> .mchange 
> .v2 
> .c3p0 
> .impl 
> .NewProxyPreparedStatement 
> .executeBatch(NewProxyPreparedStatement.java:1723) at  
> org 
> .hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java: 
> 48) at  
> org.hibernate.jdbc.BatchingBatcher.addToBatch(BatchingBatcher.java: 
> 34) at  
> org 
> .hibernate 
> .persister 
> .entity.AbstractEntityPersister.delete(AbstractEntityPersister.java: 
> 2525) ... 85 more « Back
> ------------------------------------------------------------------------------
> This SF.Net email is sponsored by the Verizon Developer Community
> Take advantage of Verizon's best-in-class app development support
> A streamlined, 14 day to market process makes app distribution fast  
> and easy
> Join now and get one step closer to millions of Verizon customers
> http://p.sf.net/sfu/verizon-dev2dev  
> _______________________________________________
> Treebase-devel mailing list
> Tre...@li...
> https://lists.sourceforge.net/lists/listinfo/treebase-devel

-- 
===========================================================
: Hilmar Lapp  -:- Durham, NC -:- informatics.nescent.org :
===========================================================