User Creation error: COLLATION 'utf8mb4_bin' is not valid

User Creation error: COLLATION 'utf8mb4_bin' is not valid

از Antonio Pérez Adame در
Number of replies: 3

I use Moodle 3.10 and Mariadb 10.6.8 and PHP 8.1.6. Ubuntu Server 20.04.


I get the following error message when I try to create a user:

×Debug-Info:  COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'utf8mb3'
SELECT 'x' FROM mdl_user WHERE LOWER(email) COLLATE utf8mb4_bin = LOWER(?) AND mnethostid = ? AND id <> ? LIMIT 0, 1
[array (
0 => 'anouk.albrecht@adbk-nuernberg.de',
1 => '1',
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 1987 of /lib/dml/moodle_database.php: call to moodle_database->record_exists_sql()
  • line 310 of /user/editadvanced_form.php: call to moodle_database->record_exists_select()
  • line 615 of /lib/formslib.php: call to user_editadvanced_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 171 of /user/editadvanced.php: call to moodleform->get_data()
before it also happened to me with plugins or theme imports, but I solved it by adding the line 'dbcollation' => 'utf8mb4_unicode_ci' to config.php. I also added the following lines to the Mariadb configuration:
[client]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

MariaDB [(none)]> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | utf8mb4_general_ci         |
| collation_database       | utf8mb4_unicode_ci         |
| collation_server         | utf8mb4_unicode_ci         |
+--------------------------+----------------------------+

I tried running the following command on the server to fix the problem

php admin/cli/mysql_collation.php

But I also get the following error:

#0 /srv/www/moodle/lib/navigationlib.php(897): default_error_handler()
#1 /srv/www/moodle/lib/setup.php(610): require_once('...')
#2 /srv/www/moodle/config.php(28): require_once('...')
#3 /srv/www/moodle/admin/cli/mysql_collation.php(27): require('...')
#4 {main} in /srv/www/moodle/lib/navigationlib.php on line 897
PHP Stack trace:
PHP   1. {main}() /srv/www/moodle/admin/cli/mysql_collation.php:0
PHP   2. require() /srv/www/moodle/admin/cli/mysql_collation.php:27
PHP   3. require_once() /srv/www/moodle/config.php:28
PHP   4. require_once() /srv/www/moodle/lib/setup.php:610

What do you recommend me to do?

میانگین امتیازات: -
In reply to Antonio Pérez Adame

Re: User Creation error: COLLATION 'utf8mb4_bin' is not valid

از Leon Stringer در
تصویر Core developers تصویر Particularly helpful Moodlers

It looks like some or all of your database tables do not use the recommended character encoding for full UTF-8 support: utf8mb4.

You've tried to fix this with admin/cli/mysql_collation.php – which is the correct action – but there's an error. I think this error is because the script cannot read config.php. So either:

1. run admin/cli/mysql_collation.php as the user that executes PHP scripts, possibly www-data in which case run:

sudo -u www-data admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci

or;

2. change config.php's ownership or permissions so that whichever user you are logged in as can read this file.

میانگین امتیازات:Useful (1)
In reply to Leon Stringer

Re: User Creation error: COLLATION 'utf8mb4_bin' is not valid

از Antonio Pérez Adame در
Thank you both very much, it was a mix of both ideas.

The php that is installed to be used by default is php 8, but the one I use with Moodle is 7.4.
Then I used exactly sudo, which I forgot to put it above in the post and php7.4 to run the command as follows:

sudo php7.4 admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci

and it works correctly sonrisa

I tried later to run cron in the same way and I also get some errors that I have to look carefully, but at least Moodle is working again correctly and normally.
میانگین امتیازات: -