Database read error after encoding update from utf8 to uft8mb4

Keywords: Moodle - Google Cloud Platform - How to - Other
Description:
Dear Community

I am looking for a way to roll back an encoding update which i assume went wrong. But i am not sure how to do this (if possible) from the terminal. Any help is much appreciated.

When installing a plugin in my freshly deployed moodle stack, i gor the following warning from moodle:

"mysql_full_unicode_support; if this test fails, it indicates a potential problem
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."

I searched this forum and found a similar case here: https://community.bitnami.com/t/howto-utf8mb4-conversion/62787

I ran the first command "mysql_collation.php --collation=utf8mb4_unicode_ci" which was successful. But when executing the second command "ctlscript.sh restart mysql" i got an error "sudo: ctlscript.ls: command not found", and the warning did not go away in moodle. I restarted the server thinking it would also perform the wanted restart of the database, but the warning pesisted in moodle.

I tried to change permission of the script with "chmod 755" as suggested in another forum post, but without any luck.

Having no obvious problems of this moodle warning, i continued setting up my site. But when enabling "Email based self registration" and attempting to register the first user i received "Error reading database". I enabled debugging and made a new attempt. Here is the result:

xDebug info: COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4'
SELECT 'x'
FROM mdl_user
WHERE LOWER(email) COLLATE utf8_bin = LOWER(?)
AND id IN (SELECT id
FROM mdl_user
WHERE email = ?
AND mnethostid = ?) LIMIT 0, 1
[array (
0 => 'jakobine@skyttevej.dk',
1 => 'jakobine@skyttevej.dk',
2 => '1',
)]
Error code: dmlreadexception
×Stack trace:
line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
line 1212 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
line 2002 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_recordset_sql()
line 1048 of /lib/authlib.php: call to moodle_database->record_exists_sql()
line 150 of /login/signup_form.php: call to signup_validate_data()
line 615 of /lib/formslib.php: call to login_signup_form->validation()
line 551 of /lib/formslib.php: call to moodleform->validate_defined_fields()
line 661 of /lib/formslib.php: call to moodleform->is_validated()
line 85 of /login/signup.php: call to moodleform->get_data()

I fear it is the encoding update error that is the root cause of this.

What can i do to either properly change encoding to utf8mb4 or to roll back encoding to utf8?

Best regards

Hi @jakob1,

Another user reported a similar issue recently and I fixed it by following the app's official documentation, can you take a look at that other thread?

https://community.bitnami.com/t/change-mariadb-encoding-from-uft8-to-utf8mb4/86642/13?u=jota

Happy to help!


Was my answer helpful? Click on :heart:

Hi @jota

Thank you very much for your quick response.
I followed the link to the Moodle doc.
First step (after the optional step) is "Restart your MySQL server". I stil get the "Command not found" error when trying to use "sudo /opt/bitnami/ctlscript.sh restart mysql".
I successfully ran the script "php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci" as described in the Moodle doc.
The next step "Make sure to repair and optimize all databases and tables." with the command "mysqlcheck -u root -p --auto-repair --optimize --all-databases" went ok.
Then I changed the 'dbcollation' => 'utf8mb4_unicode_ci', in config.php.
Because i was not able to restart mysql, i rebooted the VM.

Now i do not get the moodle warning regarding utf8, and neither do i see the database read error.

Conclusion is probably, that the first guide telling me to run the collation script did not mention, that i had to manually edit config.php.

Thanks jota for bringing my attention to another path of documentation.

Best regards

Hi @jakob1,

I'm glad to hear that :slight_smile:

However, I'd like to help you with this issue. Can you run these commands?

sudo ls -la /opt/bitnami
sudo bash -c "/opt/bitnami/ctlscript.sh status"

Thanks

Hi @jota

Response from first command:

