kohastructure.sql : columns with COLLATE
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 ? $ 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, -- Fridolin SOMERS <fridolin.somers@biblibre.com> BibLibre, France - software and system maintainer
I think it doesn't really make sense. There are cases in which the general table collation is utf8mb4_unicode_ci and some columns need utf8mb4_binary. I think those are the only meaningful ones. El vie., 27 sept. 2019 a las 11:27, Fridolin SOMERS (< fridolin.somers@biblibre.com>) escribió:
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 ?
$ 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,
-- Fridolin SOMERS <fridolin.somers@biblibre.com> BibLibre, France - software and system maintainer _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
-- Tomás Cohen Arazi Theke Solutions (http://theke.io) ✆ +54 9351 3513384 GPG: B2F3C15F
I happened to spot the same only yesterday and wondered about doing such a tidy up too.. I'd agree with Tomas, where the collate matches the table I'd drop it from the column.. I didn't go ahead and do it yet as I didn't want to introduce any pain for RMaints without discussing it first. *Martin Renvoize* <https://www.ptfs-europe.com> Development Team Manager *Phone:* +44 (0) 1483 378728 *Mobile:* +44 (0) 7725 985 636 *Email:* martin.renvoize@ptfs-europe.com *Fax:* +44 (0) 800 756 6384 www.ptfs-europe.com Registered in the United Kingdom No. 06416372 VAT Reg No. 925 7211 30 The information contained in this email message may be privileged, confidential and protected from disclosure. If you are not the intended recipient, any dissemination, distribution or copying is strictly prohibited. If you think that you have received this email message in error, please email the sender at info@ptfs-europe.com On Fri, 27 Sep 2019 at 18:06, Tomas Cohen Arazi <tomascohen@gmail.com> wrote:
I think it doesn't really make sense. There are cases in which the general table collation is utf8mb4_unicode_ci and some columns need utf8mb4_binary. I think those are the only meaningful ones.
El vie., 27 sept. 2019 a las 11:27, Fridolin SOMERS (< fridolin.somers@biblibre.com>) escribió:
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 ?
$ 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,
-- Fridolin SOMERS <fridolin.somers@biblibre.com> BibLibre, France - software and system maintainer _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
-- Tomás Cohen Arazi Theke Solutions (http://theke.io) ✆ +54 9351 3513384 GPG: B2F3C15F _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
Also see https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=20269 <https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=20269> -- Pasi Kallinen Koha-Suomi oy +358 400 174 235 Sep 27, 2019, 20:05 by tomascohen@gmail.com:
I think it doesn't really make sense. There are cases in which the general table collation is utf8mb4_unicode_ci and some columns need utf8mb4_binary. I think those are the only meaningful ones.
El vie., 27 sept. 2019 a las 11:27, Fridolin SOMERS (<> fridolin.somers@biblibre.com <mailto:fridolin.somers@biblibre.com>> >) escribió:
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 ?
$ 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,
-- Fridolin SOMERS <>> fridolin.somers@biblibre.com <mailto:fridolin.somers@biblibre.com>>> > BibLibre, France - software and system maintainer _______________________________________________ Koha-devel mailing list
Koha-devel@lists.koha-community.org <mailto:Koha-devel@lists.koha-community.org> https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel <https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel> website : >> http://www.koha-community.org/ <http://www.koha-community.org/> git : >> http://git.koha-community.org/ <http://git.koha-community.org/> bugs : >> http://bugs.koha-community.org/ <http://bugs.koha-community.org/>
-- Tomás Cohen Arazi Theke Solutions (> http://theke.io <http://theke.io/>> ) ✆ +54 9351 3513384 GPG: B2F3C15F
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,
participants (5)
-
Fridolin SOMERS -
Pasi Kallinen -
Paul A -
Renvoize, Martin -
Tomas Cohen Arazi