User creation ends in error: COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'utf8'

User creation ends in error: COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'utf8'

by Miguel Brostrom -
Number of replies: 5
OS: Red Hat Enterprise Linux Server release 7.9
Webserver: Apache/2.4.34
PHP: 7.3.33
MySQL: Distrib 5.7.24
Moodle Version: 3.7.2+ (Build: 20190927)
Debugging: Enabled at Developer verbosity

Error Message: The following error appears with debugging after attempting to create a new user. As far as I am aware, there are no other issues. 

Debug info: COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'utf8'
SELECT 'x' FROM mdl_user WHERE LOWER(email) COLLATE utf8mb4_bin = LOWER(?) AND mnethostid = ? AND id <> ? LIMIT 0, 1
[array (
0 => 'mili@uhtasi.org',
1 => '1',
2 => -1,
)]
Error code: dmlreadexception

Stack trace:
  • line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
  • line 1186 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 1918 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_recordset_sql()
  • line 1903 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 607 of /lib/formslib.php: call to user_editadvanced_form->validation()
  • line 543 of /lib/formslib.php: call to moodleform->validate_defined_fields()
  • line 653 of /lib/formslib.php: call to moodleform->is_validated()
  • line 171 of /user/editadvanced.php: call to moodleform->get_data()

MySQL my.cnf file:
[client-server]
# MOODLE Added MySQL full unicode support
[client]
default-character-set = utf8mb4

#
# This group is read by the server
#
[mysqld]
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# MOODLE Added MySQL full unicode support
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake

# MOODLE Added MySQL full unicode support
[mysql]
default-character-set = utf8mb4
#
# include all files from the config directory
#
!includedir /etc/opt/rh/rh-mysql57/my.cnf.d

Here are the following steps I've followed to try and fix the issues:

  • Check the Moodle conf.php and ensured that 'dbcollation' => 'utf8mb4_unicode_ci'  is included.
  • Reviewed the my.cnf file to confirm the recommended collation settings were there.
  • Run the admin/cli/mysql_collation.php. This resulted in errors below:

mdl_tag                                  - NO CHANGE

    name                                 - Default exception handler: DDL sql execution error Debug: Duplicate entry '1-yet' for key 'mdl_tag_tagnam_uix'

ALTER TABLE mdl_tag

                        MODIFY COLUMN name varchar(255)

                        CHARACTER SET utf8mb4

                        COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''

Error code: ddlexecuteerror

* line 492 of /lib/dml/moodle_database.php: ddl_change_structure_exception thrown

* line 1072 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()

* line 203 of /admin/cli/mysql_collation.php: call to mysqli_native_moodle_database->change_database_structure()


!!! DDL sql execution error !!!

!! Duplicate entry '1-yet' for key 'mdl_tag_tagnam_uix'

ALTER TABLE mdl_tag

                        MODIFY COLUMN name varchar(255)

                        CHARACTER SET utf8mb4

                        COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''

Error code: ddlexecuteerror !!

!! Stack trace: * line 492 of /lib/dml/moodle_database.php: ddl_change_structure_exception thrown

* line 1072 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database-&gt;query_end()

* line 203 of /admin/cli/mysql_collation.php: call to mysqli_native_moodle_database-&gt;change_database_structure()

 !!

I understand there seems to be a mismatch in the collation that Moodle has in configuration and what is presented in the database. I have looked through the forum and have found others with similar if not that same issues. I am not a DB admin and wondering if there is anything I can do to fix this? 

Thank you for any help or comments in advance.

Average of ratings: -
In reply to Miguel Brostrom

Re: User creation ends in error: COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'utf8'

by Ken Task -
Picture of Particularly helpful Moodlers
ssh into your server.
cd /path/to/moodlecode/admin/cli/

There are 3 command line only scripts that will help you:
mysql_collation.php, mysql_compressed_rows.php, and mysql_engine.php

As long as your php-cli is same version as web, run them like:
php nameofscript.php [ENTER]

They all have help as default if no parameter passed to their execution.

As is good practice, probably a good idea to make a backup of your DB before running any of those scripts.

'SoS', Ken

Average of ratings: Useful (1)
In reply to Ken Task

Re: User creation ends in error: COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'utf8'

by Miguel Brostrom -
Hey Ken,

Thank you for pointing out the cli scripts included with Moodle.  The attempt to use the mysql_collation.php ended with the errors posted in the OP. Would you recommend I run the mysql_compressed_rows.php, and mysql_engine.php scripts as well? Should the collation succeed that is.

PS: I have read MANY of your post replies across numerous threads over the last few years and you have been a real source of helpful information for most of my Moodle issues. Many thanks for your helpful and kindly put advice.
Average of ratings: -
In reply to Miguel Brostrom

Re: User creation ends in error: COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'utf8'

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

You've correctly identified that you need to run admin/cli/mysql_collation.php but the error – !! Duplicate entry '1-yet' for key 'mdl_tag_tagnam_uix' – is preventing this script from completing successfully.

It looks like there's a sneaky hidden character in a tag (U+FEFF). The mdl_tag table's unique constraint prevents two tags with the same name in the same tag collection. Presumably mdl_tag's current character collation treats strings with that hidden character as different, so "<U+FEFF>yet" and "yet" are different. But the target character collation, utf8mb4, treats these strings identical.

Try going to the manage tags page and see if you can find the duplicate tags. Based on the '1-yet' in the Duplicate entry message presumably there are two (or more tags) called "yet".  If you can find these tags try renaming one of them (for example, to "yet2"). This may annoy whoever set this tag.

Hopefully once any duplicate tags are renamed you'll be able to run admin/cli/mysql_collation.php successfully.

Technical note: I found the hidden character by copying the !! Duplicate entry … line from the OP's error and pasting it into Vim which showed:

!! Duplicate entry '1-<feff>yet' for key 'mdl_tag_tagnam_uix'

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

Re: User creation ends in error: COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'utf8'

by Miguel Brostrom -
Thank you Leon, this has pointed me in the right direction I hope. After doing some digging into the tagging on the site, I realized there were over 500 pages of tags under the default collection Site Administration > Appearance > Manage tags > Default collection. All the tags seemed like random text from course material or comments across the site. See below:
Image of duplicate tags and many other random text labeled as tags in the Moodle site.

This was rather unusual as I do not manage tabs on the site and seeing this many seems unintentional. A new duplicate was found after running admin/cli/mysql_collation.php and this happened many times. Words that were duplicates were "yet, thus, moreover, therefore, so". Seems like words that would sometime be followed by a semicolon. Trying to make sense of this insane amount of tagging.

Either way, I do not know if I should continue the script and identify duplicates or just drop the table entirely, as I do not know if any users are utilizing tagging after seeing all of these random tags. I'll update after the script can get through successfully.

Average of ratings: -
In reply to Miguel Brostrom

Re: User creation ends in error: COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'utf8'

by Miguel Brostrom -
After removing all the duplicates I was able to successfully run the admin/cli/mysql_collation.php which ended up converting around 50 more tables. Once completed I tried to add a new user through the site administration and it worked without error. 

Many thanks Leon!
Average of ratings: Useful (1)