Details
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?
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?
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
..