[Bug 17160] New: Foreign key constraint for message_transports is too loose
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17160 Bug ID: 17160 Summary: Foreign key constraint for message_transports is too loose Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Notices Assignee: koha-bugs@lists.koha-community.org Reporter: barton@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org I think this is the probable cause for Bug 13171. http://git.koha-community.org/gitweb/?p=koha.git;a=blob;f=installer/data/mys... The message transports definition -- -- Table structure for table `message_transports` -- DROP TABLE IF EXISTS `message_transports`; CREATE TABLE `message_transports` ( `message_attribute_id` int(11) NOT NULL, `message_transport_type` varchar(20) NOT NULL, `is_digest` tinyint(1) NOT NULL default '0', `letter_module` varchar(20) NOT NULL default '', `letter_code` varchar(20) NOT NULL default '', `branchcode` varchar(10) NOT NULL default '', PRIMARY KEY (`message_attribute_id`,`message_transport_type`,`is_digest`), KEY `message_transport_type` (`message_transport_type`), KEY `letter_module` (`letter_module`,`letter_code`), CONSTRAINT `message_transports_ibfk_1` FOREIGN KEY (`message_attribute_id`) REFERENCES `message_attributes` (`message_attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `message_transports_ibfk_2` FOREIGN KEY (`message_transport_type`) REFERENCES `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`, `branchcode`) REFERENCES `letter` (`module`, `code`, `branchcode`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; Has `message_transport_types` (`message_transport_type`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`, `branchcode`) REFERENCES `letter` (`module`, `code`, `branchcode`) ON DELETE CASCADE ON UPDATE CASCADE The foreign key contstraints `module`, `code`, `branchcode` do *NOT* uniquely identify a row in the letter table, because message_transport_type may vary. Therefore, a deletion *any* message transport type will delete *all* rows matching `module`, `code`, `branchcode`. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17160 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |kyle@bywatersolutions.com Severity|enhancement |major -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17160 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart@bugs.koha-c | |ommunity.org --- Comment #1 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Barton, I understand the problem but it does not look so easy to fix it. In the message_transports we have MariaDB [koha]> select message_transport_type, letter_module, letter_code from message_transports; +------------------------+---------------+-------------+ | message_transport_type | letter_module | letter_code | +------------------------+---------------+-------------+ | email | circulation | CHECKIN | | sms | circulation | CHECKIN | | email | circulation | CHECKOUT | | sms | circulation | CHECKOUT | | email | circulation | DUE | | sms | circulation | DUE | | email | circulation | DUEDGST | | sms | circulation | DUEDGST | | email | circulation | PREDUE | | sms | circulation | PREDUE | | email | circulation | PREDUEDGST | | sms | circulation | PREDUEDGST | | email | reserves | HOLD | | sms | reserves | HOLD | +------------------------+---------------+-------------+ 14 rows in set (0.10 sec) But the letter table does necessarily contain a sms template for CHECKIN or CHECKOUT (or whatever). If it does not exist, we won't be able to create the FK correctly. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17160 --- Comment #2 from Barton Chittenden <barton@bywatersolutions.com> --- (In reply to Jonathan Druart from comment #1)
Barton, I understand the problem but it does not look so easy to fix it.
<snip>
But the letter table does necessarily contain a sms template for CHECKIN or CHECKOUT (or whatever). If it does not exist, we won't be able to create the FK correctly.
I see your point... can we remove ON DELETE CASCADE ON UPDATE CASCADE from CONSTRAINT `message_transports_ibfk_3` FOREIGN KEY (`letter_module`, `letter_code`, `branchcode`) REFERENCES `letter` (`module`, `code`, `branchcode`) ON DELETE CASCADE ON UPDATE CASCADE So that at least we're not losing lines in 'message_transports' if we happen to delete a row from the letter table? -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17160 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |katrin.fischer@bsz-bw.de --- Comment #3 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Wondering - how would you delete a single message transport line without SQL? In the GUI the letter with all its message types appears as one thing that can only be deleted completely. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17160 --- Comment #4 from Barton Chittenden <barton@bywatersolutions.com> --- (In reply to Katrin Fischer from comment #3)
Wondering - how would you delete a single message transport line without SQL? In the GUI the letter with all its message types appears as one thing that can only be deleted completely.
Cait, I'm pretty sure that updatedatabase did it on Bug 13171. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17160 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |5334 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=5334 [Bug 5334] Omnibus: Add explicit foreign key constraints -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17160 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Resolution|--- |DUPLICATE Status|NEW |RESOLVED --- Comment #5 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- message_transports_ibfk_3 has been removed on bug 17762. *** This bug has been marked as a duplicate of bug 17762 *** -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org