Bug 29778 - Deleting additional_contents leaves entries for additional languages
Summary: Deleting additional_contents leaves entries for additional languages
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: OPAC (show other bugs)
Version: master
Hardware: All All
: P5 - low critical (vote)
Assignee: Jonathan Druart
QA Contact: Testopia
URL:
Keywords:
Depends on: 24387
Blocks: 29805
  Show dependency treegraph
 
Reported: 2022-01-01 12:27 UTC by Katrin Fischer
Modified: 2022-10-11 12:45 UTC (History)
5 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:
22.05.00,21.11.02


Attachments
Bug 29778: Prevent orphan additional contents (2.67 KB, patch)
2022-01-03 10:35 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 29778: Remove DBMS error (1.29 KB, patch)
2022-01-05 15:46 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 29778: Prevent orphan additional contents (2.72 KB, patch)
2022-01-05 18:19 UTC, Lucas Gass
Details | Diff | Splinter Review
Bug 29778: Remove DBMS error (1.34 KB, patch)
2022-01-05 18:19 UTC, Lucas Gass
Details | Diff | Splinter Review
Bug 29778: Prevent orphan additional contents (2.78 KB, patch)
2022-01-08 21:32 UTC, Katrin Fischer
Details | Diff | Splinter Review
Bug 29778: Remove DBMS error (1.40 KB, patch)
2022-01-08 21:33 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 Katrin Fischer 2022-01-01 12:27:12 UTC
We have had 2 reports about this on the mailing list from 3 different people so far. It was not possible yet to determine the cause:

https://lists.katipo.co.nz/pipermail/koha/2021-December/057161.html
https://lists.katipo.co.nz/pipermail/koha/2021-December/057186.html

I've tried to replicate on master, but no luck so far.
Comment 1 Katrin Fischer 2022-01-02 14:55:27 UTC
I have managed to find the steps to make this bug visible:

1) Install an additional language, like de-DE
2) Activate language
3) Add a new HTML customization entry
  * Fill in default and Englis
  * Fill in the additional installed language
  * Save
4) Verify the contents show as they should
5) Delete the entry
6) Verify the content remain for the additional language

This can also be verified by running: select * from additional_contents;

In my case I deleted everything else and now only this entry remains that is not visible from the GUI:

+-------+---------------------+---------------------+-------------------+------------+-------+---------+-------+--------------+---------------------+----------------+--------+----------------+
| idnew | category            | code                | location          | branchcode | title | content | lang  | published_on | updated_on          | expirationdate | number | borrowernumber |
+-------+---------------------+---------------------+-------------------+------------+-------+---------+-------+--------------+---------------------+----------------+--------+----------------+
|     7 | html_customizations | OpacMainUserBlock_6 | OpacMainUserBlock | NULL       | test  | test    | de-DE | 2022-01-02   | 2022-01-02 01:46:58 | NULL           |   NULL |             51 |
+-------+---------------------+---------------------+-------------------+------------+-------+---------+-------+--------------+---------------------+----------------+--------+----------------+

Updating severity as this means people have no way to change or remove unwanted content - we need to fix this and also include a database update.
Comment 2 Jonathan Druart 2022-01-03 10:35:28 UTC
Created attachment 128957 [details] [review]
Bug 29778: Prevent orphan additional contents

When deleting a content, only the main one (lang="default") is removed,
which leads to orphan contents in the DB that are still displayed on the
UI.

Test plan:
0. Don't apply this patch
1. Create some contents, translate them in different languages
2. Delete some of them
=> Note that they are still displayed on the UI and that the entries
with lang!="default" are still in the DB
3. Apply this patch
4. Repeat 1
5. Run updatedatabase
6. Delete from of the contents
=> Note that the orphan entries created before you applied the patch
have been removed and that the "delete" behaviour is now working
correnctly.
Comment 3 Owen Leonard 2022-01-05 12:17:58 UTC
I'm getting this error when running updatedatabase:

ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Table 'additional_contents' is specified twice, both as a target for 'DELETE' and as a separate source for data at /kohadevbox/koha/C4/Installer.pm line 738

