Solución al error de collation en Moodle al editar usuarios

Llevo tiempo peleándome con este error de Moodle, y ya por fin encontré la causa. Se trata de un error del sistema de caracteres de la BBDD al querer editar un usuario de Moodle o crear uno nuevo.

El problema se me complicaba porque tenía dos entornos supuestamente idénticos. En uno funcionaba bien, y en el otro me mostraba el error en cuestión.

Así que nada, os voy a contar todo mi proceso de investigación, ya que no había encontrado la solución en ningún sitio.

También te puede interesar:
Crear una academia online WordPress en 3 pasos
¿Qué es Divi? Guía de Divi Theme para WordPress
Crear Cursos Online con Thrive Apprentice [+Webinar]
Mejora tu perfil de LinkedIn en pocos pasos

Error COLLATION Moodle 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4'

Este es el error que se obtiene cuando se quiere editar la ficha de un usuario de Moodle o se quiere crear un usuario nuevo.

Indica que el juego de caracteres utilizado no es compatible para poder editar la tabla mdl_user de Moodle.

Transcripcion de la traza de error:

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

Stack trace:
line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
line 1184 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
line 1902 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_recordset_sql()
line 1887 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 590 of /lib/formslib.php: call to user_editadvanced_form->validation()
line 526 of /lib/formslib.php: call to moodleform->validate_defined_fields()
line 636 of /lib/formslib.php: call to moodleform->is_validated()
line 171 of /user/editadvanced.php: call to moodleform->get_data()

Para poder visualizar hasta este detalle la traza de error, hay que activar el modo debug de desarrollador desde administración del sitio de Moodle.

Analizar el problema de collation de BBDD en MariaDB

Analizando el problema tomando de partida que el problema estuviera en la codificación de la BBDD, hice la siguientes consultas sobre MySQL o MariaDB.

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATCH
def aulasae utf8mb4 utf8mb4_unicode_ci null

SHOW CREATE DATABASE aulasae;

CREATE DATABASE `aulasae` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */

SHOW CREATE TABLE mdl_user

CREATE TABLE `mdl_user` (
`id` bigint(10) NOT NULL AUTO_INCREMENT,
`auth` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'manual',
`confirmed` tinyint(1) NOT NULL DEFAULT 0,
`policyagreed` tinyint(1) NOT NULL DEFAULT 0,
`deleted` tinyint(1) NOT NULL DEFAULT 0,
`suspended` tinyint(1) NOT NULL DEFAULT 0,
`mnethostid` bigint(10) NOT NULL DEFAULT 0,
`username` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`idnumber` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`firstname` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`lastname` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`emailstop` tinyint(1) NOT NULL DEFAULT 0,
`icq` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`skype` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`yahoo` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`aim` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`msn` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`phone1` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`phone2` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`institution` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`department` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`address` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`city` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`country` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`lang` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'en',
`calendartype` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'gregorian',
`theme` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`timezone` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '99',
`firstaccess` bigint(10) NOT NULL DEFAULT 0,
`lastaccess` bigint(10) NOT NULL DEFAULT 0,
`lastlogin` bigint(10) NOT NULL DEFAULT 0,
`currentlogin` bigint(10) NOT NULL DEFAULT 0,
`lastip` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`secret` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`picture` bigint(10) NOT NULL DEFAULT 0,
`url` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`description` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`descriptionformat` tinyint(2) NOT NULL DEFAULT 1,
`mailformat` tinyint(1) NOT NULL DEFAULT 1,
`maildigest` tinyint(1) NOT NULL DEFAULT 0,
`maildisplay` tinyint(2) NOT NULL DEFAULT 2,
`autosubscribe` tinyint(1) NOT NULL DEFAULT 1,
`trackforums` tinyint(1) NOT NULL DEFAULT 0,
`timecreated` bigint(10) NOT NULL DEFAULT 0,
`timemodified` bigint(10) NOT NULL DEFAULT 0,
`trustbitmask` bigint(10) NOT NULL DEFAULT 0,
`imagealt` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`lastnamephonetic` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`firstnamephonetic` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`middlename` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`alternatename` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `mdl_user_mneuse_uix` (`mnethostid`,`username`),
KEY `mdl_user_del_ix` (`deleted`),
KEY `mdl_user_con_ix` (`confirmed`),
KEY `mdl_user_fir_ix` (`firstname`),
KEY `mdl_user_las_ix` (`lastname`),
KEY `mdl_user_cit_ix` (`city`),
KEY `mdl_user_cou_ix` (`country`),
KEY `mdl_user_las2_ix` (`lastaccess`),
KEY `mdl_user_ema_ix` (`email`),
KEY `mdl_user_aut_ix` (`auth`),
KEY `mdl_user_idn_ix` (`idnumber`),
KEY `mdl_user_fir2_ix` (`firstnamephonetic`),
KEY `mdl_user_las3_ix` (`lastnamephonetic`),
KEY `mdl_user_mid_ix` (`middlename`),
KEY `mdl_user_alt_ix` (`alternatename`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED COMMENT='One record for each person'

Cabe decir, que esa configuración que se muestra en las instancias de BBDD, tablas, y campos, son correctas. Correctas en el sentido de que no son la causa, ya que estaba exactamente igual en los dos entornos que os comentaba, donde funciona bien en uno pero no en el otro.

En vuestro caso a lo mejor tenéis otro collation que también será compatible.

Continúo con el análisis.

Cambiar collation de BBDD de Moodle por comandos desde consola SSH

Existe un comando de PHP que nos permite cambiar el collation si lo necesitásemos. En mi caso, para asegurarme de que las tablas tenían el collation correcto, lanzé el siguiente comando desde la carpeta raíz de nuestro moodle del apache:

# php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci

Daría como resultado:

Converted: 459, skipped: 1264, errors: 0

Viendo que con esto que el problema se seguía produciendo, seguí investigando. Hasta que ya por fin encontré la diferencia entre los dos entornos. Se encontraba en el fichero de configuración de Moodle donde se establece la conexión con BBDD.

Solución al error de collation en Moodle

Comparando los entornos en cuestión, donde funcionaba en uno, pero fallaba en el otro, y todo parecía igual, encontré esta diferencia.

El problema estaba en el fichero de configuración de Moodle config.php:

$CFG->dboptions = array (
'dbpersist' => 0,
'dbport' => 3306,
'dbsocket' => '/srv/mariadb/mariadb.sock',
'dbcollation' => 'utf8_general_ci', <===== ELIMINAR ESTA LÍNEA
);

Lo que hay que hacer es eliminar la línea que he marcado con el comentario, que es la que entra en conflicto con el collation que hemos definido a nivel de MariaDB. Así dejamos que sea la BBDD quien controle el sistema de caracteres y no la conexión desde Moodle.

Se ve que en el entorno que me funcionaba no tenía esa línea, y en el que fallaba sí la tenía.

Añadí esa línea al entorno que funcionaba, y ya pude reproducir el error. Por lo que la investigación quedaba resuelta. Solo había que eliminar esa línea.

Conclusiones

Este tipo de errores pueden ser costosos de solucionar. Hasta que se averigua la causa hay que realizar muchas comprobaciones. Pero al menos, he conseguido dar solución a este error. Espero que os sirva.