Changing Charset

Changing Charset

by Frances Clynes -
Number of replies: 10

Hi,

I upgraded to 3.6.5+ and in the Environment messages I was given the error 

'The current setup of MySQL or MariaDB is using 'utf8'. This character set does not support four byte characters which include some emoji. Trying to use these characters will result in an error when updating a record, and any information being sent to the database will be lost. Please consider changing your settings to 'utf8mb4'. See the documentation for full details.'

Using the cPanel I changed the charset and collation to 'utf8mb4_unicode_ci

While this changed the status to 'OK' in the environment, there are errors in writing to the database - 10.3.24-MariaDB - MariaDB Server. Screenshot attached.

Is it possible to make the change to utf8mb4 via the cPanel?

Attachment errorMessage.png
Average of ratings: -
In reply to Frances Clynes

Re: Changing Charset

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

You have to run the script, admin/cli/mysql_collation.php, at the command line on the server. You should be able to configure SSH access for this within cPanel.

With MariaDB 10.3.24 I wouldn't expect any changes to the configuration settings to be needed.

Make a backup of the database before running the script. The script can take a while (hours) unless the site is small.

Reply if there are issues with this or any questions and we'll try to help.

Average of ratings: -
In reply to Leon Stringer

Re: Changing Charset

by Frances Clynes -

Hi Leon,

Thank you for answering. SSH Access on my cpanel just generates keys - it does not give details of how to access the command line. There was a link to Putty which I tried but that just generates more keys!

Would it be easier just to install a new version of Moodle where I don't have to convert the character set?

Frances


Average of ratings: -
In reply to Frances Clynes

Re: Changing Charset

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

If you install a new version of Moodle with the character set and collation set to utf8mb4 and utf8mb4_unicode_ci respectively then you won't have to convert the character set.

But I'd recommend setting up SSH access as once this is working it's useful if for troubleshooting Moodle if required in the future.

You'll need PuTTY on your computer (assuming this is Microsoft Windows) to connect.

Keys come in a pair: private and public. These are just a long string of letters and numbers, the private key is normally protected by a passphrase (i.e. a password).

The private key goes on your computer and the public key on the server. Either 1) use cPanel to generate the keys and save the private key to your computer, or 2) use PuTTY's key generator (PuTTYgen) and add the public key to cPanel.

Then in PuTTY specify the server's IP address and the private key file to connect, you should be prompted for the passphrase.

It may seem daunting but hopefully you can get it to work.

Average of ratings: -
In reply to Leon Stringer

Re: Changing Charset

by Frances Clynes -

Hi Leon,

I have generated the keys and downloaded Putty. I got two apps - Puttygen which just seems to generate keys, and PSFTP. Is it PSFTP that I should use as the command line?

Thanks for all of this.

Frances

Average of ratings: -
In reply to Frances Clynes

Re: Changing Charset

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

No, there should be a third app: PuTTY itself (file PuTTY.exe). It should show the configuration window when you start it:

PuTTY Configuration window

In this window you specify the server's IP address (or host name), and the private key file to connect.

Average of ratings: -
In reply to Leon Stringer

Re: Changing Charset

by Frances Clynes -
Downloaded it now.
Is there any particular folder on my computer that I should put the key? I type the url in Host Name, click Open and then type in the username with which I log onto the cpanel, but I am getting an authentication error.
Average of ratings: -
In reply to Frances Clynes

Re: Changing Charset

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

You need to specify the location of the key in PuTTY, the steps were in the link in my previous reply:

PuTTY: Specify private key file

Average of ratings: -
In reply to Leon Stringer

Re: Changing Charset

by Frances Clynes -

Thanks Leon. I appreciate all the trouble you taking here.

I have done that, got shell access from the webhosts but when I enter $ php admin/cli/mysql_collation.php --collation=utf8mb4_unicoe_ci 

I get an error 'Could not open input file: admin/cli/mysql_collation.php'




Average of ratings: -
In reply to Frances Clynes

Re: Changing Charset

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

The script needs to be run from the Moodle source code folder so you'll need to change to that first with cd. This folder might be something like public_html or moodle, it's the folder that the Moodle source code files were copied to when the site was installed.

So the commands would be 1) cd to change the folder and 2) run the script, e.g. assuming public_html is the folder:

$ cd public_html
$ php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci

You can check what's in the current folder with the ls command. The Moodle source code folder contains files like:

$ ls
admin             enrol          notes
analytics         error          npm-shrinkwrap.json
auth              favourites     package.json
availability      file.php       phpunit.xml.dist
backup            files          pix
badges            filter         plagiarism
behat.yml.dist    grade          pluginfile.php
⋮                 ⋮              ⋮
Average of ratings: -
In reply to Leon Stringer

Re: Changing Charset

by Frances Clynes -
I have done it and it seems to be fine. The error message is no longer displaying and I can write to the db.
Leon - I cannot thank you enough.

Frances
Average of ratings: -