[Bug 13810] New: Prevent updatedatabase.pl from breaking because of case insensitive utf8 collation
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 Bug ID: 13810 Summary: Prevent updatedatabase.pl from breaking because of case insensitive utf8 collation Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Architecture, internals, and plumbing Assignee: gmcharlt@gmail.com Reporter: nicolas.legrand@bulac.fr QA Contact: testopia@bugs.koha-community.org When upgrading database from 3.18 to master upgrade Upgrade to 3.19.00.006 (Bug 11944 - Convert DB tables to utf8_unicode_ci) may fail like this: DBD::mysql::db do failed: Duplicate entry 'ACQ-995-a' for key 'PRIMARY' at ./installer/data/mysql/updatedatabase.pl line 9671. This is from table marc_subfield_structure where the keys are made of framework (ACQ), tagfield (995) and tagsubfield (a). If we have a key ACQ-995-A it thinks the entries are duplicates, because the collation is case insensitive. It's rather annoying, since it's legit in mark to have a subfield 'a' different from subfield 'A' inside the same subfield. I propose to default collation on one which is not case insensitive, like utf8_bin. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 --- Comment #1 from Nicolas Legrand <nicolas.legrand@bulac.fr> --- Created attachment 36744 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=36744&action=edit Change default collation to utf8_bin When upgrading database from 3.18 to master upgrade Upgrade to 3.19.00.006 (Bug 11944 - Convert DB tables to utf8_unicode_ci) may fail like this: DBD::mysql::db do failed: Duplicate entry 'ACQ-995-a' for key 'PRIMARY' at ./installer/data/mysql/updatedatabase.pl line 9671. This is from table marc_subfield_structure where the keys are made of framework (ACQ), tagfield (995) and tagsubfield (a). If we have a key ACQ-995-A it thinks the entries are duplicates, because the collation is case insensitive. It's rather annoying, since it's legit in mark to have a subfield 'a' different from subfield 'A' inside the same subfield. utf8_bin prevent updatedatabase.pl from breaking. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 --- Comment #2 from Nicolas Legrand <nicolas.legrand@bulac.fr> --- putting utf8_bin in place of everything else breaks something. The intranet launches the web installer instead of the login prompt. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |katrin.fischer@bsz-bw.de --- Comment #3 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Quite worried about this one as we are also using upper case subfields in some cases (running out of item subfields). Is there a way to fix this? -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 --- Comment #4 from Nicolas Legrand <nicolas.legrand@bulac.fr> --- I'm not on it right now, because I find a way to have a working master branch. Not sure if it has everything it should about collation though. I tried to debug this upgrading step by step, commit to updatedatabase.pl by commit. It appears it ran smoothly without complains. I realised I launched a different version of the dbrev 3.19.00.006: the one from commit 42a8082c8ffd05b3719b79060d81ba77d4f508d3. I then thought I was having trouble with the code added by Martin from commit a303cdec4871d3d3740d419c972eea7faf8e5a8a. I'm not sure it make sense. But I didn't have complains when upgrading and still have my items attached to biblios in intranet (I was losing them when first trying with code from commit a303cdec4871d3d3740d419c972eea7faf8e5a8a). -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 Nicolas Legrand <nicolas.legrand@bulac.fr> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |nicolas.legrand@bulac.fr --- Comment #5 from Nicolas Legrand <nicolas.legrand@bulac.fr> --- Created attachment 37437 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=37437&action=edit Reproduce Bug 13810 This patch create subfields that should break when updating DBRev to 3.19.00.006. Here is the ouptut on a 3.18.x box : ./reproduce_bug_13810.pl Add subfields 666z and 666Z Try update to DBRev 3.19.00.006 for marc_subfield_structure only DBD::mysql::db do failed: Duplicate entry '666-z' for key 'PRIMARY' at ./reproduce_bug_13810.pl line 29. Remove subfields 666z and 666Z -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 --- Comment #6 from Nicolas Legrand <nicolas.legrand@bulac.fr> --- also, I was wrong, it seems commit a303cdec4871d3d3740d419c972eea7faf8e5a8a does also break everything -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |11944 -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 Jonathan Druart <jonathan.druart@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|gmcharlt@gmail.com |jonathan.druart@biblibre.co | |m CC| |jonathan.druart@biblibre.co | |m Status|NEW |ASSIGNED -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 Jonathan Druart <jonathan.druart@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 --- Comment #7 from Jonathan Druart <jonathan.druart@biblibre.com> --- Created attachment 38543 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=38543&action=edit Bug 13810: Change collate for tagsubfield (utf8_bin) Before bug 11944, the marc_subfield_structure.tagsubfield column had a specific collate: utf8_bin. It has been introduced by commit 67e20d82ffdfcd69344ec30696bebc51c00d863c Date: Sun Jun 22 16:35:48 2008 -0500 DB Bump 094 - bug 2268 -- allow mixed case subfield labels in MARC21 by changing db column collation. This change should be kept and Koha should continue to allow and create subfields with the same letter but different case sensitivity. What does this patch: 1/ To prevent the updatedatabase entry 3.19.00.006 to fail if subfields with different case sensitivity already exist in the DB, the table is managed separately from others. 2/ To update DB which have already pass this dbrev, a new entry will be create to update the specific collate for this column. Test plan: 1/ a. With a 3.18 DB, create subfield 'a' and 'A' for the same field b. Execute the updatedatabase.pl script. 3.19.00.006 should not fail anymore 2/ a. With a master DB (3.19.00.006 has already been executed), create subfields 'a' and 'A'. You should get an error. b. Apply this patch, execute the DB entry and try again 2/a. you should be able to create the second subfield. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 --- Comment #8 from Jonathan Druart <jonathan.druart@biblibre.com> --- Nicolas, If you have a look at a 3.18.x branch, you will see that the marc_subfield_structure.tagsubfield had a specific collate. We should keep utf8_unicode_ci for other tables and columns, so I suggest to set back utf8_bin for this column. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 --- Comment #9 from Jonathan Druart <jonathan.druart@biblibre.com> --- Please obsolete your patch if you agree with mine. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 Jonathan Druart <jonathan.druart@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |tomascohen@gmail.com Severity|normal |major --- Comment #10 from Jonathan Druart <jonathan.druart@biblibre.com> --- This issue could cause failures when upgrading from 3.18 to 3.20, I am raising the priority. It should be fixed before the 3.20 release. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 Jonathan Druart <jonathan.druart@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #38543|0 |1 is obsolete| | --- Comment #11 from Jonathan Druart <jonathan.druart@biblibre.com> --- Created attachment 38562 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=38562&action=edit Bug 13810: Change collate for tagsubfield (utf8_bin) Before bug 11944, the marc_subfield_structure.tagsubfield column had a specific collate: utf8_bin. It has been introduced by commit 67e20d82ffdfcd69344ec30696bebc51c00d863c Date: Sun Jun 22 16:35:48 2008 -0500 DB Bump 094 - bug 2268 -- allow mixed case subfield labels in MARC21 by changing db column collation. This change should be kept and Koha should continue to allow and create subfields with the same letter but different case sensitivity. What does this patch: 1/ To prevent the updatedatabase entry 3.19.00.006 to fail if subfields with different case sensitivity already exist in the DB, the table is managed separately from others. 2/ To update DB which have already pass this dbrev, a new entry will be create to update the specific collate for this column. Test plan: 1/ a. With a 3.18 DB, create subfield 'a' and 'A' for the same field b. Execute the updatedatabase.pl script. 3.19.00.006 should not fail anymore 2/ a. With a master DB (3.19.00.006 has already been executed), create subfields 'a' and 'A'. You should get an error. b. Apply this patch, execute the DB entry and try again 2/a. you should be able to create the second subfield. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|major |blocker --- Comment #12 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- I agree - updating the priority some more. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 Nicolas Legrand <nicolas.legrand@bulac.fr> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #36744|0 |1 is obsolete| | --- Comment #13 from Nicolas Legrand <nicolas.legrand@bulac.fr> --- Comment on attachment 36744 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=36744 Change default collation to utf8_bin Jonathan's patch seems far better. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 Nicolas Legrand <nicolas.legrand@bulac.fr> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 Nicolas Legrand <nicolas.legrand@bulac.fr> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #38562|0 |1 is obsolete| | --- Comment #14 from Nicolas Legrand <nicolas.legrand@bulac.fr> --- Created attachment 38664 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=38664&action=edit Bug 13810: Change collate for tagsubfield (utf8_bin) Before bug 11944, the marc_subfield_structure.tagsubfield column had a specific collate: utf8_bin. It has been introduced by commit 67e20d82ffdfcd69344ec30696bebc51c00d863c Date: Sun Jun 22 16:35:48 2008 -0500 DB Bump 094 - bug 2268 -- allow mixed case subfield labels in MARC21 by changing db column collation. This change should be kept and Koha should continue to allow and create subfields with the same letter but different case sensitivity. What does this patch: 1/ To prevent the updatedatabase entry 3.19.00.006 to fail if subfields with different case sensitivity already exist in the DB, the table is managed separately from others. 2/ To update DB which have already pass this dbrev, a new entry will be create to update the specific collate for this column. Test plan: 1/ a. With a 3.18 DB, create subfield 'a' and 'A' for the same field b. Execute the updatedatabase.pl script. 3.19.00.006 should not fail anymore 2/ a. With a master DB (3.19.00.006 has already been executed), create subfields 'a' and 'A'. You should get an error. b. Apply this patch, execute the DB entry and try again 2/a. you should be able to create the second subfield. Signed-off-by: Nicolas Legrand <nicolas.legrand@bulac.fr> -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 --- Comment #15 from Nicolas Legrand <nicolas.legrand@bulac.fr> --- Note I've only done test plan 1, I'm working on a base that was breaking, I don't have the one to try to break :). Thanks a lot for the patch ! -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 --- Comment #16 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- A first thought: Should we also take care of the auth_subfield_structure table? -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 --- Comment #17 from Jonathan Druart <jonathan.druart@biblibre.com> --- (In reply to Katrin Fischer from comment #16)
A first thought: Should we also take care of the auth_subfield_structure table?
The only place where the collate utf8_bin was used before bug 11944 was on marc_subfield_structure.tagsubfield. If we want to change it for auth_subfield_structure.tagsubfield it won't be a bugfix but an enh and should be done in another bug report. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 --- Comment #18 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Hi Jonathan, cool - I didn't check. This should bring us back to the before-state then. -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA Patch complexity|--- |Medium patch -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #38664|0 |1 is obsolete| | --- Comment #19 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Created attachment 38793 --> http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=38793&action=edit [PASSED QA] Bug 13810: Change collate for tagsubfield (utf8_bin) Before bug 11944, the marc_subfield_structure.tagsubfield column had a specific collate: utf8_bin. It has been introduced by commit 67e20d82ffdfcd69344ec30696bebc51c00d863c Date: Sun Jun 22 16:35:48 2008 -0500 DB Bump 094 - bug 2268 -- allow mixed case subfield labels in MARC21 by changing db column collation. This change should be kept and Koha should continue to allow and create subfields with the same letter but different case sensitivity. What does this patch: 1/ To prevent the updatedatabase entry 3.19.00.006 to fail if subfields with different case sensitivity already exist in the DB, the table is managed separately from others. 2/ To update DB which have already pass this dbrev, a new entry will be create to update the specific collate for this column. Test plan: 1/ a. With a 3.18 DB, create subfield 'a' and 'A' for the same field b. Execute the updatedatabase.pl script. 3.19.00.006 should not fail anymore 2/ a. With a master DB (3.19.00.006 has already been executed), create subfields 'a' and 'A'. You should get an error. b. Apply this patch, execute the DB entry and try again 2/a. you should be able to create the second subfield. Signed-off-by: Nicolas Legrand <nicolas.legrand@bulac.fr> Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de> -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to Master --- Comment #20 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Fix pushed to master. Thanks Jonathan! -- You are receiving this mail because: You are watching all bug changes.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13810 Bug 13810 depends on bug 11944, which changed state. Bug 11944 Summary: Cleanup Koha UTF-8 http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11944 What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to Master |RESOLVED Resolution|--- |FIXED -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org