Bug 13577 - The correct COLLATE is not set to all DB tables
Summary: The correct COLLATE is not set to all DB tables
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: master
Hardware: All All
: P5 - low blocker (vote)
Assignee: Jonathan Druart
QA Contact: Testopia
URL:
Whiteboard:
Keywords:
Depends on: 11944 13596
Blocks:
  Show dependency treegraph
 
Reported: 2015-01-14 08:48 UTC by Jonathan Druart
Modified: 2015-12-03 22:11 UTC (History)
6 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Bot Control: ---
When did the bot last check this:
Who signed the patch off:
Text to go in the release notes:


Attachments
Bug 13577: Add the utf8_unicode_ci COLLATE to all tables (2.89 KB, patch)
2015-01-14 08:58 UTC, Jonathan Druart
Details | Diff | Splinter Review
[SIGNED OFF] Bug 13577: Add the utf8_unicode_ci COLLATE to all tables (3.02 KB, patch)
2015-01-14 12:10 UTC, Paola Rossi
Details | Diff | Splinter Review
[PASSED QA] Bug 13577: Add the utf8_unicode_ci COLLATE to all tables (3.01 KB, patch)
2015-01-16 16:47 UTC, Kyle M Hall
Details | Diff | Splinter Review
BUG 7134 [QA Followup]: Prevent utf8mb4 collation change (1.81 KB, patch)
2015-01-19 13:51 UTC, Martin Renvoize
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Jonathan Druart 2015-01-14 08:48:25 UTC
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.

The error is:
  ERROR 1005 (HY000) at line 3493: Can't create table 'koha.items_search_fields' (errno: 150)
Comment 1 Jonathan Druart 2015-01-14 08:58:12 UTC Comment hidden (obsolete)
Comment 2 Paola Rossi 2015-01-14 12:10:44 UTC Comment hidden (obsolete)
Comment 3 Kyle M Hall 2015-01-16 16:47:15 UTC
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>
Comment 4 Kyle M Hall 2015-01-16 16:48:11 UTC
QA Question: Should we also be fixing this via updatedatabase.pl?
Comment 5 Katrin Fischer 2015-01-16 19:31:13 UTC
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.
Comment 6 Tomás Cohen Arazi 2015-01-17 13:45:11 UTC
Patch pushed to master.

Thanks Jonathan!
Comment 7 Martin Renvoize 2015-01-19 09:06:27 UTC
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.
Comment 8 Martin Renvoize 2015-01-19 09:28:45 UTC
Also,.. i believe the use of ->tables is deprecated?

https://metacpan.org/pod/DBI#tables
Comment 9 Martin Renvoize 2015-01-19 13:51:49 UTC Comment hidden (obsolete)
Comment 10 Martin Renvoize 2015-01-19 15:36:46 UTC
Wrong bug.. my bad. Making a new one now.
Comment 11 Dobrica Pavlinusic 2015-04-22 09:51:11 UTC
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.
Comment 12 Jonathan Druart 2015-04-27 13:23:41 UTC
(In reply to Dobrica Pavlinusic from comment #11)

Dobrica, could you please detail how did you get this error?