Uploaded image for project: 'LAMS Development'
  1. LAMS Development
  2. LDEV-2704

Can't delete learning designs due to constraints with logs

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.4, 2.3.6, 2.3.5
    • Fix Version/s: 2.4, 2.3.6, 2.3.5
    • Component/s: General
    • Labels:
      None

      Description

      It seems that in 2.3.5 if you try to delete a learning design in author you get an exception from the database as there's a foreign key constraint.


      CREATE TABLE `lams_log_event` (
        `id` bigint(20) NOT NULL AUTO_INCREMENT,
        `log_event_type_id` int(5) NOT NULL,
        `user_id` bigint(20) NOT NULL,
        `occurred_date_time` datetime NOT NULL,
        `learning_design_id` bigint(20) DEFAULT NULL,
        `lesson_id` bigint(20) DEFAULT NULL,
        `activity_id` bigint(20) DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `occurred_date_time` (`occurred_date_time`),
        KEY `FK_lams_event_log_1` (`log_event_type_id`),
        KEY `user_id` (`user_id`),
        KEY `FK_lams_event_log_3` (`learning_design_id`),
        KEY `FK_lams_event_log_4` (`lesson_id`),
        KEY `FK_lams_event_log_5` (`activity_id`),
        CONSTRAINT `FK_lams_event_log_1` FOREIGN KEY (`log_event_type_id`) REFERENCES `lams_log_event_type` (`log_event_type_id`),
        CONSTRAINT `FK_lams_event_log_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`),
        CONSTRAINT `FK_lams_event_log_3` FOREIGN KEY (`learning_design_id`) REFERENCES `lams_learning_design` (`learning_design_id\
      `),
        CONSTRAINT `FK_lams_event_log_4` FOREIGN KEY (`lesson_id`) REFERENCES `lams_lesson` (`lesson_id`),
        CONSTRAINT `FK_lams_event_log_5` FOREIGN KEY (`activity_id`) REFERENCES `lams_learning_activity` (`activity_id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
      /*!40101 SET character_set_client = @saved_cs_client */;


      See exception:


      2011-07-15 14:16:59,905 DEBUG [WorkspaceManagementService] 0 child workspace folders
      2011-07-15 14:17:04,856 ERROR [WorkspaceAction] deleteResource: Exception occured. userID 1 folderID 1
      org.springframework.dao.DataIntegrityViolationException: Hibernate transaction synchronization: Could not execute JDBC batch update; SQL [delete from lams_learning_design where learning_design_id=?]; Cannot delete or update a parent row: a foreign key constraint fails (`lams`.`lams_log_event`, CONSTRAINT `FK_lams_event_log_3` FOREIGN KEY (`learning_design_id`) REFERENCES `lams_learning_design` (`learning_design_id`)); nested exception is java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails (`lams`.`lams_log_event`, CONSTRAINT `FK_lams_event_log_3` FOREIGN KEY (`learning_design_id`) REFERENCES `lams_learning_design` (`learning_design_id`))
      java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails (`lams`.`lams_log_event`, CONSTRAINT `FK_lams_event_log_3` FOREIGN KEY (`learning_design_id`) REFERENCES `lams_learning_design` (`learning_design_id`))
              at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1269)
              at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:955)
              at org.jboss.resource.adapter.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:701)
              at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:57)
              at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:172)
              at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:226)
              at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:141)
              at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
              at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
              at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:716)
              at org.springframework.orm.hibernate3.SessionFactoryUtils$SpringSessionSynchronization.beforeCommit(SessionFactoryUtils.java:884)
              at org.springframework.transaction.support.AbstractPlatformTransactionManager.triggerBeforeCommit(AbstractPlatformTransactionManager.java:661)
              at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:486)
              at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:473)
              at org.springframework.transaction.interceptor.TransactionAspectSupport.doCommitTransactionAfterReturning(TransactionAspectSupport.java:267)
              at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
              at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
              at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:623)
              at org.lamsfoundation.lams.workspace.service.WorkspaceManagementService$$EnhancerByCGLIB$$7c9ff315.deleteResource(<generated>)
              at org.lamsfoundation.lams.workspace.web.WorkspaceAction.deleteResource(WorkspaceAction.java:321)
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
              at java.lang.reflect.Method.invoke(Method.java:592)
              at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:270)
              at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:187)
              at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
              at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
              at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
              at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
              at javax.servlet.http.HttpServlet.service(HttpServlet.java:697)
              at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
              at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
              at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
              at org.lamsfoundation.lams.web.filter.LocaleFilter.doFilterInternal(LocaleFilter.java:142)
              at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:77)
              at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
              at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
              at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:174)
              at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:77)
              at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
              at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
              at org.lamsfoundation.lams.web.session.SystemSessionFilter.doFilter(SystemSessionFilter.java:71)
              at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
              at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
              at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:81)
              at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
              at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
              at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
              at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
              at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:39)
              at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:153)
              at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:482)
              at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:482)
              at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:59)
              at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
              at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
              at org.lamsfoundation.lams.integration.security.SingleSignOn.invoke(SingleSignOn.java:121)
              at org.lamsfoundation.lams.integration.security.LoginRequestValve.invoke(LoginRequestValve.java:67)
              at org.apache.catalina.valves.FastCommonAccessLogValve.invoke(FastCommonAccessLogValve.java:481)
              at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
              at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
              at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
              at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
              at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
              at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112)
              at java.lang.Thread.run(Thread.java:655)
      2011-07-15 14:17:04,988 DEBUG [WorkspaceManagementService] sysadmin has 3 access to System Administrator
      2011-07-15 14:17:05,015 DEBUG [WorkspaceManagementService] Trying to get contents from folder System Administrator
      2011-07-15 14:17:05,015 DEBUG [WorkspaceManagementService] 0 child workspace folders
      2011-07-15 14:17:12,247 INFO [LocalDataSourceJobStore] Handling 1 triggers that missed their scheduled fire-time.


      I think the quickest way to fix it is to run this on the database:

      ALTER TABLE lams_log_event DROP FOREIGN KEY FK_lams_event_log_3;

      However, that would break references when we get to create a meaningful logging system with the lams_log_event table. But since we don't have that right now, I think we are OK to delete the foreign key.

      Andrey, do you forsee any issues if we drop the foreign key?

        Activity

        ernieg Ernie Ghiglione created issue -
        Hide
        dronych Andrey Balan added a comment -
        I added a fix to autopatch(both for lams2_3_release and HEAD).

        but if you need to apply this now you can run the following script:
        ALTER TABLE lams_log_event DROP FOREIGN KEY FK_lams_event_log_3;
        ALTER TABLE lams_log_event ADD CONSTRAINT FK_lams_event_log_3 FOREIGN KEY (learning_design_id) REFERENCES lams_learning_design (learning_design_id ) ON DELETE CASCADE;
        Show
        dronych Andrey Balan added a comment - I added a fix to autopatch(both for lams2_3_release and HEAD). but if you need to apply this now you can run the following script: ALTER TABLE lams_log_event DROP FOREIGN KEY FK_lams_event_log_3; ALTER TABLE lams_log_event ADD CONSTRAINT FK_lams_event_log_3 FOREIGN KEY (learning_design_id) REFERENCES lams_learning_design (learning_design_id ) ON DELETE CASCADE;
        dronych Andrey Balan made changes -
        Field Original Value New Value
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        Hide
        ernieg Ernie Ghiglione added a comment -
        missing the creation db scripts
        Show
        ernieg Ernie Ghiglione added a comment - missing the creation db scripts
        ernieg Ernie Ghiglione made changes -
        Resolution Fixed [ 1 ]
        Status Resolved [ 5 ] Reopened [ 4 ]
        Hide
        ernieg Ernie Ghiglione added a comment -
        done
        Show
        ernieg Ernie Ghiglione added a comment - done
        ernieg Ernie Ghiglione made changes -
        Status Reopened [ 4 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        Hide
        ernieg Ernie Ghiglione added a comment -
        Tested and closed
        Show
        ernieg Ernie Ghiglione added a comment - Tested and closed
        ernieg Ernie Ghiglione made changes -
        Status Resolved [ 5 ] Closed [ 6 ]

          People

          • Assignee:
            dronych Andrey Balan
            Reporter:
            ernieg Ernie Ghiglione
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development