Bug 29631

Summary: 21.06.000.12 may fail
Product: Koha Reporter: Jonathan Druart <jonathan.druart+koha>
Component: Architecture, internals, and plumbingAssignee: Jonathan Druart <jonathan.druart+koha>
Status: RESOLVED FIXED QA Contact: Tomás Cohen Arazi <tomascohen>
Severity: blocker    
Priority: P5 - low CC: andrew, david, egpetridis, fridolin.somers, hans.palsson, kyle, mhby87, mtj, nazrul.it.puc, nick, tomascohen
Version: unspecifiedKeywords: rel_21_11_candidate
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=20754
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29641
Change sponsored?: --- Patch complexity: ---
Text to go in the release notes:
This fixes an issue when upgrading from 21.05.x to 21.11 - the uniq_lang unique key is failing to be created because several rows with the same subtag and type exist in database table language_subtag_registry.
Version(s) released in:
22.05.00,21.11.01
Bug Depends on: 15067    
Bug Blocks:    
Attachments: Bug 29631: Prevent uniq_lang creation to fail
Bug 29631: Prevent uniq_lang creation to fail
Bug 29631: Prevent uniq_lang creation to fail
Bug 29631: Prevent uniq_lang creation to fail
Bug 29631: (QA follow-up) Add missing cleanups

Description Jonathan Druart 2021-12-03 10:41:01 UTC
We got 2 reports of this problem in the last 24h, uniq_lang unique key is failing to be created because several rows with the same (subtag, type) exist in DB.

I have no idea how this is possible, but apparently it is.
Comment 1 Jonathan Druart 2021-12-03 10:44:04 UTC
Created attachment 128209 [details] [review]
Bug 29631: Prevent uniq_lang creation to fail

We got 2 reports of this problem in the last 24h, uniq_lang unique key is failing to be created because several rows with the same (subtag, type) exist in DB.

I have no idea how this is possible, but apparently it is.
Comment 2 Jonathan Druart 2021-12-03 10:45:03 UTC
The SQL query does not seem very good, and it won't work if there are more than 2 entries (only 1 will be deleted).

Could someone help me to get something better?
Comment 3 Jonathan Druart 2021-12-03 13:35:46 UTC
Created attachment 128214 [details] [review]
Bug 29631: Prevent uniq_lang creation to fail

We got 2 reports of this problem in the last 24h, uniq_lang unique key is failing to be created because several rows with the same (subtag, type) exist in DB.

I have no idea how this is possible, but apparently it is.

Test plan:
Checkout a commit before 21.06.00.012
reset_all
Create duplicate in language_subtag_registry
> insert into language_subtag_registry(subtag, type, description) values('IN', 'region', 'India');
Checkout master+this patch, updatedatabase
=> Only 1 IN-region exists in the DB, the last one.
Comment 4 David Nind 2021-12-03 23:28:18 UTC
Created attachment 128238 [details] [review]
Bug 29631: Prevent uniq_lang creation to fail

We got 2 reports of this problem in the last 24h, uniq_lang unique key is failing to be created because several rows with the same (subtag, type) exist in DB.

I have no idea how this is possible, but apparently it is.

Test plan:
Checkout a commit before 21.06.00.012
reset_all
Create duplicate in language_subtag_registry
> insert into language_subtag_registry(subtag, type, description) values('IN', 'region', 'India');
Checkout master+this patch, updatedatabase
=> Only 1 IN-region exists in the DB, the last one.

Signed-off-by: David Nind <david@davidnind.com>
Comment 5 David Nind 2021-12-03 23:29:57 UTC
I hope I have tested this correctly, as I often have problems with KTD and changing to previous versions rather than master.

