Jerry Lau

Hello.

On Moodle 3.6.7 running rhel 7.6 wity php 7.2.x and MySQL 5.7

I want to change the enrolid from one to another and when I tried to do an sql update, its says there was a duplicate of that id but I do not see it.

Here is my query

mysql> select enrolid, userid from mdl_user_enrolments where enrolid ='11884';

+---------+--------+
| enrolid | userid |
+---------+--------+
|   11884 |   8220 |
|   11884 |  10062 |
|   11884 |  23984 |
|   11884 |  83676 |
|   11884 |  97388 |
|   11884 | 111307 |
|   11884 | 121795 |
|   11884 | 140118 |
|   11884 | 142939 |
|   11884 | 145377 |
|   11884 | 145977 |
|   11884 | 147332 |
|   11884 | 148167 |
|   11884 | 148203 |
|   11884 | 148338 |
|   11884 | 149100 |
|   11884 | 149157 |
|   11884 | 149575 |
|   11884 | 149587 |
+---------+--------+
19 rows in set (0.00 sec)

mysql> update mdl_user_enrolments set enrolid = '8261' where enrolid = '11884';

ERROR 1062 (23000): Duplicate entry '8261-8220' for key 'mdl_userenro_enruse_uix'

So queried to see what we have for that user (id of 8220) in the user_enrolments table but I don't see a duplicate

mysql> select id, status, enrolid, userid, modifierid from mdl_user_enrolments where userid = '8220';

+--------+--------+---------+--------+------------+
| id     | status | enrolid | userid | modifierid |
+--------+--------+---------+--------+------------+
| 635942 |      0 |   21359 |   8220 |          0 |
| 666012 |      0 |    8261 |   8220 |      76746 |
| 666087 |      0 |   11884 |   8220 |          0 |
| 666237 |      0 |   19209 |   8220 |          0 |
+--------+--------+---------+--------+------------+
4 rows in set (0.00 sec)

The I did a show create table:

mysql> show create table mdl_user_enrolments;

 mdl_user_enrolments | CREATE TABLE `mdl_user_enrolments` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `status` bigint(10) NOT NULL DEFAULT '0',
  `enrolid` bigint(10) NOT NULL,
  `userid` bigint(10) NOT NULL,
  `timestart` bigint(10) NOT NULL DEFAULT '0',
  `timeend` bigint(10) NOT NULL DEFAULT '2147483647',
  `modifierid` bigint(10) NOT NULL DEFAULT '0',
  `timecreated` bigint(10) NOT NULL DEFAULT '0',
  `timemodified` bigint(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `mdl_userenro_enruse_uix` (`enrolid`,`userid`),
  KEY `mdl_userenro_enr_ix` (`enrolid`),
  KEY `mdl_userenro_use_ix` (`userid`),
  KEY `mdl_userenro_mod_ix` (`modifierid`)
) ENGINE=InnoDB AUTO_INCREMENT=679231 DEFAULT CHARSET=utf8 |

Is it because in my role_assignments table, I have duplicates for this course/context id?

mysql> select userid, count(userid) from mdl_role_assignments where contextid = '587553' group by userid having count(userid) > 1;

+--------+---------------+
| userid | count(userid) |
+--------+---------------+
|   8220 |             2 |
|  10062 |             2 |
|  23984 |             2 |
|  83676 |             2 |
|  97388 |             2 |
| 111307 |             2 |
| 121795 |             2 |
| 140118 |             2 |
| 142939 |             2 |
| 145377 |             2 |
| 145977 |             2 |
| 147332 |             2 |
| 148167 |             2 |
| 148203 |             2 |
| 148338 |             2 |
| 149100 |             2 |
| 149157 |             2 |
| 149575 |             2 |
| 149587 |             2 |
+--------+---------------+
19 rows in set (0.01 sec)

More information here:

mysql> Select roleid, contextid, userid, component, itemid from mdl_role_assignments where contextid = '587553' order by userid;

