Bug 29778

Summary: Deleting additional_contents leaves entries for additional languages
Product: Koha Reporter: Katrin Fischer <katrin.fischer>
Component: OPACAssignee: Jonathan Druart <jonathan.druart>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: critical    
Priority: P5 - low CC: andrewfh, fridolin.somers, jonathan.druart, kyle, m.de.rooy
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29782
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29828
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
Bug Depends on: 24387    
Bug Blocks: 29805    
Attachments: Bug 29778: Prevent orphan additional contents
Bug 29778: Remove DBMS error
Bug 29778: Prevent orphan additional contents
Bug 29778: Remove DBMS error
Bug 29778: Prevent orphan additional contents
Bug 29778: Remove DBMS error

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.