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

A foreign key constraint affects Live Edit

    Details

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

      Description

      As part of LDEV-2550, we create a table called lams_log_event:



      DROP TABLE IF EXISTS lams_log_event;
      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)
           , lesson_id BIGINT(20)
           , activity_id BIGINT(20)
           , PRIMARY KEY (id)
           , INDEX (occurred_date_time)
           , CONSTRAINT FK_lams_event_log_1 FOREIGN KEY (log_event_type_id)
                        REFERENCES lams_log_event_type (log_event_type_id)
               , INDEX (user_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;


      However, if you do a Live edit in monitor for a lesson and then you Apply changes, you get the following exception:

      Caused by: java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails (`lams24_wvi`.`lams_log_event`, CONSTRAINT `FK_lams_event_log_5` FOREIGN KEY (`activity_id`) REFERENCES `lams_learning_activity` (`activity_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:774)
      at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
      at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
      ... 79 more


      Can you have a look if this foreign key constraint is necessary and whether we need it in place?

        Activity

        Hide
        ernieg Ernie Ghiglione added a comment -
        This is related and has been fixed in 2.4 via LDEV-2704.

        However, the issue on this bug is because the db upgrade script is missing the on delete contraint. So this should be:

        ....
            , CONSTRAINT FK_lams_event_log_3 FOREIGN KEY (learning_design_id)
                          REFERENCES lams_learning_design (learning_design_id) ON DELETE CASCADE
        ..
        Show
        ernieg Ernie Ghiglione added a comment - This is related and has been fixed in 2.4 via LDEV-2704 . However, the issue on this bug is because the db upgrade script is missing the on delete contraint. So this should be: ....     , CONSTRAINT FK_lams_event_log_3 FOREIGN KEY (learning_design_id)                   REFERENCES lams_learning_design (learning_design_id) ON DELETE CASCADE ..
        Hide
        ernieg Ernie Ghiglione added a comment -
        Tested and closed on upgrades now
        Show
        ernieg Ernie Ghiglione added a comment - Tested and closed on upgrades now

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development