Moodle
  1. Moodle
  2. MDL-24443

Merge Users: Add functionality to Moodle that allows site admins to merge two user profiles.

    Details

    • Type: New Feature New Feature
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: Future Dev
    • Fix Version/s: BACKEND
    • Component/s: Administration
    • Labels:
    • Environment:
      In my case it's MSSQL 2005, IIS 6 and Windows Server 2003, but it's really a platform independant issue.

      Description

      I use Moodle in a corporate training environment and, as a site admin, I have a need to be able to merge two student records in Moodle. Our users are authenticated against Active Directory and when they login Moodle authenticates them against Active Directory and then checks if they are already registered as users on Moodle. If they are, then they are logged in. If not, Moodle pulls their user info off Active Directory and creates a new account for that username. This all works beautifully.

      However, due to business processes and rules that are outside of our control, we sometimes have a situation where the same user gets registered on Moodle with two different usernames - the result being a duplicate record. Example, a user might change jobs or transfer to a different division. Alternatively, he might resign but then be re-hired sometime later. In each of these cases the user will most likely be given a new username on Active Directory (one of those things that are out of our control) by the HR department when they go through the whole HR recruitment or transfer process.

      The result is that when this user goes on training again in his new position and tries to access Moodle with his NEW Active Directory username Moodle sees him as a new user and creates a new record for him. Obviously, all his old activity is still linked to his OLD username on Moodle. The course and activity data linked to his old username doesn't pull through to the new username because Moodle doesn't know that the two user records are related.

      An ideal solution would be to have a function in Moodle where the site admin could merge the old username with the new username and all the activity from the old username would be transferred to the new username. The old record would then be deleted.

      Over the last few years the Moodle community has developed their own scripts to solve this problem - so there's clearly a need. Quite a few of these scripts are shared on the http://moodle.org/mod/forum/discuss.php?d=103425 thread on the Using Moodle forum. However, running these unofficial scripts can be risky as merging records in Moodle seems to be quite an involved process, so we need a solution that's been fully tested on all the operating system and database platforms that Moodle supports. None of these community solutions are platform independent. Example, I haven't been able to get any of them to run on a MSSQL database. So, unless I hire a programmer to develop a MSSQL script for me, I'm stuck. I'd much rather have an official merge function built into Moodle than having to rely on third-party scripts.

        Activity

        Hide
        Angel Infante added a comment -

        Hello,

        I've read that someone is currently working in the Merge Utility creation, thank you very much, this will be really really helpful in our environment, I've been requested to research about this functionality in our Moodle 2.0 instance which contains several users as duplicated, I was wondering whether you have an update on this? do you know when this new functionality will be available to download and install in the Moodle 2.0 instances?, I've voted the feature, is there another way to help with this ?

        Best regards...

        Show
        Angel Infante added a comment - Hello, I've read that someone is currently working in the Merge Utility creation, thank you very much, this will be really really helpful in our environment, I've been requested to research about this functionality in our Moodle 2.0 instance which contains several users as duplicated, I was wondering whether you have an update on this? do you know when this new functionality will be available to download and install in the Moodle 2.0 instances?, I've voted the feature, is there another way to help with this ? Best regards...
        Hide
        Luis de Vasconcelos added a comment -

        @Angel, can you say who is working on the Merge function? Perhaps we can combine our efforts?

        Show
        Luis de Vasconcelos added a comment - @Angel, can you say who is working on the Merge function? Perhaps we can combine our efforts?
        Hide
        Brent Lee added a comment -

        I am onboard to test this in 1.9.X and 2.X anytime.

        Show
        Brent Lee added a comment - I am onboard to test this in 1.9.X and 2.X anytime.
        Hide
        Forrest Gaston added a comment -

        Angel,

        I have used the past third-party scripts many times, can you give me some indication if you are thinking some of the past scripts just need modified, or is this a start from scratch project.

        I was looking at getting a bid to get this built, but I would like support the community etc if possible.

        Thanks
        Forrest

        Show
        Forrest Gaston added a comment - Angel, I have used the past third-party scripts many times, can you give me some indication if you are thinking some of the past scripts just need modified, or is this a start from scratch project. I was looking at getting a bid to get this built, but I would like support the community etc if possible. Thanks Forrest
        Hide
        Luis de Vasconcelos added a comment -

        Forrest,

        Which third-party scripts have you used?

        Show
        Luis de Vasconcelos added a comment - Forrest, Which third-party scripts have you used?
        Hide
        Forrest Gaston added a comment -

        I've Used the latest merge users tool mentioned in the following discussion, I don't think it works in Moodle 2.

        http://moodle.org/mod/forum/discuss.php?d=103425

        I may need to get the tool built, if we don't get it working as a community project.

        Thanks
        Forrest

        Show
        Forrest Gaston added a comment - I've Used the latest merge users tool mentioned in the following discussion, I don't think it works in Moodle 2. http://moodle.org/mod/forum/discuss.php?d=103425 I may need to get the tool built, if we don't get it working as a community project. Thanks Forrest
        Hide
        Susan Mangan added a comment -

        We are running 2.3 and I'm not sure how to address these issues for our faculty. It seems as thought this is a much-needed functionality for core.

        I found this new utility for 2x written in March 2012 http://moodle.org/mod/forum/discuss.php?d=103425#p866655

        Has anyone used this? Am wondering if this works for 2.3?

        Thanks!

        Show
        Susan Mangan added a comment - We are running 2.3 and I'm not sure how to address these issues for our faculty. It seems as thought this is a much-needed functionality for core. I found this new utility for 2x written in March 2012 http://moodle.org/mod/forum/discuss.php?d=103425#p866655 Has anyone used this? Am wondering if this works for 2.3? Thanks!
        Hide
        Nicolas Dunand added a comment -

        There are versions of the plugin mentioned in http://moodle.org/mod/forum/discuss.php?d=103425 working both for Moodle 1.9 and 2.x.

        Show
        Nicolas Dunand added a comment - There are versions of the plugin mentioned in http://moodle.org/mod/forum/discuss.php?d=103425 working both for Moodle 1.9 and 2.x.
        Hide
        Luis de Vasconcelos added a comment -

        Thanks Nicolas. Unfortunately none of them works on MSSQL yet...

        Show
        Luis de Vasconcelos added a comment - Thanks Nicolas. Unfortunately none of them works on MSSQL yet...
        Hide
        Nicolas Dunand added a comment -

        Hi Luis,
        Could you PM me so we could try a few things and sort this out? I unfortunately don't have a MSSQL server at hand, so you could be of some help.

        Show
        Nicolas Dunand added a comment - Hi Luis, Could you PM me so we could try a few things and sort this out? I unfortunately don't have a MSSQL server at hand, so you could be of some help.
        Hide
        Luis de Vasconcelos added a comment -

        Sure, I'll PM you...

        FYI, Microsoft does have a free version of MSSQL 2008 R2 at http://www.microsoft.com/en-us/download/details.aspx?id=25174

        Show
        Luis de Vasconcelos added a comment - Sure, I'll PM you... FYI, Microsoft does have a free version of MSSQL 2008 R2 at http://www.microsoft.com/en-us/download/details.aspx?id=25174
        Hide
        Brian Kremer added a comment -

        Another use case: An employee's last name changes due to marriage or divorce. Their information is updated in Active Directory. They log in to Moodle and it creates a new user account.

        Show
        Brian Kremer added a comment - Another use case: An employee's last name changes due to marriage or divorce. Their information is updated in Active Directory. They log in to Moodle and it creates a new user account.
        Hide
        Luis de Vasconcelos added a comment - - edited

        Nicolas,

        I finally managed to get your script working on a instance of Moodle v2.5.1+ using a MSSQL 2008 R2 database on a IIS/WinXP SP3 laptop. I managed to merge two profiles "successfully" (i.e. without crashing Moodle or getting any errors), but I then found a problem when you have custom profile fields: and that is that after merging two profiles there are now TWO records, one from each of the two user profiles, for each of the custom profile fields. So, if you have ONE custom profile field there will be TWO records for that custom profile field after the merge (the record from the "User ID to be removed" profile and the record from the "User ID to be removed" profile. This is obviously a problem as there should only be one record for each profile field for each user profile - the /user/profile.php page can't handle two records for a custom profile field.

        Example: If you have three custom profile fields: X, Y and Z and you have captured data in each of these custom fields then there will be three records in mdl_user_info_data for each student, one record for each of those custom fields:

        SELECT id, userid, fieldid, data, dataformat
        FROM mdl_user_info_data
        WHERE (userid IN (36501, 14710))

        gives this data for the three profile fields:

        id userid fieldid data dataformat
        20369 14710 1 0
        64878 36501 1 0
        20370 14710 2 432 0
        64879 36501 2 6733 0
        64880 36501 3 ABC 0

        If you now merge the profile with another profile (i.e. merge 14710 and 36501) each of the above rows will be duplicated with the rows from the other profile - so there will now be two records for each custom profile field:

        id userid fieldid data dataformat
        20369 36501 1 0
        64878 36501 1 0
        20370 36501 2 432 0
        64879 36501 2 6733 0
        64880 36501 3 ABC 0

        Moodle doesn't expect that so the result is that the /user/profile.php?id=36501 page might now show the incorrect data in those custom profile fields of the remaining profile after the merge.

        So, profile field "fieldid = 2" might show "6733" instead of "432" on the /user/profile.php?id=36501 page after the merge.

        If you're using any custom profile fields in Moodle this report_mergeusers.zip script be aware of this problem with custom profile fields.

        Show
        Luis de Vasconcelos added a comment - - edited Nicolas, I finally managed to get your script working on a instance of Moodle v2.5.1+ using a MSSQL 2008 R2 database on a IIS/WinXP SP3 laptop. I managed to merge two profiles "successfully" (i.e. without crashing Moodle or getting any errors), but I then found a problem when you have custom profile fields: and that is that after merging two profiles there are now TWO records, one from each of the two user profiles, for each of the custom profile fields. So, if you have ONE custom profile field there will be TWO records for that custom profile field after the merge (the record from the "User ID to be removed" profile and the record from the "User ID to be removed" profile. This is obviously a problem as there should only be one record for each profile field for each user profile - the /user/profile.php page can't handle two records for a custom profile field. Example: If you have three custom profile fields: X, Y and Z and you have captured data in each of these custom fields then there will be three records in mdl_user_info_data for each student, one record for each of those custom fields: SELECT id, userid, fieldid, data, dataformat FROM mdl_user_info_data WHERE (userid IN (36501, 14710)) gives this data for the three profile fields: id userid fieldid data dataformat 20369 14710 1 0 64878 36501 1 0 20370 14710 2 432 0 64879 36501 2 6733 0 64880 36501 3 ABC 0 If you now merge the profile with another profile (i.e. merge 14710 and 36501) each of the above rows will be duplicated with the rows from the other profile - so there will now be two records for each custom profile field: id userid fieldid data dataformat 20369 36501 1 0 64878 36501 1 0 20370 36501 2 432 0 64879 36501 2 6733 0 64880 36501 3 ABC 0 Moodle doesn't expect that so the result is that the /user/profile.php?id=36501 page might now show the incorrect data in those custom profile fields of the remaining profile after the merge. So, profile field "fieldid = 2" might show "6733" instead of "432" on the /user/profile.php?id=36501 page after the merge. If you're using any custom profile fields in Moodle this report_mergeusers.zip script be aware of this problem with custom profile fields.
        Hide
        Nicolas Dunand added a comment -

        Hi Luis,

        Did you have to make any changes in the code to support MSSQL? If so, could you post a diff here, or issue a pull request on GitHub?

        As for the custom profile fields, what should the plugin do? For me it would be more logical, for each custom profile field:

        • if only one value is not empty, keep that one
        • else, keep the "probably-most-up-to-date" value (i.e. the one with the highest id)
        Show
        Nicolas Dunand added a comment - Hi Luis, Did you have to make any changes in the code to support MSSQL? If so, could you post a diff here, or issue a pull request on GitHub? As for the custom profile fields, what should the plugin do? For me it would be more logical, for each custom profile field: if only one value is not empty, keep that one else, keep the "probably-most-up-to-date" value (i.e. the one with the highest id)
        Hide
        Jordi Pujol-Ahulló added a comment -

        Hi all,

        I found that custom user fields are moved by the plugin from the one user to target user, so that target user will have N values for the same custom files after N-1 merges.

        We are trying it in a dev installation, and we'll try to update the case of user_info_data table, copying the data column (instead of updating the userid column) from one user to target user. This will prevent duplicated records, and warnings on Moodle web in that sense.

        We'll try to do it by MySQL and what should it be for other databases (even though we'll not be able to test it; thus, feedback will be necessary).

        Hoping this helps,

        Jordi

        Show
        Jordi Pujol-Ahulló added a comment - Hi all, I found that custom user fields are moved by the plugin from the one user to target user, so that target user will have N values for the same custom files after N-1 merges. We are trying it in a dev installation, and we'll try to update the case of user_info_data table, copying the data column (instead of updating the userid column) from one user to target user. This will prevent duplicated records, and warnings on Moodle web in that sense. We'll try to do it by MySQL and what should it be for other databases (even though we'll not be able to test it; thus, feedback will be necessary). Hoping this helps, Jordi
        Hide
        Michael de Raadt added a comment -

        It looks like there's a few people interested in this. I encourage you to keep those creative juices flowing and put up a code solution.

        Show
        Michael de Raadt added a comment - It looks like there's a few people interested in this. I encourage you to keep those creative juices flowing and put up a code solution.
        Hide
        Nicolas Dunand added a comment -

        Hi Michael,
        Well there's a code solution in form of a plugin here (it's a report, but I recently turned it in to an admintool): https://moodle.org/plugins/view.php?plugin=report_mergeusers

        Show
        Nicolas Dunand added a comment - Hi Michael, Well there's a code solution in form of a plugin here (it's a report, but I recently turned it in to an admintool): https://moodle.org/plugins/view.php?plugin=report_mergeusers
        Hide
        Luis de Vasconcelos added a comment -

        Nicolas,

        No, I didn't have to change anything to make it work on my MSSQL 2008 R2 test site (IIS 5.1 on WinXP SP3). The "$plugin->version = 2013050100;" version from https://moodle.org/plugins/view.php?plugin=report_mergeusers seems to work fine "out the box" on MSSQL 2008 R2.

        The only thing that I'm unsure of is the usage of "mysql_error()". Is that a MYSQL specific function? If so, how do other databases like MSSQL and POSTGRES handle it?

        And according to http://php.net/manual/en/function.mysql-error.php mysql_error() is deprecated as of PHP 5.5.0, so will it cause any problems with newer versions of PHP? I'm still on PHP v5.3.9.

        Show
        Luis de Vasconcelos added a comment - Nicolas, No, I didn't have to change anything to make it work on my MSSQL 2008 R2 test site (IIS 5.1 on WinXP SP3). The "$plugin->version = 2013050100;" version from https://moodle.org/plugins/view.php?plugin=report_mergeusers seems to work fine "out the box" on MSSQL 2008 R2. The only thing that I'm unsure of is the usage of "mysql_error()". Is that a MYSQL specific function? If so, how do other databases like MSSQL and POSTGRES handle it? And according to http://php.net/manual/en/function.mysql-error.php mysql_error() is deprecated as of PHP 5.5.0, so will it cause any problems with newer versions of PHP? I'm still on PHP v5.3.9.
        Hide
        Luis de Vasconcelos added a comment - - edited

        As for the custom profile fields, the best solution would be to prompt the admin to select the value that s/he wants to keep.

        Otherwise, if that's not an option, I think the data for the "User ID to be kept" should be retained, if it's not empty:

        • if only one value is not empty, keep that one
        • else, keep the value from the "User ID to be kept" profile
        Show
        Luis de Vasconcelos added a comment - - edited As for the custom profile fields, the best solution would be to prompt the admin to select the value that s/he wants to keep. Otherwise, if that's not an option, I think the data for the "User ID to be kept" should be retained, if it's not empty: if only one value is not empty, keep that one else, keep the value from the "User ID to be kept" profile
        Hide
        Luis de Vasconcelos added a comment -

        Another option would be to add the mdl_user_info_data table to the $tablesToSkip array on the index.php page so that mdl_user_info_data table is not included in the merge. The admin/teacher would then have to manually update any custom profile fields on the 'User ID to be kept' profile with the data from the 'User ID to be removed' profile after the merge. This will prevent the duplicate records from being created in the mdl_user_info_data table after a merge.

        What do you think?

        Show
        Luis de Vasconcelos added a comment - Another option would be to add the mdl_user_info_data table to the $tablesToSkip array on the index.php page so that mdl_user_info_data table is not included in the merge. The admin/teacher would then have to manually update any custom profile fields on the 'User ID to be kept' profile with the data from the 'User ID to be removed' profile after the merge. This will prevent the duplicate records from being created in the mdl_user_info_data table after a merge. What do you think?
        Hide
        Jordi Pujol-Ahulló added a comment -

        Hi all,

        I think the best way is to make a copy of the values of the custom fields. Why?

        • They are usually synced, so if they have to change, it will anyway.
        • All fields user can edit, the user will update them (if the user updated it in his/her old-to-be-removed profile).
        • Fields user cannot edit, but only admins/teachers can, they would review always anyway.

        We will develop this way, which is what we need actually. What we can do is add a flag on plugin settings to enable/disable the copy of the values. So, this flag would specify:

        • When merging two user profiles, the value of custom fields will be copied if the flag is enabled.
        • When mergin with the flag disabled, nothing will be done.

        This way, we have the best of both options:

        • Manually revision when flag disabled (suggested by Luis de Vasconcelos) after user profiles are merged.
        • Automatically merged also of custom fields when flag is enabled (what we actually need).

        When it will be done, you'll always be able to improve it

        Jordi

        Show
        Jordi Pujol-Ahulló added a comment - Hi all, I think the best way is to make a copy of the values of the custom fields. Why? They are usually synced, so if they have to change, it will anyway. All fields user can edit, the user will update them (if the user updated it in his/her old-to-be-removed profile). Fields user cannot edit, but only admins/teachers can, they would review always anyway. We will develop this way, which is what we need actually. What we can do is add a flag on plugin settings to enable/disable the copy of the values. So, this flag would specify: When merging two user profiles, the value of custom fields will be copied if the flag is enabled. When mergin with the flag disabled, nothing will be done. This way, we have the best of both options: Manually revision when flag disabled (suggested by Luis de Vasconcelos) after user profiles are merged. Automatically merged also of custom fields when flag is enabled (what we actually need). When it will be done, you'll always be able to improve it Jordi
        Hide
        Nicolas Dunand added a comment -

        Luis,

        I don't really want to add on-the-fly options for the admin. I prefer your proposal to keep a non empty value or (if both non empty) keep the one from the "User ID to be kept", because this would cover most use cases I can think of.

        However, as an immediate bug fix, I've added the user_info_data table to $tablesToSkip as you proposed. This has just been pushed to https://github.com/ndunand/moodle-mergeusers

        Show
        Nicolas Dunand added a comment - Luis, I don't really want to add on-the-fly options for the admin. I prefer your proposal to keep a non empty value or (if both non empty) keep the one from the "User ID to be kept", because this would cover most use cases I can think of. However, as an immediate bug fix, I've added the user_info_data table to $tablesToSkip as you proposed. This has just been pushed to https://github.com/ndunand/moodle-mergeusers
        Hide
        Jordi Pujol-Ahulló added a comment -

        Hi all,

        After talking with my colleagues, we found that your aproach on custom fields is the best choice also for us, so we are not developing any other option.

        I've just requested a pull for lang updates, including Catalan and Spanish among other changes.

        However, I have other questions:

        • Should we include the cases of non-core tables (like the one for mod/journal we use in our Moodle)?
        • We think it should be better to store a log of the merging actions, and a listing of them. Do you think we could include this funcionality in this plugin?

        Thanks in advance,

        Jordi

        Show
        Jordi Pujol-Ahulló added a comment - Hi all, After talking with my colleagues, we found that your aproach on custom fields is the best choice also for us, so we are not developing any other option. I've just requested a pull for lang updates, including Catalan and Spanish among other changes. However, I have other questions: Should we include the cases of non-core tables (like the one for mod/journal we use in our Moodle)? We think it should be better to store a log of the merging actions, and a listing of them. Do you think we could include this funcionality in this plugin? Thanks in advance, Jordi
        Hide
        Nicolas Dunand added a comment -

        Hi Jordi,

        OK for the custom profile fields, great!

        • The current plugin merges data from all non-core tables, but I always wondered whether this should be the case for non core plugins or not. In particular, problems could arise because of unique keys. For instance, if the user has made attempts at a quiz with both his "bad" and "good" accounts, we end up with two "attempt #1" for a quiz, which is not allowed. This will be difficult to tackle.
        • A log of the actions is already displayed (but not stored) – see lines 203-210 of index.php. I use to keep this as a reference if anything goes wrong.
        Show
        Nicolas Dunand added a comment - Hi Jordi, OK for the custom profile fields, great! The current plugin merges data from all non-core tables, but I always wondered whether this should be the case for non core plugins or not. In particular, problems could arise because of unique keys. For instance, if the user has made attempts at a quiz with both his "bad" and "good" accounts, we end up with two "attempt #1" for a quiz, which is not allowed. This will be difficult to tackle. A log of the actions is already displayed (but not stored) – see lines 203-210 of index.php. I use to keep this as a reference if anything goes wrong.
        Hide
        Jordi Pujol-Ahulló added a comment -

        Hi Nicolas,

        • Non-core tables:
          • I think your solution, which makes a blind search for user-ish columns on tables and replaces user ids, is simply genial. Ok, but not perfect.
          • To be perfect, in my opinion, we need feedback to detect any possible conflict in any table. You found several cases, but I'm sure there are more cases, as you suggested.
          • For instance, mod/journal (non-core plugin) uses a table mdl_journal_entries (one per user), and having debug messages enabled I can see a message like this "Notice: Error: mdb->get_record() found more than one record!". It gets only a record, but, which of them? It seems that record with the lowest id, but may be different in other databases. What we have decide to do with the journal is to just skip that table.
          • So, in conclusion, I think we can add the cases we and other people detect.
        • For the log being displayed, yes, I can see it.
          • It is easy to maintain (copy & paste for logging) if only few cases appear.
          • But if the amount of occurrences is quite high (like several cases every month), that approach may not be sufficient and become hard to maintain.
          • From another viewpoint, it will serve for forensic purposes too.
          • The table structure could be something like: id int(10), originuserid int(10), targetuserid int(10), actions text. 'actions' should contain the list of actions made on that merge.
          • And, as always, have you any other proposal for that?

        Thanks for your time,

        Jordi

        Show
        Jordi Pujol-Ahulló added a comment - Hi Nicolas, Non-core tables: I think your solution, which makes a blind search for user-ish columns on tables and replaces user ids, is simply genial. Ok, but not perfect. To be perfect, in my opinion, we need feedback to detect any possible conflict in any table. You found several cases, but I'm sure there are more cases, as you suggested. For instance, mod/journal (non-core plugin) uses a table mdl_journal_entries (one per user), and having debug messages enabled I can see a message like this "Notice: Error: mdb->get_record() found more than one record!". It gets only a record, but, which of them? It seems that record with the lowest id, but may be different in other databases. What we have decide to do with the journal is to just skip that table. So, in conclusion, I think we can add the cases we and other people detect. For the log being displayed, yes, I can see it. It is easy to maintain (copy & paste for logging) if only few cases appear. But if the amount of occurrences is quite high (like several cases every month), that approach may not be sufficient and become hard to maintain. From another viewpoint, it will serve for forensic purposes too. The table structure could be something like: id int(10), originuserid int(10), targetuserid int(10), actions text. 'actions' should contain the list of actions made on that merge. And, as always, have you any other proposal for that? Thanks for your time, Jordi
        Hide
        Jordi Pujol-Ahulló added a comment - - edited

        Hi again,

        I just found that the merging for user enrolment on courses does not work properly.

        The user when logs in into Moodle, cannot see any course, but in the courses, the user appears in the list of enroled users, but not in the list of participants. I think that souldn't be the case.

        Reedition:

        • That is the case of the special case #2.

        Sorry :-P

        Re-reedition:

        • I think that putting the value "2" for the user_enrolments.status column is not a suitable option. When you try to re-edit that enrolment, you are given with some warnings on non-valid values on select (active=0, disabled=1). Is that value "2" explicit to detect merging actions?

        Jordi

        Show
        Jordi Pujol-Ahulló added a comment - - edited Hi again, I just found that the merging for user enrolment on courses does not work properly. The user when logs in into Moodle, cannot see any course, but in the courses, the user appears in the list of enroled users, but not in the list of participants. I think that souldn't be the case. Reedition: That is the case of the special case #2. Sorry :-P Re-reedition: I think that putting the value "2" for the user_enrolments.status column is not a suitable option. When you try to re-edit that enrolment, you are given with some warnings on non-valid values on select (active=0, disabled=1). Is that value "2" explicit to detect merging actions? Jordi
        Hide
        Jordi Pujol-Ahulló added a comment -

        The activity mod/groupselect relies on mdl_group_members to show groups' members.

        Is a good idea to omit the mdl_group_members table on the merging process?

        Pros & cons:

        • In a almost-automatic-daily syncronization of users, groups and courses, updating the mdl_group_members is unnecessary (since the old user will disappear), but for sites with mostly manual management, it would be great to automatically update the old user id with the new id.

        Maybe, we could give some "control evidence" to administrator giving the choice of "what we have to do with group membership?" Option 1. Update Option 2. Do-nothing, placed under the users' id form fields.

        Jordi

        Show
        Jordi Pujol-Ahulló added a comment - The activity mod/groupselect relies on mdl_group_members to show groups' members. Is a good idea to omit the mdl_group_members table on the merging process? Pros & cons: In a almost-automatic-daily syncronization of users, groups and courses, updating the mdl_group_members is unnecessary (since the old user will disappear), but for sites with mostly manual management, it would be great to automatically update the old user id with the new id. Maybe, we could give some "control evidence" to administrator giving the choice of "what we have to do with group membership?" Option 1. Update Option 2. Do-nothing, placed under the users' id form fields. Jordi
        Hide
        Jordi Pujol-Ahulló added a comment -

        Hi all,

        I've requested a pull to include changes over merging proces when the table groups_members is processed.

        We found that a full update of the currentId for the newId is not correct, given the three cases may exist:

        • Only currentId appears for a given groupid: so update to newId.
        • Only newId appears for a given groupid: do nothing.
        • Both currentId and newId appear for a given groupid: we need to clean up the currentId and leave the newId record.

        We perform in a very similar way than in the grade_grades. No backup is made of the old membership (when deletion applies), but the newId already appears, so no problem at all.

        Looking for feedback from you.

        Hugs,

        Jordi

        Show
        Jordi Pujol-Ahulló added a comment - Hi all, I've requested a pull to include changes over merging proces when the table groups_members is processed. We found that a full update of the currentId for the newId is not correct, given the three cases may exist: Only currentId appears for a given groupid: so update to newId. Only newId appears for a given groupid: do nothing. Both currentId and newId appear for a given groupid: we need to clean up the currentId and leave the newId record. We perform in a very similar way than in the grade_grades. No backup is made of the old membership (when deletion applies), but the newId already appears, so no problem at all. Looking for feedback from you. Hugs, Jordi
        Hide
        Jordi Pujol-Ahulló added a comment -

        Hi all,

        Thanks Nicolas.

        The plugin has been updated with several improvements:

        • User id can be now: id, idnumber or username (username by default).
        • Tables groups_mebers, grade_grades and journal_entries are treated in the same way: if record exists for both users, the plugin deletes the record for the old user. If only old user appears, it is updated.

        Hope this helps. It does for us.

        Jordi

        Show
        Jordi Pujol-Ahulló added a comment - Hi all, Thanks Nicolas. The plugin has been updated with several improvements: User id can be now: id, idnumber or username (username by default). Tables groups_mebers, grade_grades and journal_entries are treated in the same way: if record exists for both users, the plugin deletes the record for the old user. If only old user appears, it is updated. Hope this helps. It does for us. Jordi
        Hide
        Luis de Vasconcelos added a comment - - edited

        Jordi, is it necessary to DELETE any data on the 'User ID to be removed' profile? Wouldn't it be "safer" to just leave that data on that 'User ID to be removed' profile? It will then be removed or handled by the standard Moodle 'Delete User' function (/admin/user.php?delete=12345) when you delete the 'User ID to be removed' profile. Surely there's no need to delete any data in the Merge script when you're going to delete the duplicate profile anyway?

        Show
        Luis de Vasconcelos added a comment - - edited Jordi, is it necessary to DELETE any data on the 'User ID to be removed' profile? Wouldn't it be "safer" to just leave that data on that 'User ID to be removed' profile? It will then be removed or handled by the standard Moodle 'Delete User' function (/admin/user.php?delete=12345) when you delete the 'User ID to be removed' profile. Surely there's no need to delete any data in the Merge script when you're going to delete the duplicate profile anyway?
        Hide
        Jordi Pujol-Ahulló added a comment -

        Hi all,

        I'll try to explain I think removing user's data is necessary in certain cases. Remember that the actions are done in this timeline:

        1. Merge users.
        2. (if necessary) delete old user.

        In the current version in github, when do we delete data?

        1. On grade_grades, but given the backup appears on grade_grades_history, actually, we are not removing anything. Afterwards, this table grade_grades_history will be updated also.
        2. On groups_members, given that if you do not remove it from the groups, with debugging enabled, you see warnings of duplicated ids when selecting groups' members. And actually, you do not know which database record is selected.
        3. On journal_entries. It is not a standard plugin, and it is necessary to detect when the data has to be updated or removed.

        The general behavior on all these three cases is as follows:

        • Case 1: only "User ID to be removed" appears in records of the given table. They are simply updated with the new user id. The activity on that table was done by the old user and has to be updated.
        • Case 2: only the new user id appears in records of the given table. Do nothing. Any activity on that was done by the user to be maintained and that data is correct.
        • Case 3: both users appear in records of the given table for a given compound index/key. That means that the person using the old user id and the new id did activity on that table. If we simply update the "User ID to be removed" with the new user id, we will have duplicated indexes/keys in the database table, which is not allowed, and if allowed, Moodle afterwards would give us warning for the same issue (duplicated entries, like in the groups_members). So we need to do clean up. So, which database records are correct? We assume that the activity done with the new user is the correct, since it is the most recent. Most of the times, the "User ID to be removed" is deactivated (auth plugin does not allows to log in), so person cannot log in with that username any more.

        And that is important, user deletion is done if necessary/possible after merging user (if it was ok). And we do not know the time that will pass between merging users and old user deletion. In that sense, we have to guarantee a correct Moodle behavior in any case.

        Checking the admin/user.php?delete=12345 functionality, one can see that the database table we skip, the grade_grades and groups_members are clean up by the function delete_user(), and that an event 'user_deleted' is triggered. But in our Moodle installation there is no event handler registered for that, so there is no action taken for tables/plugins not covered in the core function delete_user().

        The good thing would be all plugins with user interaction to have registered handlers for 'user_deleted' events. But that task is out of scope of this plugin.

        In conclusion, the job of this plugin is just in the scope of this plugin: Moodle have to work as well as before merging users, without warnings or problems of any kind. And that is what this plugin is making :-D

        Any other comments/suggestions from other plugin participants to clarify that are welcome.

        Have a nice day,

        Jordi

        Show
        Jordi Pujol-Ahulló added a comment - Hi all, I'll try to explain I think removing user's data is necessary in certain cases. Remember that the actions are done in this timeline: Merge users. (if necessary) delete old user. In the current version in github, when do we delete data? On grade_grades, but given the backup appears on grade_grades_history, actually, we are not removing anything. Afterwards, this table grade_grades_history will be updated also. On groups_members, given that if you do not remove it from the groups, with debugging enabled, you see warnings of duplicated ids when selecting groups' members. And actually, you do not know which database record is selected. On journal_entries. It is not a standard plugin, and it is necessary to detect when the data has to be updated or removed. The general behavior on all these three cases is as follows: Case 1: only "User ID to be removed" appears in records of the given table. They are simply updated with the new user id. The activity on that table was done by the old user and has to be updated. Case 2: only the new user id appears in records of the given table. Do nothing. Any activity on that was done by the user to be maintained and that data is correct. Case 3: both users appear in records of the given table for a given compound index/key. That means that the person using the old user id and the new id did activity on that table. If we simply update the "User ID to be removed" with the new user id, we will have duplicated indexes/keys in the database table, which is not allowed, and if allowed, Moodle afterwards would give us warning for the same issue (duplicated entries, like in the groups_members). So we need to do clean up. So, which database records are correct? We assume that the activity done with the new user is the correct, since it is the most recent. Most of the times, the "User ID to be removed" is deactivated (auth plugin does not allows to log in), so person cannot log in with that username any more. And that is important, user deletion is done if necessary/possible after merging user (if it was ok). And we do not know the time that will pass between merging users and old user deletion. In that sense, we have to guarantee a correct Moodle behavior in any case. Checking the admin/user.php?delete=12345 functionality, one can see that the database table we skip, the grade_grades and groups_members are clean up by the function delete_user(), and that an event 'user_deleted' is triggered. But in our Moodle installation there is no event handler registered for that, so there is no action taken for tables/plugins not covered in the core function delete_user(). The good thing would be all plugins with user interaction to have registered handlers for 'user_deleted' events. But that task is out of scope of this plugin. In conclusion, the job of this plugin is just in the scope of this plugin: Moodle have to work as well as before merging users, without warnings or problems of any kind. And that is what this plugin is making :-D Any other comments/suggestions from other plugin participants to clarify that are welcome. Have a nice day, Jordi
        Hide
        Nicolas Dunand added a comment -

        I agree that deleting entries in the database should be the last option, but Jordi's rationale makes it clear in my opinion that this is the way to go, as it's the most "correct" behaviour for this plugin.

        Show
        Nicolas Dunand added a comment - I agree that deleting entries in the database should be the last option, but Jordi's rationale makes it clear in my opinion that this is the way to go, as it's the most "correct" behaviour for this plugin.
        Hide
        Jordi Pujol-Ahulló added a comment -

        Hi all,

        We have moved the plugin from a report (https://moodle.org/plugins/view.php?plugin=report_mergeusers) to an administration tool pending to be approved (https://moodle.org/plugins/view.php?plugin=tool_mergeusers).

        It has now brand new functionalities, such us:

        • Manual and cron-able CLI script support to make user mergings.
        • Merging actions are log into a database table, so that any merging action is recorded there for further reference.
        • Two events are triggered according to the result of a merging action: merging_success and merging_failed. This helps other Moodle plugins to react to those events.

        Event though the plugin's maturity is still "beta", I consider it "stable". Actually, I use it in the Moodle instance of our University in production and there is no problem at all. Since that Moodle uses a MySQL database, supporting transactions, no matter if a merging action fails, since any database change is rolled back.

        Above all, the only way a merging action could fail is for only:

        • Database tables from non-core plugins with users' id and some compound index including that field.

        If that occurs, it is quickly solved by only reporting the problem and updating a configuration file from the plugin.

        For more details, take a look at the README (https://github.com/ndunand/moodle-mergeusers/blob/master/README.txt).

        In the end, let me show you my personal experience: Duplicated users were hard headaches for us in our Moodle, but since we use this tool (including the CLI scripts put into the server's cron), we are really, really happy

        Saludos,

        Jordi

        Show
        Jordi Pujol-Ahulló added a comment - Hi all, We have moved the plugin from a report ( https://moodle.org/plugins/view.php?plugin=report_mergeusers ) to an administration tool pending to be approved ( https://moodle.org/plugins/view.php?plugin=tool_mergeusers ). It has now brand new functionalities, such us: Manual and cron-able CLI script support to make user mergings. Merging actions are log into a database table, so that any merging action is recorded there for further reference. Two events are triggered according to the result of a merging action: merging_success and merging_failed. This helps other Moodle plugins to react to those events. Event though the plugin's maturity is still "beta", I consider it "stable". Actually, I use it in the Moodle instance of our University in production and there is no problem at all. Since that Moodle uses a MySQL database, supporting transactions, no matter if a merging action fails, since any database change is rolled back. Above all, the only way a merging action could fail is for only: Database tables from non-core plugins with users' id and some compound index including that field. If that occurs, it is quickly solved by only reporting the problem and updating a configuration file from the plugin. For more details, take a look at the README ( https://github.com/ndunand/moodle-mergeusers/blob/master/README.txt ). In the end, let me show you my personal experience: Duplicated users were hard headaches for us in our Moodle, but since we use this tool (including the CLI scripts put into the server's cron), we are really, really happy Saludos, Jordi
        Hide
        Luis de Vasconcelos added a comment -

        Awesome! Thanks.

        I will test this new version asap (on MSSQL 2008 R2).

        Jordi, the \mergeusers\README.txt file still says that "there is no provision for automatic rollbacks". Bearing in mind your "Moodle uses a MySQL database, supporting transactions, no matter if a merging action fails, since any database change is rolled back" comment, does that part of the README.txt file need to be updated?

        Show
        Luis de Vasconcelos added a comment - Awesome! Thanks. I will test this new version asap (on MSSQL 2008 R2). Jordi, the \mergeusers\README.txt file still says that "there is no provision for automatic rollbacks". Bearing in mind your "Moodle uses a MySQL database, supporting transactions, no matter if a merging action fails, since any database change is rolled back" comment, does that part of the README.txt file need to be updated?
        Hide
        Luis de Vasconcelos added a comment - - edited

        Also, since this is now a Admin Tool please can you confirm/update the installation indstructions.

        Do you put the code in: '\admin\tool\mergeusers'?

        Please also describe how the 'CLI scripts' are used.

        Thanks.

        Show
        Luis de Vasconcelos added a comment - - edited Also, since this is now a Admin Tool please can you confirm/update the installation indstructions. Do you put the code in: '\admin\tool\mergeusers'? Please also describe how the 'CLI scripts' are used. Thanks.
        Hide
        Luis de Vasconcelos added a comment - - edited

        I ran a quick test on MSSQL 2008 R2 and Moodle 2.5.3+ (Build: 20131129) and it worked (see screenshot). The screen has this message: "Here are the queries that have been sent to the DB", but no queries are displayed. How do I get it to show the queries?

        Can you explain the meaning of: "For logging or security reasons we are skipping mdl_user_info_data, mdl_user_lastaccess, mdl_user_preferences, mdl_user_private_key." What does that mean? Are those tables excluded from the merge?

        Thanks for the great work!

        Show
        Luis de Vasconcelos added a comment - - edited I ran a quick test on MSSQL 2008 R2 and Moodle 2.5.3+ (Build: 20131129) and it worked (see screenshot). The screen has this message: "Here are the queries that have been sent to the DB", but no queries are displayed. How do I get it to show the queries? Can you explain the meaning of: "For logging or security reasons we are skipping mdl_user_info_data, mdl_user_lastaccess, mdl_user_preferences, mdl_user_private_key." What does that mean? Are those tables excluded from the merge? Thanks for the great work!
        Hide
        Jordi Pujol-Ahulló added a comment -

        Thanks for your feedback Luis!!!

        Some points I can explain:

        1. Automatic rollbacks: this functionality relies on the rollback database support. So if your database supports transactions to be committed/rollback, this plugin uses them. So, I agree with you that this point will be updated in the next future.
        2. CLI scripts: its usage is explained in the README.txt, as well as how to customize it. By default, it runs an interative comman line interface where user.id are typed to be merged. You can cancel that script at any time (see instructions when running, but always also using Ctrl+C).
        3. "\admin\tool\mergeusers": what do you mean about it? I greped for any "require" looking for relative paths, and there is no instance of such an string. Do you mean about PHP namespaces?
        4. "quereis that have been sent to the DB": The string may be a little confusing. The situation from the screenshot is only possible if you have users with no activity at all, or when another merging action was already done (in the same way, from A to B). You can try to merge in the other way around (from B to A) or other testing users with activity.
        5. Get the list of merging actions: To do so, you can go the Site Administration->Users->Accounts->Merge User Accounts->See merging logs
        6. Skipping database tables from merging: In the README.txt appears a Special Case #3 that explains why we rely on omitting those tables from merging.
        7. Special cases and skipping tables: All those special cases (compound indexes) and skipping tables are set up in a configuration file (config/config.php). However, you can extend the list easily by creating a local file (config/config.local.php, with the same structure that appears on config/config.php) with only the list of your own local special cases and skippable tables. It is also commented on the README.txt

        I hope I had been clear on my reply. Feel free to give more feedback... always!

        Saludos,

        Jordi

        Show
        Jordi Pujol-Ahulló added a comment - Thanks for your feedback Luis!!! Some points I can explain: Automatic rollbacks: this functionality relies on the rollback database support. So if your database supports transactions to be committed/rollback, this plugin uses them. So, I agree with you that this point will be updated in the next future. CLI scripts: its usage is explained in the README.txt, as well as how to customize it. By default, it runs an interative comman line interface where user.id are typed to be merged. You can cancel that script at any time (see instructions when running, but always also using Ctrl+C). "\admin\tool\mergeusers": what do you mean about it? I greped for any "require" looking for relative paths, and there is no instance of such an string. Do you mean about PHP namespaces? "quereis that have been sent to the DB": The string may be a little confusing. The situation from the screenshot is only possible if you have users with no activity at all, or when another merging action was already done (in the same way, from A to B). You can try to merge in the other way around (from B to A) or other testing users with activity. Get the list of merging actions: To do so, you can go the Site Administration->Users->Accounts->Merge User Accounts->See merging logs Skipping database tables from merging: In the README.txt appears a Special Case #3 that explains why we rely on omitting those tables from merging. Special cases and skipping tables: All those special cases (compound indexes) and skipping tables are set up in a configuration file (config/config.php). However, you can extend the list easily by creating a local file (config/config.local.php, with the same structure that appears on config/config.php) with only the list of your own local special cases and skippable tables. It is also commented on the README.txt I hope I had been clear on my reply. Feel free to give more feedback... always! Saludos, Jordi
        Hide
        Luis de Vasconcelos added a comment -

        Thanks Jordi.

        Regarding point 3 - "\admin\tool\mergeusers", I was referring to the installation instructions for the plugin. The Readme.txt file does not contain any. I think there should be a section in the Readme.txt file that explains how you install the plugin.

        I'll provide more feedback once I've tested some more, but so far I'm quite impressed!

        Show
        Luis de Vasconcelos added a comment - Thanks Jordi. Regarding point 3 - "\admin\tool\mergeusers", I was referring to the installation instructions for the plugin. The Readme.txt file does not contain any. I think there should be a section in the Readme.txt file that explains how you install the plugin. I'll provide more feedback once I've tested some more, but so far I'm quite impressed!
        Hide
        Luis de Vasconcelos added a comment -

        By the way, the "See merging logs" feature is BRILLIANT! Thanks!

        Show
        Luis de Vasconcelos added a comment - By the way, the "See merging logs" feature is BRILLIANT! Thanks!
        Hide
        Jordi Pujol-Ahulló added a comment -

        Thanks for your replies Luis!!!

        We use this plugin in production in our Moodle. So we need something usefull and easy to use. Before the "See merging logs", our technicians had to copy&paste. Now it is no more necessary. It can be improved with pagination, for instance

        In the end, there are these points about README.txt:

        1. Explain automatic rollbacks relying on transactional databases.
        2. Path where to deploy the plugin in the Moodle.

        Saludos,

        Jordi

        Show
        Jordi Pujol-Ahulló added a comment - Thanks for your replies Luis!!! We use this plugin in production in our Moodle. So we need something usefull and easy to use. Before the "See merging logs", our technicians had to copy&paste. Now it is no more necessary. It can be improved with pagination, for instance In the end, there are these points about README.txt: 1. Explain automatic rollbacks relying on transactional databases. 2. Path where to deploy the plugin in the Moodle. Saludos, Jordi
        Hide
        Luis de Vasconcelos added a comment -

        Where is the best place to report bugs or requests for this plugin?

        • Here in this MDL-24443 issue
        • Create a new issue on tracker.moodle.org (Where? I can't find this plugin on tracker.moodle.org/browse/CONTRIB)
        • github.com/ndunand/moodle-mergeusers
        • github.com/jpahullo/moodle-mergeusers

        Thanks.

        Show
        Luis de Vasconcelos added a comment - Where is the best place to report bugs or requests for this plugin? Here in this MDL-24443 issue Create a new issue on tracker.moodle.org (Where? I can't find this plugin on tracker.moodle.org/browse/CONTRIB) github.com/ndunand/moodle-mergeusers github.com/jpahullo/moodle-mergeusers Thanks.
        Hide
        Nicolas Dunand added a comment -

        Hi Luis,

        We just decided with Jordi that –for now– we'd like to see the issues reported here. We'll then deal with them and announce updates of the plugin here as well.

        Show
        Nicolas Dunand added a comment - Hi Luis, We just decided with Jordi that –for now– we'd like to see the issues reported here. We'll then deal with them and announce updates of the plugin here as well.
        Hide
        Luis de Vasconcelos added a comment -

        Shall we report them in this issue or as new issues?

        Creating new issues for each problem sounds best, but where? Will a new component for 'tool_mergeusers' be added to CONTRIB on tracker.moodle.org when/if 'tool_mergeusers' gets approved?

        Show
        Luis de Vasconcelos added a comment - Shall we report them in this issue or as new issues? Creating new issues for each problem sounds best, but where? Will a new component for 'tool_mergeusers' be added to CONTRIB on tracker.moodle.org when/if 'tool_mergeusers' gets approved?
        Hide
        John Hoopes added a comment - - edited

        hello all,

        I've made some changes to the merge users plugin that might be of interest to you. I've added the ability to now search for users instead of just knowing the username/id/idnumber. You can specify which user fields you'd like to search on, or through all of them.

        This helps with users who might not know this information directly. and allows the user to select the old user and new user. The direct entry of username/id/idnumber is still available, just set as "advanced" in the moodleform terminology. (which in 2.6 is "show more" link) I also added a bit of form handling and error handling to make it easier for a user to figure out what is going on, as well as user verification at necessary steps to ensure that the ids selected are in fact users. (protects against form injection of values across page loads) That might seem unnecessary at times, but for a plugin that touches every table in the database, it's worth being sure values are correct.

        I also added a confirmation table so that administrators can see the users that they are merging, after entering information on a form, before the merging process actually occurs.

        A few minor changes I made as well was updating the way the merge user class was checking for supported databases. As I gathered, the intention was to run the merging process in a DB transaction, but the check for mysql wasn't sufficient enough to be sure that it supported a transaction. Only certain MySQL table engines support transactions, and so I added a check for the supported MySQL engines.

        Also, I added a configuration option for the suspension of users. Suspending of users occurs over the event system, and so, I made a config option for the tool in order to specify if you want the old user to be suspended or not. In most cases you would, which is why I left the default to be yes, but for where I work they'd like that to not happen.

        There is one other issue I've run into with this plugin and that's when the users have attempted the same quiz. The quiz_attempts table has another complex index to it. And simply removing the old record isn't sufficient as the quiz module attempts touch more than just the quiz attempt but go further into the question engine. There is a function that removes quiz attempts, and so I will attempt to code that into this tool.

        forgot to add the location of my changes. I've made a pull request, but here's my github repo:
        https://github.com/jhoopes/moodle-mergeusers

        Let me know what you think of the changes or if you want something different

        Show
        John Hoopes added a comment - - edited hello all, I've made some changes to the merge users plugin that might be of interest to you. I've added the ability to now search for users instead of just knowing the username/id/idnumber. You can specify which user fields you'd like to search on, or through all of them. This helps with users who might not know this information directly. and allows the user to select the old user and new user. The direct entry of username/id/idnumber is still available, just set as "advanced" in the moodleform terminology. (which in 2.6 is "show more" link) I also added a bit of form handling and error handling to make it easier for a user to figure out what is going on, as well as user verification at necessary steps to ensure that the ids selected are in fact users. (protects against form injection of values across page loads) That might seem unnecessary at times, but for a plugin that touches every table in the database, it's worth being sure values are correct. I also added a confirmation table so that administrators can see the users that they are merging, after entering information on a form, before the merging process actually occurs. A few minor changes I made as well was updating the way the merge user class was checking for supported databases. As I gathered, the intention was to run the merging process in a DB transaction, but the check for mysql wasn't sufficient enough to be sure that it supported a transaction. Only certain MySQL table engines support transactions, and so I added a check for the supported MySQL engines. Also, I added a configuration option for the suspension of users. Suspending of users occurs over the event system, and so, I made a config option for the tool in order to specify if you want the old user to be suspended or not. In most cases you would, which is why I left the default to be yes, but for where I work they'd like that to not happen. There is one other issue I've run into with this plugin and that's when the users have attempted the same quiz. The quiz_attempts table has another complex index to it. And simply removing the old record isn't sufficient as the quiz module attempts touch more than just the quiz attempt but go further into the question engine. There is a function that removes quiz attempts, and so I will attempt to code that into this tool. forgot to add the location of my changes. I've made a pull request, but here's my github repo: https://github.com/jhoopes/moodle-mergeusers Let me know what you think of the changes or if you want something different

          People

          • Votes:
            54 Vote for this issue
            Watchers:
            34 Start watching this issue

            Dates

            • Created:
              Updated: