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!