Summary: | The correct COLLATE is not set to all DB tables | ||
---|---|---|---|
Product: | Koha | Reporter: | Jonathan Druart <jonathan.druart> |
Component: | Architecture, internals, and plumbing | Assignee: | Jonathan Druart <jonathan.druart> |
Status: | CLOSED FIXED | QA Contact: | Testopia <testopia> |
Severity: | blocker | ||
Priority: | P5 - low | CC: | dpavlin, kyle, martin.renvoize, paola.rossi, tomascohen |
Version: | Main | ||
Hardware: | All | ||
OS: | All | ||
See Also: | http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=11425 | ||
GIT URL: | Change sponsored?: | --- | |
Patch complexity: | --- | Documentation contact: | |
Documentation submission: | Text to go in the release notes: | ||
Version(s) released in: | Circulation function: | ||
Bug Depends on: | 11944, 13596 | ||
Bug Blocks: | |||
Attachments: |
Bug 13577: Add the utf8_unicode_ci COLLATE to all tables
[SIGNED OFF] Bug 13577: Add the utf8_unicode_ci COLLATE to all tables [PASSED QA] Bug 13577: Add the utf8_unicode_ci COLLATE to all tables BUG 7134 [QA Followup]: Prevent utf8mb4 collation change |
Description
Jonathan Druart
2015-01-14 08:48:25 UTC
Created attachment 35176 [details] [review] Bug 13577: Add the utf8_unicode_ci COLLATE to all tables Bug 11944 added the COLLATE=utf8_unicode_ci to all DB tables. But some new tables have been created between the write of the patch set and the push to master. So these new tables don't have the correct collate. Test plan: 0/ Reproduce the error: mysql> create database koha_test CHARACTER SET utf8 COLLATE utf8_unicode_ci; mysql koha_test < installer/data/mysql/kohastructure.sql It will boom with: ERROR 1005 (HY000) at line 3493: Can't create table `koha_test`.`items_search_fields` (errno: 150 "Foreign key constraint is incorrectly formed") 1/ Apply the patch, destroy the DB and recreate it: mysql> create database koha_test CHARACTER SET utf8 COLLATE utf8_unicode_ci; mysql> drop database koha_test; mysql koha_test < installer/data/mysql/kohastructure.sql All tables should be inserted without any error. Created attachment 35182 [details] [review] [SIGNED OFF] Bug 13577: Add the utf8_unicode_ci COLLATE to all tables I've applied this patch against master 3.19.00.006 Everything's OK. I pass the patch to "Signed Off" status. Created attachment 35333 [details] [review] [PASSED QA] Bug 13577: Add the utf8_unicode_ci COLLATE to all tables Bug 11944 added the COLLATE=utf8_unicode_ci to all DB tables. But some new tables have been created between the write of the patch set and the push to master. So these new tables don't have the correct collate. Test plan: 0/ Reproduce the error: mysql> create database koha_test CHARACTER SET utf8 COLLATE utf8_unicode_ci; mysql koha_test < installer/data/mysql/kohastructure.sql It will boom with: ERROR 1005 (HY000) at line 3493: Can't create table `koha_test`.`items_search_fields` (errno: 150 "Foreign key constraint is incorrectly formed") 1/ Apply the patch, destroy the DB and recreate it: mysql> create database koha_test CHARACTER SET utf8 COLLATE utf8_unicode_ci; mysql> drop database koha_test; mysql koha_test < installer/data/mysql/kohastructure.sql All tables should be inserted without any error. Signed-off-by: Paola Rossi <paola.rossi@cineca.it> Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> QA Question: Should we also be fixing this via updatedatabase.pl? Hm, I think if the update is ok between stable releases and as the utf-8 patches have just been pushed, it's probably ok to skip the database update here. Patch pushed to master. Thanks Jonathan! I wish I'd spotted this before it had been pushed.. oops. I would have said, can we do a check on whether tables are _mb4 before running the change.. I know we've got mb4 set which give minor advantages over not _mb4 encoding.. but mroe importantly, if you've already got a utf8 database (especialyl with _mb4) you shuld nto have to run the db update at all, thus saving allot of time at upgrade time. Also,.. i believe the use of ->tables is deprecated? https://metacpan.org/pod/DBI#tables Created attachment 35384 [details] [review] BUG 7134 [QA Followup]: Prevent utf8mb4 collation change utf8mb4 is a superset of utf8 adn thus is compatible. We should not force an entire db change upon people who have proactively set utf8 encoding before now. This patch aslo removed the deprecaed ->tables stanze in favour of the table_info stanze. Wrong bug.. my bad. Making a new one now. We have a minor problem with databases created with When trying to access it with mysql client from command line, I get following error in where: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' However, fix is simple: mysql> alter database koha collate = 'utf8_unicode_ci' ; I'm wondering if we should alter database collate also to avoid problems like this. (In reply to Dobrica Pavlinusic from comment #11) Dobrica, could you please detail how did you get this error? |