1116519 - merge-users.pl should automatically detect when a user ID column belongs to a UNIQUE index

Status: UNCONFIRMED

(Bugzilla :: User Accounts, enhancement)

Description

[Damien Nozay [:dnozay]]

see bug 400160, bug 365209, bug 375287, bug 545770, bug 1021218.

Merging users is not reliable; several reasons:
- difficult to keep track of all fields to update.
- extensions may reference profiles.userid.

solution: query schema.
One could update the contrib/merge-users.pl script with this recipe:

e.g.

==========================================================================================

mysql> use information_schema;
mysql> select table_name, column_name, concat('update ', table_name, ' set ',column_name,' = ','NEWID',' where ',column_name,' = ','OLDID',' ;') as query from key_column_usage where constraint_schema = 'bugs' and referenced_table_name = 'profiles' and referenced_column_name = 'userid';
+-----------------------------+------------------+---------------------------------------------------------------------------------+
| table_name                  | column_name      | query                                                                           |
+-----------------------------+------------------+---------------------------------------------------------------------------------+
| attachments                 | submitter_id     | update attachments set submitter_id = NEWID where submitter_id = OLDID ;        |
| audit_log                   | user_id          | update audit_log set user_id = NEWID where user_id = OLDID ;                    |
| bugs                        | assigned_to      | update bugs set assigned_to = NEWID where assigned_to = OLDID ;                 |
| bugs                        | qa_contact       | update bugs set qa_contact = NEWID where qa_contact = OLDID ;                   |
| bugs                        | reporter         | update bugs set reporter = NEWID where reporter = OLDID ;                       |
| bugs_activity               | who              | update bugs_activity set who = NEWID where who = OLDID ;                        |
| cc                          | who              | update cc set who = NEWID where who = OLDID ;                                   |
| component_cc                | user_id          | update component_cc set user_id = NEWID where user_id = OLDID ;                 |
| component_watch             | user_id          | update component_watch set user_id = NEWID where user_id = OLDID ;              |
| components                  | initialowner     | update components set initialowner = NEWID where initialowner = OLDID ;         |
| components                  | initialqacontact | update components set initialqacontact = NEWID where initialqacontact = OLDID ; |
| components                  | watch_user       | update components set watch_user = NEWID where watch_user = OLDID ;             |
| email_setting               | user_id          | update email_setting set user_id = NEWID where user_id = OLDID ;                |
| flags                       | requestee_id     | update flags set requestee_id = NEWID where requestee_id = OLDID ;              |
| flags                       | setter_id        | update flags set setter_id = NEWID where setter_id = OLDID ;                    |
| login_failure               | user_id          | update login_failure set user_id = NEWID where user_id = OLDID ;                |
| logincookies                | userid           | update logincookies set userid = NEWID where userid = OLDID ;                   |
| longdescs                   | who              | update longdescs set who = NEWID where who = OLDID ;                            |
| namedqueries                | userid           | update namedqueries set userid = NEWID where userid = OLDID ;                   |
| namedqueries_link_in_footer | user_id          | update namedqueries_link_in_footer set user_id = NEWID where user_id = OLDID ;  |
| profile_search              | user_id          | update profile_search set user_id = NEWID where user_id = OLDID ;               |
| profile_setting             | user_id          | update profile_setting set user_id = NEWID where user_id = OLDID ;              |
| profiles_activity           | userid           | update profiles_activity set userid = NEWID where userid = OLDID ;              |
| profiles_activity           | who              | update profiles_activity set who = NEWID where who = OLDID ;                    |
| quips                       | userid           | update quips set userid = NEWID where userid = OLDID ;                          |
| reports                     | user_id          | update reports set user_id = NEWID where user_id = OLDID ;                      |
| series                      | creator          | update series set creator = NEWID where creator = OLDID ;                       |
| tag                         | user_id          | update tag set user_id = NEWID where user_id = OLDID ;                          |
| tokens                      | userid           | update tokens set userid = NEWID where userid = OLDID ;                         |
| user_group_map              | user_id          | update user_group_map set user_id = NEWID where user_id = OLDID ;               |
| votes                       | who              | update votes set who = NEWID where who = OLDID ;                                |
| watch                       | watched          | update watch set watched = NEWID where watched = OLDID ;                        |
| watch                       | watcher          | update watch set watcher = NEWID where watcher = OLDID ;                        |
| whine_events                | owner_userid     | update whine_events set owner_userid = NEWID where owner_userid = OLDID ;       |
+-----------------------------+------------------+---------------------------------------------------------------------------------+
34 rows in set (0.01 sec)

==========================================================================================



Once all relevant entries have been rewritten, you can delete the old profiles row, cookies, etc.

I am sure someone can also write testcases for the merge-users.pl script to query
all relevant tables and check that the old userid is not in use anywhere after the script is run.
This should take care of the script referencing all relevant tables for vanilla installs.

Comment 1

[Frédéric Buclin]

What you suggest won't work when there are UNIQUE indexes set. For instance, (who, bug_id) in the cc table must be unique, and so you cannot blindly replace the old user ID by the new user ID. Bugzilla already automatically takes care of all columns referencing a user ID. No need for extra code for this. What is less trivial is to detect user ID columns involved in a UNIQUE index. This is the only place for improvement.

Comment 2

[:glob ✱]

> - extensions may reference profiles.userid

this can be addressed by adding a hook to merge-users.pl.