Generell diskusjon/Sosialt forum

Sorting mixed up with Norwegian char 'Å' and 'A'

 
Picture of Alf Martin Johnsen
Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
Translators

Hi,

so close: The correct collation is utf8_danish_ci or utf8mb4_danish_ci

Utf8mb4...  is demanded on newer Moodle versions.

Picture of Mohammad kabir
Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
 

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? 


Picture of Mohammad kabir
Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
 

Another attachment with glossary sorting.


Picture of Alf Martin Johnsen
Svar: Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
Translators

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.


Picture of Mohammad kabir
Re: Svar: Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
 

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 smile


Picture of Alf Martin Johnsen
Svar: Re: Svar: Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
Translators

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. wink

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.

Picture of Mohammad kabir
Re: Svar: Re: Svar: Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
 

Thanks Alf. Its helpful and I appreciate it.

Picture of Mohammad kabir
Re: Svar: Re: Svar: Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
 

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.

Picture of Alf Martin Johnsen
Svar: Re: Svar: Re: Svar: Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
Translators

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.

wink

Picture of Mohammad kabir
Re: Svar: Re: Svar: Re: Svar: Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
 

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 smile

Picture of Alf Martin Johnsen
Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
Translators

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.

Picture of Mohammad kabir
Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
 

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


Picture of Alf Martin Johnsen
Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
Translators

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

Picture of Mohammad kabir
Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
 

Hi Alf,

First of all thanks for continues helping smile

Database query done! 

For script:

First script done!

Next one saying 'Not necessary'

And final one has the same error.


Picture of Mohammad kabir
Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
 

Hi Alf,

How to debug to find where is the problem?

Picture of Mohammad kabir
Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
 

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 smile  Sorting is working now. Thanks for help.

Picture of Mohammad kabir
Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
 

Hi Alf,

This query is enough. $sql = "SHOW COLLATION WHERE Collation LIKE 'utf8%'"; 

How to report for it?

Picture of Alf Martin Johnsen
Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
Translators

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?

Picture of Mohammad kabir
Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
 

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).


Picture of Mohammad kabir
Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
 

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.

Picture of Alf Martin Johnsen
Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
Translators

Good plan, but you still have to change the  encoding in the actual db. Look at the manual for your version.

Picture of Mohammad kabir
Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Re: Svar: Sorting mixed up with Norwegian char 'Å' and 'A'
 

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