jakob@moodle-sg-online-vm:~$ sudo ls -la /opt/bitnami
total 88
drwxr-xr-x 16 root    root     4096 Jan  2 17:32 .
drwxr-xr-x  3 root    root     4096 Dec 28 15:14 ..
drwxr-xr-x 17 root    root     4096 Dec 10 17:48 apache
lrwxrwxrwx  1 root    root        6 Dec 28 15:14 apache2 -> apache
drwxr-xr-x  3 root    root     4096 Nov 26 12:15 bncert
lrwxrwxrwx  1 root    root       47 Nov 26 12:11 bncert-tool -> /opt/bitnami/bncert/bncert-0.5.15-linux-x64.run
drwxr-xr-x  4 root    root     4096 Dec 28 15:15 bnsupport
-rw-r--r--  1 root    root      754 Apr 15  2020 bnsupport-regex.ini
lrwxrwxrwx  1 root    root       52 Nov 11 18:10 bnsupport-tool -> /opt/bitnami/bnsupport/bnsupport-0.9.3-linux-x64
.run
drwxr-xr-x  6 root    root     4096 Dec 21 13:06 common
-rwxr-xr-x  1 root    root     1453 Dec 28 15:16 ctlscript.sh
-rw-r--r--  1 root    root        4 Jan  2 17:32 .firstboot.status
drwxr-xr-x  4 root    root     4096 Jun 25  2020 gonit
drwxr-xr-x  3 root    root    12288 Dec 17 15:09 licenses
drwxr-xr-x 12 root    root     4096 Dec 28 15:14 mariadb
lrwxrwxrwx  1 root    root       15 Jan  2 17:32 moodle -> /bitnami/moodle
drwxr-xr-x  6 root    root     4096 Dec 28 15:14 mysql
drwxr-xr-x  7 root    root     4096 Dec 17 15:07 nami
drwxr-xr-x 12 root    root     4096 Dec  4 21:33 php
drwxrwxr-x 14 bitnami daemon   4096 Dec 28 15:14 phpmyadmin
-rwxrwx---  1 bitnami root      114 Dec 28 15:16 properties.ini
drwxr-xr-x 11 root    root     4096 Dec 28 15:15 scripts
drwxr-xr-x  2 bitnami bitnami  4096 Jan  6 16:32 stats
drwxr-xr-x  6 root    root     4096 Jan  2 17:32 var
jakob@moodle-sg-online-vm:~$

Response from second command:
jakob@moodle-sg-online-vm:~$ sudo bash -c "/opt/bitnami/ctlscript.sh status"
apache already running
mariadb already running
php-fpm already running
jakob@moodle-sg-online-vm:~$

Du you have to put the script path in quotes? This is not mentioned in any of the guides I have seen so far?

Thanks for taking your time on this.

Jakob

Hi @jakob1,

The ctlscript.sh file exists and it has the correct permissions so you shouldn't run into any issue when using it. Let's try again.

sudo /opt/bitnami/ctlscript.sh status

We're getting close now...

jakob@moodle-sg-online-vm:~$ sudo /opt/bitnami/ctlscript.sh status
apache already running
mariadb already running
php-fpm already running
jakob@moodle-sg-online-vm:~$

Hi @jakob1,

If the status command works, the restart one should also work.

sudo /opt/bitnami/ctlscript.sh restart mariadb

Note: You were trying to restart MySQL but the instance has MariaDB

Hi @jota

jakob@moodle-sg-online-vm:~$ sudo /opt/bitnami/ctlscript.sh restart mariadb
Restarted mariadb
jakob@moodle-sg-online-vm:~$

Working. I must admit i thought 'MariaDB' was just the name of the DB, and i am not quite sure what it is. I was convinced i was running a MySQL DB, and to the best of my knowledge, all the guides i found with the restart command was shown as either 'restart' or 'restart mysql'.

But anyway - i am up and running.

Thanks again for your time and your patience :slight_smile:

Hi @jakob1,

MariaDB is another open source relational database. You can learn more about it in its official site

https://mariadb.org/documentation/

You are welcome!! Enjoy :slight_smile:

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.

We have added this to our Moodle troubleshooting pages: https://docs.bitnami.com/google/apps/moodle/troubleshooting/debug-errors/#change-database-encoding-to-utf-8