search

phac-nml / irida

Canada’s Integrated Rapid Infectious Disease Analysis Platform for Genomic Epidemiology
https://irida.ca
Apache License 2.0
40 stars 31 forks source link

Exception when deleting analyses marked with "Error" state #22

Closed glwinsor closed 6 years ago

glwinsor commented 6 years ago

While configuring the SFU instance of IRIDA, we experienced a few exceptions in the pipeline that appear to be resolved by requesting more memory for each job.

As a result, there are some old analyses that remain in the "Error" state that I wanted to clean up by deleting them using the trash can on the right. When I click on them, I get a message on the front end saying "An unexpected exception occurred while deleting your analysis".

I checked the catalina.out for more information and I found an exception that suggests a foreign key problem with MySQL.

Here is what I see: 29 Mar 2018 12:50:28,993 DEBUG ca.corefacility.bioinformatics.irida.service.impl.analysis.submission.AnalysisSubmissionServiceImpl:302 - Not cleaning submission [203] when deleting, it's already cleaned. 29 Mar 2018 12:50:29,006 ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper:146 - Cannot delete or update a parent row: a foreign key constraint fails (irida_irida.job_error, CONSTRAINT FK_JOB_ERROR_ANALYSIS_SUBMISSION FOREIGN KEY (analysis_submission_id) REFERENCES analysis_submission (id)) 29 Mar 2018 12:50:29,007 ERROR ca.corefacility.bioinformatics.irida.ria.web.errors.ExceptionHandlerController:117 - could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:225) at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:521) at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:761) at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:730) at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:485) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:291) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:654) at ca.corefacility.bioinformatics.irida.service.impl.analysis.submission.AnalysisSubmissionServiceImpl$$EnhancerBySpringCGLIB$$f3de2a90.delete(<generated>) at ca.corefacility.bioinformatics.irida.ria.web.analysis.AnalysisController.deleteAjaxAnalysisSubmission(AnalysisController.java:543) at ca.corefacility.bioinformatics.irida.ria.web.analysis.AnalysisController$$FastClassBySpringCGLIB$$1659c59e.invoke(<generated>) at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:650) at ca.corefacility.bioinformatics.irida.ria.web.analysis.AnalysisController$$EnhancerBySpringCGLIB$$50b74429.deleteAjaxAnalysisSubmission(<generated>) at sun.reflect.GeneratedMethodAccessor785.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:222) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:814) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:737) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) at org.springframework.web.servlet.FrameworkServlet.doDelete(FrameworkServlet.java:894) at javax.servlet.http.HttpServlet.service(HttpServlet.java:656) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) at javax.servlet.http.HttpServlet.service(HttpServlet.java:731) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at ca.corefacility.bioinformatics.irida.web.filter.SlashFilter.doFilter(SlashFilter.java:71) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at ca.corefacility.bioinformatics.irida.web.filter.HttpHeadFilter.doFilter(HttpHeadFilter.java:56) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at ca.corefacility.bioinformatics.irida.ria.config.filters.SessionFilter.doFilter(SessionFilter.java:35) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:154) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:199) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:110) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:50) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192) at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:962) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445) at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:190) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748) Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:72) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:211) at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:62) at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3400) at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3630) at org.hibernate.action.internal.EntityDeleteAction.execute(EntityDeleteAction.java:114) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:465) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:351) at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350) at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56) at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1258) at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:425) at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101) at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:177) at org.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:77) at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:517) ... 87 more Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (irida_irida.job_error, CONSTRAINT FK_JOB_ERROR_ANALYSIS_SUBMISSION FOREIGN KEY (analysis_submission_id) REFERENCES analysis_submission (id)) at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:408) at com.mysql.jdbc.Util.getInstance(Util.java:383) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1049) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4226) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2840) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2262) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2246) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208) ... 101 more

dfornika commented 6 years ago

Link to where that first log statement is generated:

https://github.com/phac-nml/irida/blob/8fd5ff3420074bef506549f004cb0b6d36ce9c02/src/main/java/ca/corefacility/bioinformatics/irida/service/impl/analysis/submission/AnalysisSubmissionServiceImpl.java#L280-L306

peterk87 commented 6 years ago

Looks like onDelete="CASCADE" might need to be set on the job_error table analysis_submission_id foreign key constraint:

https://github.com/phac-nml/irida/blob/development/src/main/resources/ca/corefacility/bioinformatics/irida/database/changesets/0.20.0/add-job-error-table.xml#L39

apetkau commented 6 years ago

Thanks @peterk87, that makes sense to me. Could you test out a fix?

peterk87 commented 6 years ago

@apetkau Altering the foreign key constraint with the SQL below allows deletion of an analysis submission in the error state with job error info: 

