Patch #19742

RedmineInstall: MySQL: collation_database

Added by @ go2null over 6 years ago. Updated about 2 years ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Documentation
Target version:-

Description

Currently, the code sample creates a database with collation_database=utf8_general_ci. This potentially creates issues1 when sorting some unicode languages. A better approach would be to specify the collation.

- CREATE DATABASE redmine CHARACTER SET utf8;
+ CREATE DATABASE redmine CHARACTER SET utf8 COLLATE utf8_unicode_ci;

[1] http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci

Related issues

Related to Redmine - Defect #18866: MySQL: disappear after 4-Byte UTF-8 New
Related to Redmine - Defect #20143: Mailhandler cannot handle 4-byte characters New
Related to Redmine - Defect #21398: Mysql: 500 server error when submitting 4 bytes utf8 (to ... New
Related to Redmine - Feature #31921: Changes to properly support 4 byte characters (emoji) whe... Closed

History

#1 Updated by Toshi MARUYAMA over 6 years ago

  • Related to Defect #18866: MySQL: disappear after 4-Byte UTF-8 added

#2 Updated by Toshi MARUYAMA over 6 years ago

  • Subject changed from RedmineInstall: Step 2 - implement a best practice for MySQL to RedmineInstall: MySQL: collation_database

#3 Updated by Toshi MARUYAMA over 6 years ago

I don't prefer defining utf8_general_ci or utf8_unicode_ci because many Japanese web site recommends utf8_general_ci because of unacceptable matching HIRAGANA (ひらがな) and KATAKANA (カタカナ).

MySQL 5.5.3 supports utf8mb4_unicode_ci and utf8mb4_general_ci (#18866).

#4 Updated by Toshi MARUYAMA over 6 years ago

#5 Updated by Toshi MARUYAMA over 6 years ago

  • Related to deleted (Defect #18866: MySQL: disappear after 4-Byte UTF-8)

#6 Updated by Toshi MARUYAMA over 6 years ago

#7 Updated by Toshi MARUYAMA over 6 years ago

  • Related to Defect #18866: MySQL: disappear after 4-Byte UTF-8 added

#8 Updated by Toshi MARUYAMA over 6 years ago

  • Related to Defect #20143: Mailhandler cannot handle 4-byte characters added

#9 Updated by @ go2null about 6 years ago

How about the following change:

FROM

MySQL

CREATE DATABASE redmine CHARACTER SET utf8;
CREATE USER 'redmine'@'localhost' IDENTIFIED BY 'my_password';
GRANT ALL PRIVILEGES ON redmine.* TO 'redmine'@'localhost';

For versions of MySQL prior to 5.0.2 - skip the 'create user' step and instead:

GRANT ALL PRIVILEGES ON redmine.* TO 'redmine'@'localhost' IDENTIFIED BY 'my_password';

TO

MySQL

MySQL v5.5.3 and later

CREATE DATABASE redmine CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'redmine'@'localhost' IDENTIFIED BY 'my_password';
GRANT ALL PRIVILEGES ON redmine.* TO 'redmine'@'localhost';

MySQL v5.0.2 and later

CREATE DATABASE redmine CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE USER 'redmine'@'localhost' IDENTIFIED BY 'my_password';
GRANT ALL PRIVILEGES ON redmine.* TO 'redmine'@'localhost';

MySQL v5.0.1 and earlier

CREATE DATABASE redmine CHARACTER SET utf8 COLLATE utf8_unicode_ci;
GRANT ALL PRIVILEGES ON redmine.* TO 'redmine'@'localhost' IDENTIFIED BY 'my_password';

OR

Add the following recommendation:

It is recommended to use CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci for MySQL v5.5.3 and later, and CHARACTER SET utf8 COLLATE utf8_unicode_ci for earlier versions.

#10 Updated by Toshi MARUYAMA almost 6 years ago

  • Related to Defect #21398: Mysql: 500 server error when submitting 4 bytes utf8 (to be saved in the 'notes' field) added

#11 Updated by Marius BALTEANU about 2 years ago

  • Status changed from New to Closed

RedmineInstall was updated in the mean time.

#12 Updated by Marius BALTEANU about 2 years ago

  • Related to Feature #31921: Changes to properly support 4 byte characters (emoji) when database is MySQL added

Also available in: Atom PDF