Adding single user suddenly not working

Adding single user suddenly not working

by Andrew Fovargue -
Number of replies: 6

Tried to add a single user yesterday (9/9/20) and upon pressing save an error appears. 

Error reading from database

I have tried the most common online fix listed in google searches, which is Change @@default_storage_engine to @@storage_engine but this has not worked.

I can delete users singly or in bulk, I can add new users via CSV (uploaded a whole year group yesterday - 140 students, and did the individual user this way), but this is a huge pain for single new users (teachers) to be added.

There have been no upgrades to the hosting platform, version of moodle or addons recently so I don't know why it doesn't work.

Any help greatly appreciated.

Average of ratings: -
In reply to Andrew Fovargue

Re: Adding single user suddenly not working

by Andrew Fovargue -

additionally I cannot reset passwords - it just continues without actually changing the password, and users cannot search for their account by email address - that gives the same error as creating a new user - "error reading from database".


I really need help with this - urgent!

Average of ratings: -
In reply to Andrew Fovargue

Re: Adding single user suddenly not working

by Andrew Fovargue -
Upgraded to version 3.9.2 and still have the same issue
Average of ratings: -
In reply to Andrew Fovargue

Re: Adding single user suddenly not working

by Andrew Fovargue -
This is the Debug Info:
Debug info: COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'utf8'
SELECT 'x' FROM mdluc_user WHERE LOWER(email) COLLATE utf8mb4_bin = LOWER(?) AND mnethostid = ? AND id <> ? LIMIT 0, 1
[array (
0 => 'user.user@email.uk',
1 => '1',
2 => -1,
)]
Error code: dmlreadexception
Average of ratings: -
In reply to Andrew Fovargue

Re: Adding single user suddenly not working

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

It looks like there's some mismatch between the character set and collation for the database and Moodle's configuration. Presumably there's a warning on the Environment page.

I'd guess $CFG->dboptions['dbcollation'] is utf8mb4_bin in config.php. Changing this to utf8_unicode_ci might fix the problem. The recommended settings utf8mb4_unicode_ci if the database uses character set utf8mb4 or, for character set utf8 when then database software can't be configured for full UTF-8 support: utf8_unicode_ci. It should never be utf8mb4_bin which the error suggests is the case. Before making any changes config.php you might want to note the date and time this was last modified as this might help pinpoint how this situation occurred.

Ideally you'll want to ensure the database has full UTF-8 support configured and ensure all tables are using this, the conversion script, admin/cli/mysql_collation.php, can list the current values as well as performing the conversion.

If you need further help please tell us the database type and version and the 'dbcollation' value in config.php.

For urgent assistance you may need to consider paid support from some provider.

Average of ratings: Useful (1)
In reply to Leon Stringer

Re: Adding single user suddenly not working

by Andrew Fovargue -

Thank you, changing the setting in config.php has appeared to work - i will continue to test with new users. In the environment, previously all checks were green as I had (about 6 months ago) changed the mysql_full_unicode_support to the utf8mb4 but changing this back has appeared to solve the problem.


The dbtype is 'mariadb', dbcollataion value is 'utf_unicode_ci' (it was this bit i changed).


How do I run this script - im using cpanel and dont know how to access the command line to run scripts - i have been editing all files manually when changes are needed.

Average of ratings: -
In reply to Andrew Fovargue

Re: Adding single user suddenly not working

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

Switching to the recommended utf8mb4 is a case of:

1. Check the database configuration meets the requirements. Run the SQL query:

SHOW GLOBAL VARIABLES WHERE variable_name IN ('innodb_file_format', 'innodb_large_prefix', 'innodb_file_per_table');
+-----------------------+-----------+
| Variable_name         | Value     |
+-----------------------+-----------+
| innodb_file_format    | Barracuda | (Or blank)
| innodb_file_per_table | ON        |
| innodb_large_prefix   | ON        | (Or blank)
+-----------------------+-----------+

2. Make any changes to the database configuration resulting from the above. If you're using MariaDB with Moodle 3.9.2 I wouldn't expect any changes to be needed.

3. Run the conversion script, admin/cli/mysql_collation.php. This has to be done at the command line. Make a backup of the database first. The script can take a while (hours) unless the site is small.

4. Change dbcollation to utf8mb4_unicode_ci in config.php.

Here's a thread which covers the process of setting up command line access for this but your hosting environment may be different. You'd need to check that command line access is available, hopefully the hosting provider provides documentation or support on setting this up. If you get stuck you can ask us for help.

Average of ratings: -