+--------+-----------+--------+----------------+--------+
| roleid | contextid | userid | component      | itemid |
+--------+-----------+--------+----------------+--------+
|      5 |    587553 |   8220 | enrol_database |  11884 |
|      5 |    587553 |   8220 |                |      0 |
|      5 |    587553 |  10062 |                |      0 |
|      5 |    587553 |  10062 | enrol_database |  11884 |
|      5 |    587553 |  23984 | enrol_database |  11884 |
|      5 |    587553 |  23984 |                |      0 |
|      3 |    587553 |  76746 |                |      0 |
|      5 |    587553 |  83676 |                |      0 |
|      5 |    587553 |  83676 | enrol_database |  11884 |
|      5 |    587553 |  97388 |                |      0 |
|      5 |    587553 |  97388 | enrol_database |  11884 |
|      5 |    587553 | 111307 | enrol_database |  11884 |
|      5 |    587553 | 111307 |                |      0 |
|      5 |    587553 | 121795 | enrol_database |  11884 |
|      5 |    587553 | 121795 |                |      0 |
|      5 |    587553 | 140118 |                |      0 |
|      5 |    587553 | 140118 | enrol_database |  11884 |
|      5 |    587553 | 142939 | enrol_database |  11884 |
|      5 |    587553 | 142939 |                |      0 |
|      5 |    587553 | 145377 |                |      0 |
|      5 |    587553 | 145377 | enrol_database |  11884 |
|      5 |    587553 | 145977 | enrol_database |  11884 |
|      5 |    587553 | 145977 |                |      0 |
|      5 |    587553 | 147332 | enrol_database |  11884 |
|      5 |    587553 | 147332 |                |      0 |
|      5 |    587553 | 148167 | enrol_database |  11884 |
|      5 |    587553 | 148167 |                |      0 |
|      5 |    587553 | 148203 |                |      0 |
|      5 |    587553 | 148203 | enrol_database |  11884 |
|      5 |    587553 | 148338 | enrol_database |  11884 |
|      5 |    587553 | 148338 |                |      0 |
|      5 |    587553 | 149100 | enrol_database |  11884 |
|      5 |    587553 | 149100 |                |      0 |
|      5 |    587553 | 149157 | enrol_database |  11884 |
|      5 |    587553 | 149157 |                |      0 |
|      5 |    587553 | 149575 | enrol_database |  11884 |
|      5 |    587553 | 149575 |                |      0 |
|      5 |    587553 | 149587 | enrol_database |  11884 |
|      5 |    587553 | 149587 |                |      0 |
+--------+-----------+--------+----------------+--------+
39 rows in set (0.00 sec)

How do I clean this mess up to only use one type (manual) instead of enrol_database. I know the enrolid's of this course.

My question is how to make all db enrolment types to manual instead programatically? I used to be able to do this via script loops but now it seems it has changed a bit.

Thanks!

the documentation is bad and needs a lot of work.. they don't tell users what contextid,  context, component, itemid means, etc means and in the big picture and how it relates to other tables, etc.

That’s the catch.. how can I safely remove it via sql.

Also, we noticed that if instructors adds them first manually before dB enrolment, they can be referred as both as shown in my pic. 

Seems like a bug. Should it not check for dupes?

Hello folks.

using moodle 3.6.9 on rhel 7.7 64-bit enterprise; php 7.2.8 and MySQL 5.7

Saw something strange as we use database enrolment and now I see some users are enrolled as database and manual for that same user!  See attached photo.. it shows 2 "active" icons for that user. mouse over them and I get database enrolment and the other manual enrolment.

What we use to do is to database enrol these users and then reclassify them as "manual" enrolment so instructors can remove or unenrol them manually as they choose to. Worked great for years.

I was told that some instructors may have manually enrolled them first before our database enrolment process (built in moodle cron function) and thus, there are 2 enrolment types.

Is this true and how can we correct the ones using database enrolment and change it to manual enrolment for that course without affecting their enrolment in their course (s)?

Thanks