Glossary sorting mixed up with Norwegian char 'A' and 'Å'. Other Norwegian char (Ø,Æ) working well.
https://tracker.moodle.org/browse/MDL-21364
From above link, I found its Database issue.
What is the best MySql collation for Norsk language ?
I tried with mysql collation : utf8_unicode_ci, utf8_general_ci, latin1_swedish_ci. Nothing helps for sorting A and Å. Welcome if anybody has good idea.
Thank you.
Hi,
so close: The correct collation is utf8_danish_ci or utf8mb4_danish_ci
Utf8mb4... is demanded on newer Moodle versions.
Hi Alf,
Version : Moodle 3.2.1
Thanks for your answer. Still sort does not work. I tested in localhost, not in linux server yet. Don't think it will work in linux server as well. Please see the attachment.
Even though sort is not working, I should change the collation to utf8mb4_danish_ci, right?
It is not enough to change collation on the dB alone, you have to run a script that change collation in all tables and all fields. THis is a script you can run from the command line and follow the instructions here:
https://docs.moodle.org/32/en/MySQL_full_unicode_support
Just replace the Unicode with danish.
Hi Alf,
After follow instruction, its working fine in localhost now. Tusen takk.
Question: If I change it on production ( little afraid as it's big ), is there any possibility to loose any information or generate any kind of problem? Do I need to test a lot ?
Once again thanks a lot
First of all: Always run a full SQL dump so you can roll back if something went wrong. NEVER do db operations like this without a backup. Think that you are a line dancer trying to cross Niagara.
Next: Be careful and close the site before you begin. The conversion job takes very long time and blocks the site totally when running. On very big sites you must calculate 1-2 hours, depending on amount of users, courses etc. and the db-server performance.
Thanks Alf. Its helpful and I appreciate it.
Hi Alf,
Takk for sist.
https://docs.moodle.org/32/en/MySQL_full_unicode_support
According to above URL below script have to run in linux server:
$ php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci
Which path should I run this line? Just run anywhere?
If I run that line ( behind the line there is script ) in linux will it change collation in all tables and all fields?
Thank you.
Just go to you root Moodle folder on the same level as config.php resides. The script will change the collation for all tables and in all fields.
Hi Alf,
It will take database name from config.php file, right?
The effect will only be a particular database(configured in config.php) not whole mysql server or any other database, right?
Tusen takk
You are right.
The script only convert the db it has access to, given in the config.php.
BTW; if you deal with Norwegian sorting, you should use the utf8mb4_danish_ci collation to cover Å and A in the correct sort order.
Hi Alf,
What I did is
1. Create empty database with desired charset and collation.
CREATE DATABASE beta3_nafo
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_danish_ci;
2. Dump existing database with
mysqldump -uusername -ppassword -c -e --default-character-set=utf8mb4 --single-transaction --skip-set-charset --add-drop-database -B dbname > dump.sql
3. Import the dump database to newly created database
4. Run : php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci
and getting error.
5. CFG->dboptions Array in config.php
Ok,
after importing the db, make sure that it is converted to Barracuda with support for large prefix:
USE yourdbname;
SET SESSION sql_mode=STRICT_ALL_TABLES;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_large_prefix=1;
SET GLOBAL innodb_file_format=Barracuda;
Then the script:
php admin/cli/mysql_compressed_rows.php --list
php admin/cli/mysql_compressed_rows.php --fix
At last:
php admin/cli/mysql_collation.php --collation=utf8mb4_danish_ci
Hi Alf,
First of all thanks for continues helping
Database query done!
For script:
First script done!
Next one saying 'Not necessary'
And final one has the same error.
Hi Alf,
How to debug to find where is the problem?
Hi Alf,
Found the problem.
www/admin/cli/mysql_collation.php
L:183
$sql = "SHOW COLLATION WHERE Collation LIKE 'utf8\_%' AND Charset = 'utf8'";This query never return utf8mb4_danish_ci
So temporarily I changed it to
$sql = "SHOW COLLATION WHERE Collation LIKE 'utf8mb4\_%'";
Then it started conversion and all of my table got converted.
I think moodle should fix it. You can report Sorting is working now. Thanks for help.
Hi Alf,
This query is enough. $sql = "SHOW COLLATION WHERE Collation LIKE 'utf8%'";
How to report for it?
I don´t think this is something to report.
To me it seems that you might have forgotten to change the db encoding from utf8 to utf8mb4 before you changed the collation?
Hi Alf,
I done collation changed in beta-version. Will do in prod. I want to do it in right way.
1. Before change collation means before run script, I have to change encoding from utf8 to utf8mb4. How to do that?
2. I haven't done any server side configuration, Do I need it? ( Just ask because I want to do it in correct way).
Hi Alf,
Should I change it in our linux server /etc/mysql/my.cnf:
[client]
default-character-set = utf8mb4
[mysqld]
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake
[mysql]
default-character-set = utf8mb4
Will replace unicode with danish.
Good plan, but you still have to change the encoding in the actual db. Look at the manual for your version.
Hi Alf,
Googled but didn't find anything about 'encoding mysql db'. Do you talking about charset or set NAMES? Using mysql : 5.7.20
