Bug 24289 - Deleting branch will not delete entry in special or repeatable holidays
Summary: Deleting branch will not delete entry in special or repeatable holidays
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: master
Hardware: All All
: P5 - low normal (vote)
Assignee: Jonathan Druart
QA Contact: Testopia
URL:
Keywords:
: 18874 (view as bug list)
Depends on:
Blocks: 5334
  Show dependency treegraph
 
Reported: 2019-12-20 16:23 UTC by Rocio Dressler
Modified: 2020-11-30 21:46 UTC (History)
6 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Medium patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
20.05.00, 19.11.03, 19.05.08


Attachments
Bug 24289: Add foreign keys on *_holidays.branchcode tables (5.10 KB, patch)
2019-12-26 10:49 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 24289: DBIC changes (1.80 KB, patch)
2019-12-26 10:54 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 24289: Make sure sample_holidays will load after sample_libraries (7.33 KB, patch)
2019-12-26 11:08 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 24289: Add foreign keys on *_holidays.branchcode tables (5.10 KB, patch)
2020-01-15 19:21 UTC, Owen Leonard
Details | Diff | Splinter Review
Bug 24289: DBIC changes (1.80 KB, patch)
2020-01-15 19:21 UTC, Owen Leonard
Details | Diff | Splinter Review
Bug 24289: Make sure sample_holidays will load after sample_libraries (7.33 KB, patch)
2020-01-15 19:21 UTC, Owen Leonard
Details | Diff | Splinter Review
Bug 24289: Add foreign keys on *_holidays.branchcode tables (5.16 KB, patch)
2020-01-20 07:06 UTC, Katrin Fischer
Details | Diff | Splinter Review
Bug 24289: DBIC changes (1.85 KB, patch)
2020-01-20 07:06 UTC, Katrin Fischer
Details | Diff | Splinter Review
Bug 24289: Make sure sample_holidays will load after sample_libraries (7.38 KB, patch)
2020-01-20 07:06 UTC, Katrin Fischer
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Rocio Dressler 2019-12-20 16:23:30 UTC
When you delete a branch that has holidays specified in special_holidays, these entries don't get deleted.

To recreate:

- add an calendar entry to Calendar for this branch
- select * from special_holidays where branchcode = 'YOUR BRANCH';
- delete branch from Administration -> Libraries
- select *.... holidays are still there for that branchcode
Comment 1 Jonathan Druart 2019-12-26 10:49:09 UTC
Created attachment 96630 [details] [review]
Bug 24289: Add foreign keys on *_holidays.branchcode tables

Those are missing foreign keys, it will also clean the holidays tables
and automatically remove the holidays when a library is removed.

Test plan:
0. Do not apply this patch
1. Create a new library
2. Create special and repeatable holiday for this library
3. Remove the library
=> Notice that the holidays still exist in DB
4. Apply this patch
5. Execute the update database entry
=> Notice that the holidays for this library have been removed from the DB
6. Repeat 1-3
=> Notice that the holidays have been removed along with the library
Comment 2 Jonathan Druart 2019-12-26 10:54:13 UTC
Created attachment 96631 [details] [review]
Bug 24289: DBIC changes
Comment 3 Jonathan Druart 2019-12-26 11:08:22 UTC
Created attachment 96632 [details] [review]
Bug 24289: Make sure sample_holidays will load after sample_libraries

They are inserted depending on their alpha order, and sample_holidays
will fail because of missing branchcode.
This patch suggests a simple fix: rename sample_holidays to
sample_libraries_holidays, to make sure it will be loaded after
sample_libraries

Test plan:
Start a fresh installation and select all sample data
Comment 4 Owen Leonard 2020-01-15 19:21:51 UTC
Created attachment 97410 [details] [review]
Bug 24289: Add foreign keys on *_holidays.branchcode tables

Those are missing foreign keys, it will also clean the holidays tables
and automatically remove the holidays when a library is removed.

