Glossary sorting mixed up with Norwegian char 'A' and 'Å'. Other Norwegian char (Ø,Æ) working well.
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.
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?
Another attachment with glossary sorting.
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:
Just replace the Unicode with danish.
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.
Takk for sist.
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?
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?
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
after importing the db, make sure that it is converted to Barracuda with support for large prefix:
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
php admin/cli/mysql_collation.php --collation=utf8mb4_danish_ci
First of all thanks for continues helping
Database query done!
First script done!
Next one saying 'Not necessary'
And final one has the same error.
How to debug to find where is the problem?
Found the problem.
$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.
This query is enough. $sql = "SHOW COLLATION WHERE Collation LIKE 'utf8%'";
How to report for it?
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).
Should I change it in our linux server /etc/mysql/my.cnf:
default-character-set = utf8mb4
innodb_file_format = Barracuda
innodb_file_per_table = 1
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
default-character-set = utf8mb4
Will replace unicode with danish.