In this case, for step 1 of the test plan I checked out v21.05.07.
Comment 6 Manos PETRIDIS 2021-12-04 18:00:52 UTC
Could it be related to https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29641 ?
Comment 7 David Nind 2021-12-04 23:11:42 UTC
I think it is the same (better described). I hadn't marked as a duplicate yet, but did link them.
Comment 8 Katrin Fischer 2021-12-06 00:14:44 UTC
*** Bug 29641 has been marked as a duplicate of this bug. ***
Comment 9 Jonathan Druart 2021-12-10 14:55:50 UTC
Upping severity, this is blocker for 21.11.01
Comment 10 Katrin Fischer 2021-12-12 22:37:18 UTC
What can we do to help people that have already run into the issue? There was another report on the mailing list from Beda Szukics.
Comment 11 Jonathan Druart 2021-12-13 09:10:43 UTC
(In reply to Katrin Fischer from comment #10)
> What can we do to help people that have already run into the issue? There
> was another report on the mailing list from Beda Szukics.

They should either remove duplicates manually or run the SQL query provided by this patch. But it would be better to get a QA stamp on it before telling people to run it on production installations.
Comment 12 Tomás Cohen Arazi 2021-12-13 10:14:05 UTC
(In reply to Jonathan Druart from comment #11)
> (In reply to Katrin Fischer from comment #10)
> > What can we do to help people that have already run into the issue? There
> > was another report on the mailing list from Beda Szukics.
> 
> They should either remove duplicates manually or run the SQL query provided
> by this patch. But it would be better to get a QA stamp on it before telling
> people to run it on production installations.

There are 3 UNIQUE constraints that could fail, added on bug 15067. This patch covers one. Can you provide the others?
Comment 13 Tomás Cohen Arazi 2021-12-13 10:28:04 UTC
Created attachment 128434 [details] [review]
Bug 29631: Prevent uniq_lang creation to fail

We got 2 reports of this problem in the last 24h, uniq_lang unique key is failing to be created because several rows with the same (subtag, type) exist in DB.

I have no idea how this is possible, but apparently it is.

Test plan:
Checkout a commit before 21.06.00.012
reset_all
Create duplicate in language_subtag_registry
> insert into language_subtag_registry(subtag, type, description) values('IN', 'region', 'India');
Checkout master+this patch, updatedatabase
=> Only 1 IN-region exists in the DB, the last one.

Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 14 Tomás Cohen Arazi 2021-12-13 10:28:09 UTC
Created attachment 128435 [details] [review]
Bug 29631: (QA follow-up) Add missing cleanups

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 15 Tomás Cohen Arazi 2021-12-13 10:30:49 UTC
I ran the original INSERTs (on the three tables) several times before running the db_rev, and it made the original patch fail (of course).

My follow-up covers that.

It still feels we should add:
- FK constraints between this tables
- UNIQUE to language_script_mapping
- UNIQUE to language_script_bidi

Those, on a separate bug of course.
Comment 16 Manos PETRIDIS 2021-12-13 11:44:56 UTC
Aren't language_descriptions.id and language_rfc4646_to_iso639.id already referenced anywhere in koha? Shouldn't there be some previous statements, where use is determined (if any) and existing references updated so that they would only point to the IDs that are to remain?
Comment 17 Tomás Cohen Arazi 2021-12-13 11:49:50 UTC
(In reply to Manos PETRIDIS from comment #16)
> Aren't language_descriptions.id and language_rfc4646_to_iso639.id already
> referenced anywhere in koha? Shouldn't there be some previous statements,
> where use is determined (if any) and existing references updated so that
> they would only point to the IDs that are to remain?

I did a git grep for language_descriptions and language_rfc4646_to_iso639 and no, couldn't find any references to the ids. They are always queried by subtag, lang and type.

Good question, though.
Comment 18 Manos PETRIDIS 2021-12-13 15:04:31 UTC
As mine was one of the installations affected by this bug, I can verify that after running the following statements, web istaller completed successfully.

 DELETE a
 FROM language_subtag_registry AS a, language_subtag_registry AS b
 WHERE a.id < b.id
 AND a.subtag IS NOT NULL
 AND a.subtag=b.subtag
 AND a.type=b.type;

 DELETE a
 FROM language_descriptions AS a, language_descriptions AS b
 WHERE a.id < b.id
 AND a.subtag IS NOT NULL
 AND a.subtag=b.subtag
 AND a.lang IS NOT NULL
 AND a.lang=b.lang
 AND a.type=b.type;

 DELETE a
 FROM language_rfc4646_to_iso639 AS a, language_rfc4646_to_iso639 AS b
 WHERE a.id < b.id
 AND a.rfc4646_subtag IS NOT NULL
 AND a.rfc4646_subtag=b.rfc4646_subtag
 AND a.iso639_2_code=b.iso639_2_code;

Thank you all for your help in resolving this issue. 

Manos Petridis
Comment 19 Fridolin Somers 2021-12-14 01:46:16 UTC
Pushed to master for 22.05, thanks to everybody involved 🦄
Comment 20 Fridolin Somers 2021-12-16 20:52:30 UTC
*** Bug 29715 has been marked as a duplicate of this bug. ***
Comment 21 Kyle M Hall 2021-12-17 13:45:03 UTC
Pushed to 21.11.x for 21.11.01
Comment 22 Andrew Fuerste-Henry 2021-12-17 20:17:34 UTC
Missing dependency, not backported to 21.05
Comment 23 MD NAZRUL ISLAM 2021-12-19 05:31:34 UTC
 Upgrade to 21.06.00.012 [11:07:40]: Bug 15067 - Add missing languages
ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Duplicate entry 'IN-region' for key 'uniq_lang' at /usr/share/koha/lib/C4/Installer.pm line 738
Comment 24 Katrin Fischer 2021-12-19 10:30:43 UTC
(In reply to MD NAZRUL ISLAM from comment #23)
>  Upgrade to 21.06.00.012 [11:07:40]: Bug 15067 - Add missing languages
> ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Duplicate entry
> 'IN-region' for key 'uniq_lang' at /usr/share/koha/lib/C4/Installer.pm line
> 738

Please read through the bug - there is no need to post the issue again. The SQL statements in comment#18 are the fix.