Moodle Database error on user login - clash with External Database & Manual Enrolment Methods

Moodle Database error on user login - clash with External Database & Manual Enrolment Methods

by Brad Pasley -
Number of replies: 1

Recently a few teachers/students have been unable to login to Moodle. With debugging on, typical error output is this:

Debug info: Duplicate entry '971886-5-8833' for key 'mdl_roleassi_conroluse_uix'
INSERT INTO mdl_role_assignments (roleid,contextid,userid,component,itemid,timemodified,modifierid,sortorder) VALUES(?,?,?,?,?,?,?,?)
[array (
0 => '5',
1 => 971886,
2 => '8833',
3 => 'enrol_database',
4 => '21240',
5 => 1614641283,
6 => '10067',
7 => 0,
)]
Error code: dmlwriteexception
Stack trace:
  • line 489 of /lib/dml/moodle_database.php: dml_write_exception thrown
  • line 1329 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 1375 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()
  • line 1529 of /lib/accesslib.php: call to mysqli_native_moodle_database->insert_record()
  • line 237 of /enrol/database/lib.php: call to role_assign()
  • line 245 of /lib/enrollib.php: call to enrol_database_plugin->sync_user_enrolments()
  • line 868 of /lib/classes/session/manager.php: call to enrol_check_plugins()
  • line 81 of /course/loginas.php: call to core\session\manager::loginas()
My understand from the error is this:

During the user's login, the external database has attempted to enrol the user into a Moodle course, by insert a row into mdl_role_assignments. But this error is spitting out because there is already an enrolment in that course (via the manual enrolment method). To find this, I did an SQL query, using the second and third values (contextid and userid)

SELECT * FROM mdl_context WHERE id = '971886'


That returns a row. The contextlevel is '50' (therefore the context is at the course level). Therefore, the instanceid returned, is the course id. I opened the course (in Moodle), checked the participants. The user has a double enrolment:

double enrolment

The top one is an "external database" enrolment. The bottom one is a "manual enrolment".

Looking at the trace, it seems clear that this is caused by the external database attempting to enrol, when a manual enrolment already exists. I believe it used to be the case that the external database would simply skip enrolments when the user already had an enrolment (via manual enrolment), but I don't know why it is doing this now.

Can anyone suggest how to resolve this?

Moodle version: 3.6.2+ (Build 20190208)

Database: mysql (5.7.33-0 ubuntu 0.18.04.1

PHP: 7.2.34.18.18.04.11

Average of ratings: -
In reply to Brad Pasley

Re: Moodle Database error on user login - clash with External Database & Manual Enrolment Methods

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

A participant can be enrolled on a course with different methods as shown in this example.

I think the problem is that the mdl_roleassi_conroluse_uix constraint shouldn't be there. In Moodle 1.9 mdl_role_assignments had this constraint, defined in lib/db/install.xml:

        <INDEX NAME="contextid-roleid-userid" UNIQUE="true" FIELDS="contextid, roleid, userid" NEXT="sortorder"/>

But this was removed in Moodle 2.0 for MDL-21782, lib/db/upgrade.php removes it during upgrade:

        // The new enrol plugins may assign one role several times in one context,
        // if we did not allow it we would have big problems with roles when unenrolling
        $table = new xmldb_table('role_assignments');
        $index = new xmldb_index('contextid-roleid-userid', XMLDB_INDEX_UNIQUE, array('contextid', 'roleid', 'userid'));

        // Conditionally launch drop index contextid-roleid-userid
        if ($dbman->index_exists($table, $index)) {
            $dbman->drop_index($table, $index);
        }

You should be able to remove this manually with:

ALTER TABLE mdl_role_assignments DROP CONSTRAINT mdl_roleassi_conroluse_uix;

Back up the database before making changes!

But I'm not sure why this would become an issue now, and if there's one such issue in the database it's possible there's more. There's some documentation on checking the database structure.

Support for Moodle 3.6 ended in May 2020, you should plan to upgrade to a supported version. Moodle 3.6.2 is behind the final update, 3.6.10, so is missing many fixes including 30 security updates.

Average of ratings: -