My "About" page says: MySQL version: 	mysql Ver 15.1 Distrib 10.3.31-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Comment 4 Jonathan Druart 2022-01-05 15:46:29 UTC
Created attachment 129038 [details] [review]
Bug 29778: Remove DBMS error

ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Table 'additional_contents' is specified twice, both as a target for 'DELETE' and as a separate source for data at /kohadevbox/koha/C4/Installer.pm line 738

Happened on MariaDB 10.1 and 10.3, not 10.6
Comment 5 Lucas Gass 2022-01-05 18:19:08 UTC
Created attachment 129040 [details] [review]
Bug 29778: Prevent orphan additional contents

When deleting a content, only the main one (lang="default") is removed,
which leads to orphan contents in the DB that are still displayed on the
UI.

Test plan:
0. Don't apply this patch
1. Create some contents, translate them in different languages
2. Delete some of them
=> Note that they are still displayed on the UI and that the entries
with lang!="default" are still in the DB
3. Apply this patch
4. Repeat 1
5. Run updatedatabase
6. Delete from of the contents
=> Note that the orphan entries created before you applied the patch
have been removed and that the "delete" behaviour is now working
correnctly.

Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Comment 6 Lucas Gass 2022-01-05 18:19:11 UTC
Created attachment 129041 [details] [review]
Bug 29778: Remove DBMS error

ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Table 'additional_contents' is specified twice, both as a target for 'DELETE' and as a separate source for data at /kohadevbox/koha/C4/Installer.pm line 738

Happened on MariaDB 10.1 and 10.3, not 10.6

Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Comment 7 Katrin Fischer 2022-01-08 21:32:58 UTC
Created attachment 129218 [details] [review]
Bug 29778: Prevent orphan additional contents

When deleting a content, only the main one (lang="default") is removed,
which leads to orphan contents in the DB that are still displayed on the
UI.

Test plan:
0. Don't apply this patch
1. Create some contents, translate them in different languages
2. Delete some of them
=> Note that they are still displayed on the UI and that the entries
with lang!="default" are still in the DB
3. Apply this patch
4. Repeat 1
5. Run updatedatabase
6. Delete from of the contents
=> Note that the orphan entries created before you applied the patch
have been removed and that the "delete" behaviour is now working
correnctly.

Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Comment 8 Katrin Fischer 2022-01-08 21:33:03 UTC
Created attachment 129219 [details] [review]
Bug 29778: Remove DBMS error

ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Table 'additional_contents' is specified twice, both as a target for 'DELETE' and as a separate source for data at /kohadevbox/koha/C4/Installer.pm line 738

Happened on MariaDB 10.1 and 10.3, not 10.6

Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>

Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Comment 9 Katrin Fischer 2022-01-08 21:34:04 UTC
Bug 29828 shows another vector of creating database entries for additional contents that are not visible in the GUI - ideally we should have fixes for both in the next releases!
Comment 10 Fridolin Somers 2022-01-10 07:05:12 UTC
Pushed to master for 22.05, thanks to everybody involved 🦄
Comment 11 Jonathan Druart 2022-01-10 10:06:40 UTC
(In reply to Katrin Fischer from comment #9)
> Bug 29828 shows another vector of creating database entries for additional
> contents that are not visible in the GUI - ideally we should have fixes for
> both in the next releases!

Please backport with this comment in mind!
Comment 12 Kyle M Hall 2022-01-14 13:40:51 UTC
Pushed to 21.11.x for 21.11.02
Comment 13 Andrew Fuerste-Henry 2022-01-21 17:26:15 UTC
Missing dependency, not backported to 21.05
Comment 14 Marcel de Rooy 2022-10-11 12:45:24 UTC
Just mentioning that this dbrev can be quite nasty for people having some custom records in the former news table.