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.
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.
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?
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.
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>
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.
Could it be related to https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29641 ?
I think it is the same (better described). I hadn't marked as a duplicate yet, but did link them.
*** Bug 29641 has been marked as a duplicate of this bug. ***
Upping severity, this is blocker for 21.11.01
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.
(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.
(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?
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>
Created attachment 128435 [details] [review] Bug 29631: (QA follow-up) Add missing cleanups Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
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.
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?
(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.
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
Pushed to master for 22.05, thanks to everybody involved [U+1F984]
*** Bug 29715 has been marked as a duplicate of this bug. ***
Pushed to 21.11.x for 21.11.01
Missing dependency, not backported to 21.05
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
(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.