ALTER TABLE job_error DROP FOREIGN KEY FK_jo1shcn7ghr64lhqs7jv8dx2y;
ALTER TABLE job_error
ADD CONSTRAINT FK_jo1shcn7ghr64lhqs7jv8dx2y
FOREIGN KEY (analysis_submission_id) REFERENCES analysis_submission (id) ON DELETE CASCADE;

I can add a Liquibase migration but I'm not sure how to alter/drop the FK if the name is random (I thought I was assigning it with the foreignKeyName attribute).

Also, should I change CascadeType to ALL?

https://github.com/phac-nml/irida/blob/development/src/main/java/ca/corefacility/bioinformatics/irida/model/workflow/analysis/JobError.java#L132

apetkau commented 6 years ago

Thanks @peterk87. For dropping the FK, I think you use the name from https://github.com/phac-nml/irida/blob/0.20.0/src/main/resources/ca/corefacility/bioinformatics/irida/database/changesets/0.20.0/pipeline-metadata-entry.xml#L17. That should work. I don't know why it shows up as random.

Changing CascadeType to ALL makes sense to me, but we should check with @tom114 as he knows more about hibernate/Java persistence.

apetkau commented 6 years ago

Actually, I don't think you want to drop the FK, just change the cascade type. The foreign key should still remain.

tom114 commented 6 years ago

Yeah I think you could change the cascade type to ALL in JobError.java https://github.com/phac-nml/irida/blob/b9feb239ec1c126dc5d00da5ae7f693d812ca9fc/src/main/java/ca/corefacility/bioinformatics/irida/model/workflow/analysis/JobError.java#L132. Most of the cascading happens in Hibernate itself rather than using the FK cascade. You can check if changing the cascade type in JobError.java changes anything in the schema though.

peterk87 commented 6 years ago

It looks like in order to change what happens on delete, the FK constraint needs to be dropped and added back.

Here's some SQL that seems to work for dropping the FK constraint if the name of the constraint is unknown (it might only be a randomly assigned name in my dev DB?) and adding it back with the right name and cascade on delete:

SET @CN = (SELECT DISTINCT constraint_name
FROM information_schema.key_column_usage
WHERE table_name = 'job_error'
  AND referenced_table_name = 'analysis_submission');

SET @sql = 'ALTER TABLE job_error DROP FOREIGN KEY @CN';
SET @sql = replace(@sql, '@CN', @CN);

PREPARE alterTable from @sql;
EXECUTE alterTable;

ALTER TABLE job_error
ADD CONSTRAINT FK_JOB_ERROR_ANALYSIS_SUBMISSION
FOREIGN KEY (analysis_submission_id) REFERENCES analysis_submission (id) ON DELETE CASCADE;
tom114 commented 6 years ago

@peterk87 thinking about it a bit more I had it backwards. In order for JPA/Hibernate to handle the deletion a reference to JobError would need to be added to AnalysisSubmission. That way JPA knows to cascade the deletion. No need for getters/setters. The reference just needs to be there so the deletion is cascaded.

See how ProjectUserJoin is referenced in Project for an example. https://github.com/phac-nml/irida/blob/development/src/main/java/ca/corefacility/bioinformatics/irida/model/project/Project.java#L88

We don't want to just add the cascade deletion in SQL because that makes the dev hibernate created database different from the prod liquibase created one. Try that out.

peterk87 commented 6 years ago

@tom114 Adding the following to AnalysisSubmission fixed the issue:

@NotAudited
@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.REMOVE, mappedBy = "analysisSubmission")
private List<JobError> jobErrors;

I can delete the analysis in the web app, but not with SQL, which was where I was getting stuck since I thought Hibernate would change something to the SQL FK by adding ON DELETE CASCADE.

Thanks for the help! I'll work up a MR.

peterk87 commented 6 years ago

@tom114 would I need to make a new Liquibase change set if the add-job-error-table change set had?

<column name="analysis_submission_id" type="BIGINT">
    <constraints
            referencedTableName="analysis_submission"
            referencedColumnNames="id"
            foreignKeyName="FK_JOB_ERROR_ANALYSIS_SUBMISSION"
            nullable="false"/>
</column>

If so, would it be something like this?

<dropForeignKeyConstraint
  baseTableName="job_error"
  constraintName="FK_JOB_ERROR_ANALYSIS_SUBMISSION"/>
<addForeignKeyConstraint
  baseColumnNames="analysis_submission_id"
  baseTableName="job_error"
  constraintName="FK_JOB_ERROR_ANALYSIS_SUBMISSION"
  onDelete="CASCADE"
  referencedColumnNames="id"
  referencedTableName="analysis_submission"/>
apetkau commented 6 years ago

This issue should be fixed in https://github.com/phac-nml/irida/commit/dcdfdace864558c91c18358c41fcd9b0f1d3f569 and included in the latest release 0.21.0.