Bug 29631 - 21.06.000.12 may fail
Summary: 21.06.000.12 may fail
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: unspecified
Hardware: All All
: P5 - low blocker (vote)
Assignee: Jonathan Druart
QA Contact: Tomás Cohen Arazi
URL:
Keywords: rel_21_11_candidate
: 29641 29715 (view as bug list)
Depends on: 15067
Blocks:
  Show dependency treegraph
 
Reported: 2021-12-03 10:41 UTC by Jonathan Druart
Modified: 2021-12-19 10:30 UTC (History)
10 users (show)

See Also:
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


Attachments
Bug 29631: Prevent uniq_lang creation to fail (1.46 KB, patch)
2021-12-03 10:44 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 29631: Prevent uniq_lang creation to fail (1.63 KB, patch)
2021-12-03 13:35 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 29631: Prevent uniq_lang creation to fail (1.68 KB, patch)
2021-12-03 23:28 UTC, David Nind
Details | Diff | Splinter Review
Bug 29631: Prevent uniq_lang creation to fail (1.73 KB, patch)
2021-12-13 10:28 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review
Bug 29631: (QA follow-up) Add missing cleanups (1.84 KB, patch)
2021-12-13 10:28 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
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.