As a Moodle user, I have always wondered, is it safe to manually modify Moodle databases?
The correct answer would be ‘No’.
It’s not safe because you may end up breaking your Moodle installation, since, as you may or may not know, Moodle pulls all its data from different tables in the data base.
Also, from a developer perspective, it is not ideal to modify core data from the Moodle installation, since the chances of corrupting it are very high and every time there is an update, we may have to modify the same values again. However, it is very possible to modify values directly in the data base, if you know where you are looking.
Today I faced a challenge (which prompted me to write this article) in which I ended up executing a query directly in the Moodle data base to fix my installation. I will summarize what the objective of my task, what I did, what went wrong and how I fixed it.
The client request was to add several custom fields to the registration form, to make it more robust. It is possible to do it easily directly with the tools found in the System Administration block, however, as you create fields, they are grouped and displayed in a separate section which, in some cases, doesn’t make sense, given the type of field you want to add (like a title associated with the name of the user, the province or state associated with the address, etc.).
What was initially done
After trying several solutions, I found in the plugin directory of Moodle, a plugin that creates associated data fields, to fields that come in the form by default. By doing that, you can re-organize, in a way, the sign-up form for users. So, this plugin was installed and enabled to start testing directly in the Moodle installation.
What went wrong
Here is where it gets tricky. Being honest, several things went wrong:
Even though on the Moodle plugin directory, it says it works with versions of Moodle up to 3.2, I noticed after the fact that the plugin hadn’t been updated since 2015, which can be an issue, because several things might have changed since then, opening the door to vulnerabilities.
I noticed after the fact, that the documentation wasn’t available in English, which made me work on a trial and error basis.
After creating the first custom field, the biggest mistake was to set it as required AND visible in the signup page which, in turn, prompted me to update my own profile, and here is where the installation broke, because it didn’t matter what I did or where I clicked, the site was stuck shown the update profile page.
How the issue was fixed
After freaking out (obviously) and running in circles for a couple of minutes, I came up with different solutions that, in my opinion could have worked, at that time, but in the end (and I will always remember this from my days in University), the simplest solution is usually the best:
The first thing that came to my mind, was to actually hack the source code, identify in which part the plugin was being called, delete it and reload the page, hopefully getting enough time to delete the plugin from the database. But then again, altering the source code directly, is not a programming good practise.
The second option, was to roll back the installation to the latest stable copy, see what was missing from that point and redo all that work. It could have been the most probable solution at that time, but it could have involved more sources than we had estimated for this project.
The third and final solution, which was implemented, was to find the data of the field in the corresponding table of the data base (‘user_field’ for those wondering), find the value under the ‘required’ column, and set it to ‘0’ (or false, speaking in Boolean). This was done, and then the next time the ‘update profile’ page was displayed, I was able to just click on ‘Update’ and then continue navigating on the site (which wasn’t possible right after I installed the plugin). Right after that, I decided to uninstall this plugin altogether to avoid any future issues.
So here it is, a fine example of an occasion in which the Moodle data base had to manually modified (bypassing Moodle).