[Bug 37419] New: Deleting the record source deletes the associated biblio_metadata rows.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 Bug ID: 37419 Summary: Deleting the record source deletes the associated biblio_metadata rows. Change sponsored?: --- Product: Koha Version: unspecified Hardware: All OS: All Status: NEW Severity: major Priority: P5 - low Component: System Administration Assignee: koha-bugs@lists.koha-community.org Reporter: januszop@gmail.com QA Contact: testopia@bugs.koha-community.org CC: arthur.suzuki@biblibre.com, gmcharlt@gmail.com, tomascohen@gmail.com Depends on: 35919 Blocks: 36372 After applying the Bug 36372, it is possible to manually set record source for a given bibliographic record. At the same time, from /cgi-bin/koha/admin/record_sources it is possible to delete a record source currently being used, without any control. This in turn causes the deletion of all the rows from the biblio_metadata table linked with the record source being deleted, which is fatal. BTW the biblio and biblioitems rows remain in the database. This effect is caused by "CONSTRAINT `record_metadata_fk_2` FOREIGN KEY (`record_source_id`) REFERENCES `record_sources` (`record_source_id`) ON DELETE CASCADE" in biblio_metadata setup. Of course the same effect occurs when deleting the record_sources row by hand, directly in the database. I am not sure if the decision to link biblio_metadata with record_sources with ON DELETE CASCADE is just, even if we controlled the use of the specific record source before deletion (as we do with itemtypes for instance). Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35919 [Bug 35919] Add record sources CRUD https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36372 [Bug 36372] Allow privileged users to set the 'record source' on cataloguing -- 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=37419 --- Comment #1 from Arthur Suzuki <arthur.suzuki@biblibre.com> --- I was able to reproduce the issue -- 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=37419 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dcook@prosentient.com.au --- Comment #2 from David Cook <dcook@prosentient.com.au> --- Sounds like this should be "ON DELETE RESTRICT" -- 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=37419 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |RM_priority --- Comment #3 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to David Cook from comment #2)
Sounds like this should be "ON DELETE RESTRICT"
Makes sense. Adding RM_priority as this could lead to severe data loss, especially since rows are deleted directly instead of being moved... -- 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=37419 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Summary|Deleting the record source |Deleting the record source |deletes the associated |deletes the associated |biblio_metadata rows. |biblio_metadata rows -- 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=37419 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |tomascohen@gmail.com |ity.org | -- 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=37419 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Patch complexity|--- |Trivial patch Status|NEW |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
UPDATE biblio_metadata SET record_source_id='your source id' WHERE biblionumber=1;
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 --- Comment #4 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Created attachment 169823 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=169823&action=edit Bug 37419: Update FK constraint on record sources to avoid data loss Without this patch, deleting a record source will delete the associated biblio_metadata rows, which is a severe data loss. This patch makes the constraint restrict this action. To test: 1. Add a record source 2. Set the record source to some records $ koha-mysql kohadev 3. Delete the record source => FAIL: Record metadata deleted 4. Apply this patch 5, Run: $ ktd --shell k$ updatedatabase => SUCCESS: DB update goes well 6. Repeat 1~3 with another record => SUCCESS: Source cannot be deleted if there are linked records Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 --- Comment #5 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Created attachment 169824 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=169824&action=edit [DO NOT PUSH] Bug 37419: DBIC schema Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |37510 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37510 [Bug 37510] Koha::Object->delete should throw a Koha::Exception if there's a parent row constraint -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 Janusz Kaczmarek <januszop@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
UPDATE biblio_metadata SET record_source_id='your source id' WHERE biblionumber=1;
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 Janusz Kaczmarek <januszop@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #169823|0 |1 is obsolete| | Attachment #169824|0 |1 is obsolete| | --- Comment #6 from Janusz Kaczmarek <januszop@gmail.com> --- Created attachment 169827 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=169827&action=edit Bug 37419: Update FK constraint on record sources to avoid data loss Without this patch, deleting a record source will delete the associated biblio_metadata rows, which is a severe data loss. This patch makes the constraint restrict this action. To test: 1. Add a record source 2. Set the record source to some records $ koha-mysql kohadev 3. Delete the record source => FAIL: Record metadata deleted 4. Apply this patch 5, Run: $ ktd --shell k$ updatedatabase => SUCCESS: DB update goes well 6. Repeat 1~3 with another record => SUCCESS: Source cannot be deleted if there are linked records Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> Signed-off-by: Janusz Kaczmarek <januszop@gmail.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 --- Comment #7 from Janusz Kaczmarek <januszop@gmail.com> --- Created attachment 169828 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=169828&action=edit Bug 37419: DBIC schema Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> Signed-off-by: Janusz Kaczmarek <januszop@gmail.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 --- Comment #8 from Janusz Kaczmarek <januszop@gmail.com> --- Just one remark: the message generated after an unsuccessful deletion sounds: "Something went wrong: Error: Something went wrong, check Koha logs for details." I wonder if it shouldn't/couldn't be more detailed... -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 --- Comment #9 from Tomás Cohen Arazi <tomascohen@gmail.com> --- (In reply to Janusz Kaczmarek from comment #8)
Just one remark: the message generated after an unsuccessful deletion sounds: "Something went wrong: Error: Something went wrong, check Koha logs for details." I wonder if it shouldn't/couldn't be more detailed...
Indeed. I'm working on bug 37510 to properly handle the DBIC exception from ->delete() in the codebase, and then will handle it on the Vue side. Thanks for the feedback! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |pedro.amorim@ptfs-europe.co | |m -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |37513 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37513 [Bug 37513] Deleting the record source should never display an internal server error -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|testopia@bugs.koha-communit |martin.renvoize@ptfs-europe |y.org |.com CC| |martin.renvoize@ptfs-europe | |.com -- You are receiving this mail because: You are watching all bug changes.
UPDATE biblio_metadata SET record_source_id='your source id' WHERE biblionumber=1;
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #169827|0 |1 is obsolete| | --- Comment #10 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 169932 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=169932&action=edit Bug 37419: Update FK constraint on record sources to avoid data loss Without this patch, deleting a record source will delete the associated biblio_metadata rows, which is a severe data loss. This patch makes the constraint restrict this action. To test: 1. Add a record source 2. Set the record source to some records $ koha-mysql kohadev 3. Delete the record source => FAIL: Record metadata deleted 4. Apply this patch 5, Run: $ ktd --shell k$ updatedatabase => SUCCESS: DB update goes well 6. Repeat 1~3 with another record => SUCCESS: Source cannot be deleted if there are linked records Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> Signed-off-by: Janusz Kaczmarek <januszop@gmail.com> Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #169828|0 |1 is obsolete| | --- Comment #11 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 169933 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=169933&action=edit Bug 37419: DBIC schema Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io> Signed-off-by: Janusz Kaczmarek <januszop@gmail.com> Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|RM_priority | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to main Version(s)| |24.11.00 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 --- Comment #12 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Pushed for 24.11! Well done everyone, thank you! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to main |Pushed to stable Version(s)|24.11.00 |24.11.00,24.05.04 released in| | CC| |lucas@bywatersolutions.com --- Comment #13 from Lucas Gass <lucas@bywatersolutions.com> --- Backported to 24.05.x for upcoming 24.05.04 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to stable |Needs documenting CC| |fridolin.somers@biblibre.co | |m --- Comment #14 from Fridolin Somers <fridolin.somers@biblibre.com> --- Depends on Bug 35919 not in 23.11.x -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |additional_work_needed --- Comment #15 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- I am seeing something strange here when running the database scripts for comparing new/udpate before release: In the generated kohastructure.sql: @@ -1151,7 +1150,7 @@ CREATE TABLE `biblio_metadata` ( KEY `timestamp` (`timestamp`), KEY `record_metadata_fk_2` (`record_source_id`), CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `record_metadata_fk_2` FOREIGN KEY (`record_source_id`) REFERENCES `record_sources` (`record_source_id`) ON DELETE RESTRICT ON UPDATE CASCADE + CONSTRAINT `record_metadata_fk_2` FOREIGN KEY (`record_source_id`) REFERENCES `record_sources` (`record_source_id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; Where has the "ON DELETE RESTRICT" gone? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 --- Comment #16 from Janusz Kaczmarek <januszop@gmail.com> --- How is that possible? In master we see at line 1147 (from this bug): - CONSTRAINT `record_metadata_fk_2` FOREIGN KEY (`record_source_id`) REFERENCES `record_sources` (`record_source_id`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `record_metadata_fk_2` FOREIGN KEY (`record_source_id`) REFERENCES `record_sources` (`record_source_id`) ON DELETE RESTRICT ON UPDATE CASCADE and then noting in this region. Strange... -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 --- Comment #17 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to Janusz Kaczmarek from comment #16)
How is that possible? In master we see at line 1147 (from this bug):
- CONSTRAINT `record_metadata_fk_2` FOREIGN KEY (`record_source_id`) REFERENCES `record_sources` (`record_source_id`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `record_metadata_fk_2` FOREIGN KEY (`record_source_id`) REFERENCES `record_sources` (`record_source_id`) ON DELETE RESTRICT ON UPDATE CASCADE
and then noting in this region. Strange...
I have no explanation either. I'll probably repeat the steps on Monday just to make sure, but we need to resolve. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|additional_work_needed | --- Comment #18 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- @@ -1151,7 +1150,7 @@ CREATE TABLE `biblio_metadata` ( KEY `timestamp` (`timestamp`), KEY `record_metadata_fk_2` (`record_source_id`), CONSTRAINT `record_metadata_fk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `record_metadata_fk_2` FOREIGN KEY (`record_source_id`) REFERENCES `record_sources` (`record_source_id`) ON DELETE RESTRICT ON UPDATE CASCADE + CONSTRAINT `record_metadata_fk_2` FOREIGN KEY (`record_source_id`) REFERENCES `record_sources` (`record_source_id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; We have come to the conclusion that ON UPDATE RESTRICT is missing form the dump, as this is the default behavior. So this is a non-issue actually and things are in order. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 --- Comment #19 from Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> --- (In reply to Katrin Fischer from comment #18)
We have come to the conclusion that ON UPDATE RESTRICT is missing form the dump, as this is the default behavior. So this is a non-issue actually and things are in order.
Small behavior differences in MariaDB would cause this. It really depends on which MariaDB version you're running this at. When you regenerated for 24.05, this is what we used: ``` -- Server version 11.3.2-MariaDB-1:11.3.2+maria~ubu2204 ``` for 23.11 we used this: ``` -- Server version 11.2.2-MariaDB-1:11.2.2+maria~ubu2204 ``` -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 --- Comment #20 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to Tomás Cohen Arazi (tcohen) from comment #19)
(In reply to Katrin Fischer from comment #18)
We have come to the conclusion that ON UPDATE RESTRICT is missing form the dump, as this is the default behavior. So this is a non-issue actually and things are in order.
Small behavior differences in MariaDB would cause this. It really depends on which MariaDB version you're running this at.
When you regenerated for 24.05, this is what we used:
``` -- Server version 11.3.2-MariaDB-1:11.3.2+maria~ubu2204 ```
for 23.11 we used this:
``` -- Server version 11.2.2-MariaDB-1:11.2.2+maria~ubu2204 ```
The instructions say to use this: DB_IMAGE=mariadb:latest ktd up -d So it makes sense that a newer version was used this time. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 --- Comment #21 from Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> --- (In reply to Katrin Fischer from comment #20)
The instructions say to use this: DB_IMAGE=mariadb:latest ktd up -d
So it makes sense that a newer version was used this time.
Absolutely. Just noted that minor changes are common because of the version change. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37419 Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |caroline.cyr-la-rose@inlibr | |o.com Status|Needs documenting |RESOLVED Resolution|--- |FIXED --- Comment #22 from Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com> --- Internal architecture, nothing to add/edit in the manual. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org