[Koha-devel] kohastructure.sql : columns with COLLATE

[Paul A]

On 2019-09-27 10:27 a.m., Fridolin SOMERS wrote:
> Hi
> 
> I'm working on DB structure compare.
> I note that in installer/data/mysql/kohastructure.sql, a few columns 
> define CHARACTER SET and/or COLLATE, with same values as table.
> 
> Is this usefull or should we remove it ?

Might I assume that this is in a context of upgrade rather than new 
install? If not, the following is perhaps irrelevant.

In the "old days", (+/- 5 years ago, Debian 7, Ubuntu 14; MySQL 5.7 -- 
all "thereabouts"), my[sql].cnf in its various formats and paths used to 
include:
    # ensure charsets.
    character-set-server=utf8
    collation-server=utf8_general_ci

This has been dropped in more recent releases, corresponding to 
introduction dates of utf8mb4 (most importantly 3- to 4-bit encoding 
which can seriously impact some field lengths) -- core functionality is 
perhaps presumed.

Specificity may well be useful for 'international' and 'backwards 
compatibility' reasons -- both of which are important to Koha where 
admins may be very late in upgrading os/MySQL/Koha versions. The use of 
COLLATE with a specific CHARACTER SET should|could|might find (various 
find|sorting algorithms) existing, relevant data -- despite the fact 
that Koha appears to respect a system (Debian, Ubuntu) wide 'plain' utf8.

However, MySQL (8+) and InnoDB may not be totally aligned (I have no 
practical knowledge of MariaDB), and the char sets utf8, utf8mb4, 
utf8mb4_general_ci, utf8mb4_unicode_ci (maybe more) are an ongoing 
source of improvement and discussion.

Surely Koha should not remove this [not-neccessarily-redundant] back-stop.

Best -- Paul
> 
> $ grep  'COLLATE' installer/data/mysql/kohastructure.sql  | grep -v 
> ENGIN | grep 'utf8mb4_unicode_ci'
>    `category_name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL 
> DEFAULT '',
>    `font` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT 
> NULL DEFAULT 'TR',
>    `text_justify` char(1) CHARACTER SET utf8mb4 COLLATE 
> utf8mb4_unicode_ci NOT NULL DEFAULT 'L',
>    `auto_renew_error` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT 
> NULL, -- automatic renewal error
>    `auto_renew_error` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT 
> NULL, -- automatic renewal error
>    `suppliers_report` MEDIUMTEXT COLLATE utf8mb4_unicode_ci, -- reports 
> received from suppliers
>        entity varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
>        code varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
>        lang varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL, -- could be 
> a foreign key
>        translation MEDIUMTEXT COLLATE utf8mb4_unicode_ci,
>    `branchcode` varchar(10) CHARACTER SET utf8mb4 COLLATE 
> utf8mb4_unicode_ci DEFAULT NULL,
>