User Creation error: COLLATION 'utf8mb4_bin' is not valid

User Creation error: COLLATION 'utf8mb4_bin' is not valid

από Antonio Pérez Adame -
Αριθμός απαντήσεων: 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?

Μέσος όρος βαθμολογίας: -
Σε απάντηση σε 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)
Σε απάντηση σε 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.
Μέσος όρος βαθμολογίας: -