I recently experienced an issue in Moodle where I was unable to create new users. I was receiving an “Error reading from database” message. In this brief blog post, I share my experiences of fixing this error.

The reason behind wanting to share how I fixed the error, is down to the fact that I trawled through many pages of unhelpful forum posts. I hope that if you’re reading this and are trying to fix a similar issue this will alleviate some pain! The specific error message was:

Debug info: COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'utf8'

I recently updated to Moodle 4 so I assume that something during the installation caused a collation issue to occur in the database. To fix this, I essentially ran a command to update/change the collation type. I would always advise that if you’re not familiar or confident with the below you speak to someone in your technical team for help. I’d also like to state that just because this helped me to resolve my issues, depending on your environment it may not fix yours, but I hope it does!

How to fix the issue

If you’re using cPanel, you can usually access the command line terminal from the ‘Advanced’ menu.

It’s likely that your Moodle instance is hosted on a Linux server. This means you will need to use linux bash commands to access the Moodle directory. When you first enter the terminal you’ll be presented with a black screen and you’ll usually be in the home directory of your server. Below is an example of what you might see – the name of your web server and server domain name.

directory@servername [~]# 

There are several extremely useful commands you should know when navigating on a Linux server, the first one is:

directory@servername [~]#  ls

This stands for ‘list’ and entering this command will list all the folders within the directory you’re currently in. You can use this command to find out where you are in the file directory. You can change the directory with the following command:

directory@servername [~]#  cd FOLDERNAMEHERE

Keep using the ls and cd commands until you’re inside the following directory:

admin/cli/

When you’re in the right directory, you can run the following php script. I was able to copy and paste this straight into the terminal.

php mysql_collation.php --collation=utf8mb4_unicode_ci

You’ll see the command run and the terminal will display a message when it has finished. I was then able to create a new user right away.