HowTo FixUpImportedUsers » History » Version 1

Adrian Wilkins, 2012-06-08 18:36

1 1 Adrian Wilkins
h1. Migrating Old Users
2 1 Adrian Wilkins
3 1 Adrian Wilkins
When you migrate from Trac, you get crufty old users.
4 1 Adrian Wilkins
5 1 Adrian Wilkins
This also happens when you switch from one system of auth to another, like hand-crafted users to LDAP.
6 1 Adrian Wilkins
7 1 Adrian Wilkins
This approach was written for Redmine 1.3.3 running on MySQL.
8 1 Adrian Wilkins
9 1 Adrian Wilkins
h2. Old users table
10 1 Adrian Wilkins
11 1 Adrian Wilkins
Create this table with the script and populate it with the old and new user login names. The new users should exist already, so create them manually or with something like the message#6867
12 1 Adrian Wilkins
13 1 Adrian Wilkins
<pre><code class="sql">
14 1 Adrian Wilkins
/* Fill this table with the old login name and the new login name */
15 1 Adrian Wilkins
CREATE TABLE `old_users` (
16 1 Adrian Wilkins
  `old_login` varchar(30) NOT NULL,
17 1 Adrian Wilkins
  `new_login` varchar(30) NOT NULL,
18 1 Adrian Wilkins
  UNIQUE KEY `old_login_UNIQUE` (`old_login`)
19 1 Adrian Wilkins
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
20 1 Adrian Wilkins
</code></pre>
21 1 Adrian Wilkins
22 1 Adrian Wilkins
h2. User migration procedure
23 1 Adrian Wilkins
24 1 Adrian Wilkins
<pre><code class="sql">
25 1 Adrian Wilkins
26 1 Adrian Wilkins
DROP PROCEDURE IF EXISTS fixup_oldusers;
27 1 Adrian Wilkins
DELIMITER //
28 1 Adrian Wilkins
CREATE PROCEDURE fixup_oldusers()
29 1 Adrian Wilkins
BEGIN
30 1 Adrian Wilkins
    DECLARE done INT DEFAULT FALSE;
31 1 Adrian Wilkins
    DECLARE oldid, newid INT;
32 1 Adrian Wilkins
33 1 Adrian Wilkins
    DECLARE oldcur CURSOR FOR 
34 1 Adrian Wilkins
        SELECT u1.id as old_id, u2.id as new_id FROM old_users
35 1 Adrian Wilkins
            JOIN users u1 on old_login = u1.login
36 1 Adrian Wilkins
            JOIN users u2 on new_login = u2.login;
37 1 Adrian Wilkins
38 1 Adrian Wilkins
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
39 1 Adrian Wilkins
    
40 1 Adrian Wilkins
    OPEN oldcur;
41 1 Adrian Wilkins
42 1 Adrian Wilkins
    read_loop: LOOP
43 1 Adrian Wilkins
        FETCH oldcur into oldid, newid;
44 1 Adrian Wilkins
45 1 Adrian Wilkins
        IF done THEN
46 1 Adrian Wilkins
            LEAVE read_loop;
47 1 Adrian Wilkins
        END IF;
48 1 Adrian Wilkins
49 1 Adrian Wilkins
        IF oldid != newid THEN
50 1 Adrian Wilkins
51 1 Adrian Wilkins
            UPDATE journals SET user_id = newid WHERE user_id = oldid;
52 1 Adrian Wilkins
            UPDATE attachments SET author_id = newid WHERE author_id = oldid;
53 1 Adrian Wilkins
            UPDATE wiki_contents SET author_id = newid WHERE author_id = oldid;
54 1 Adrian Wilkins
            UPDATE wiki_content_versions SET author_id = newid WHERE author_id = oldid;
55 1 Adrian Wilkins
            UPDATE time_entries SET user_id = newid WHERE user_id = oldid;
56 1 Adrian Wilkins
            UPDATE news SET author_id = newid WHERE author_id = oldid;
57 1 Adrian Wilkins
            UPDATE issue_categories SET assigned_to_id = newid WHERE assigned_to_id = oldid;
58 1 Adrian Wilkins
            UPDATE comments SET author_id = newid WHERE author_id = oldid;
59 1 Adrian Wilkins
            UPDATE changesets SET user_id = newid WHERE user_id = oldid;
60 1 Adrian Wilkins
            UPDATE queries SET user_id = newid WHERE user_id = oldid;
61 1 Adrian Wilkins
            UPDATE messages SET author_id = newid WHERE author_id = oldid;
62 1 Adrian Wilkins
            UPDATE messages SET last_reply_id = newid WHERE last_reply_id = oldid;
63 1 Adrian Wilkins
            UPDATE issues SET assigned_to_id = newid WHERE assigned_to_id = oldid;
64 1 Adrian Wilkins
            UPDATE issues SET author_id = newid WHERE author_id = oldid;
65 1 Adrian Wilkins
66 1 Adrian Wilkins
            UPDATE journal_details SET old_value = newid WHERE prop_key = 'assigned_to_id' AND old_value = oldid;
67 1 Adrian Wilkins
            UPDATE journal_details SET value = newid WHERE prop_key = 'assigned_to_id' AND value = oldid;
68 1 Adrian Wilkins
69 1 Adrian Wilkins
            /* Delete old user data */
70 1 Adrian Wilkins
            DELETE FROM member_roles WHERE member_id = oldid;
71 1 Adrian Wilkins
            DELETE FROM members WHERE user_id = oldid;
72 1 Adrian Wilkins
            DELETE FROM user_preferences WHERE user_id = oldid;
73 1 Adrian Wilkins
            DELETE FROM users WHERE id = oldid;
74 1 Adrian Wilkins
            
75 1 Adrian Wilkins
        END IF;
76 1 Adrian Wilkins
    END LOOP;
77 1 Adrian Wilkins
78 1 Adrian Wilkins
    CLOSE oldcur;
79 1 Adrian Wilkins
80 1 Adrian Wilkins
END//
81 1 Adrian Wilkins
DELIMITER ;
82 1 Adrian Wilkins
</code></pre>
83 1 Adrian Wilkins
84 1 Adrian Wilkins
h2. Execute procedure (from MySQL Workbench)
85 1 Adrian Wilkins
86 1 Adrian Wilkins
<pre><code class="sql">
87 1 Adrian Wilkins
SET SQL_SAFE_UPDATES = 0;
88 1 Adrian Wilkins
CALL fixup_oldusers;
89 1 Adrian Wilkins
SET SQL_SAFE_UPDATES = 1;
90 1 Adrian Wilkins
</code></pre>