Test plan:
0. Do not apply this patch
1. Create a new library
2. Create special and repeatable holiday for this library
3. Remove the library
=> Notice that the holidays still exist in DB
4. Apply this patch
5. Execute the update database entry
=> Notice that the holidays for this library have been removed from the DB
6. Repeat 1-3
=> Notice that the holidays have been removed along with the library
Comment 5 Owen Leonard 2020-01-15 19:21:54 UTC
Created attachment 97411 [details] [review]
Bug 24289: DBIC changes
Comment 6 Owen Leonard 2020-01-15 19:21:56 UTC
Created attachment 97412 [details] [review]
Bug 24289: Make sure sample_holidays will load after sample_libraries

They are inserted depending on their alpha order, and sample_holidays
will fail because of missing branchcode.
This patch suggests a simple fix: rename sample_holidays to
sample_libraries_holidays, to make sure it will be loaded after
sample_libraries

Test plan:
Start a fresh installation and select all sample data
Comment 7 Katrin Fischer 2020-01-20 07:02:55 UTC
*** Bug 18874 has been marked as a duplicate of this bug. ***
Comment 8 Katrin Fischer 2020-01-20 07:06:20 UTC
Created attachment 97600 [details] [review]
Bug 24289: Add foreign keys on *_holidays.branchcode tables

Those are missing foreign keys, it will also clean the holidays tables
and automatically remove the holidays when a library is removed.

Test plan:
0. Do not apply this patch
1. Create a new library
2. Create special and repeatable holiday for this library
3. Remove the library
=> Notice that the holidays still exist in DB
4. Apply this patch
5. Execute the update database entry
=> Notice that the holidays for this library have been removed from the DB
6. Repeat 1-3
=> Notice that the holidays have been removed along with the library

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Comment 9 Katrin Fischer 2020-01-20 07:06:23 UTC
Created attachment 97601 [details] [review]
Bug 24289: DBIC changes

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Comment 10 Katrin Fischer 2020-01-20 07:06:27 UTC
Created attachment 97602 [details] [review]
Bug 24289: Make sure sample_holidays will load after sample_libraries

They are inserted depending on their alpha order, and sample_holidays
will fail because of missing branchcode.
This patch suggests a simple fix: rename sample_holidays to
sample_libraries_holidays, to make sure it will be loaded after
sample_libraries

Test plan:
Start a fresh installation and select all sample data

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Comment 11 Martin Renvoize 2020-01-20 14:04:41 UTC
Nice work everyone!

Pushed to master for 20.05
Comment 12 Joy Nelson 2020-02-07 23:14:36 UTC
Pushed to 19.11.x branch for 19.11.03
Comment 13 Lucas Gass 2020-02-13 21:12:23 UTC
backported to 19.05.x for 19.05.08
Comment 14 Pablo AB 2020-05-14 05:36:31 UTC
On December I installed an instance based on koha stable without any issue. Today I get this error on web installer, while adding sample data. Seems related to this bug:

sample_libraries_holidays.sql
[Wed May 13 19:24:37 2020] install.pl: DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`koha_mylibrary`.`repeatable_holidays`, CONSTRAINT `repeatable_holidays_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE) at /usr/share/perl5/DBIx/RunSQL.pm line 273.
Comment 15 Jonathan Druart 2020-05-14 08:42:08 UTC
(In reply to Pablo AB from comment #14)
> On December I installed an instance based on koha stable without any issue.
> Today I get this error on web installer, while adding sample data. Seems
> related to this bug:
> 
> sample_libraries_holidays.sql
> [Wed May 13 19:24:37 2020] install.pl: DBD::mysql::st execute failed: Cannot
> add or update a child row: a foreign key constraint fails
> (`koha_mylibrary`.`repeatable_holidays`, CONSTRAINT
> `repeatable_holidays_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES
> `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE) at
> /usr/share/perl5/DBIx/RunSQL.pm line 273.

Did you pick the sample libraries as well?
Comment 16 Pablo AB 2020-05-20 04:36:31 UTC
> Did you pick the sample libraries as well?

Probably not. I believe I just wanted to have some basic holidays set (for all branches) without adding the sample libraries.
Now reading the sql filename I realize this might not be possible.

Checkboxes have dependencies between each which are not validated?
Comment 17 Jonathan Druart 2020-05-20 08:35:22 UTC
There is no validation (it should). The holidays tables have a foreign key on branches.branchcode, so you need the sample libraries as well.