Migrating Old Users¶

When you migrate from Trac, you get crufty old users.

This also happens when you switch from one system of auth to another, like hand-crafted users to LDAP.

This approach was written for Redmine 1.3.3 running on MySQL.

Old users table¶

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 User CSV import plugin v0.0.1

/* Fill this table with the old login name and the new login name */
CREATE TABLE `old_users` (
  `old_login` varchar(30) NOT NULL,
  `new_login` varchar(30) NOT NULL,
  UNIQUE KEY `old_login_UNIQUE` (`old_login`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

User migration procedure¶

DROP PROCEDURE IF EXISTS fixup_oldusers;
DELIMITER //
CREATE PROCEDURE fixup_oldusers()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE oldid, newid INT;

    DECLARE oldcur CURSOR FOR 
        SELECT u1.id as old_id, u2.id as new_id FROM old_users
            JOIN users u1 on old_login = u1.login
            JOIN users u2 on new_login = u2.login;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN oldcur;

    read_loop: LOOP
        FETCH oldcur into oldid, newid;

        IF done THEN
            LEAVE read_loop;
        END IF;

        IF oldid != newid THEN

            UPDATE journals SET user_id = newid WHERE user_id = oldid;
            UPDATE attachments SET author_id = newid WHERE author_id = oldid;
            UPDATE wiki_contents SET author_id = newid WHERE author_id = oldid;
            UPDATE wiki_content_versions SET author_id = newid WHERE author_id = oldid;
            UPDATE time_entries SET user_id = newid WHERE user_id = oldid;
            UPDATE news SET author_id = newid WHERE author_id = oldid;
            UPDATE issue_categories SET assigned_to_id = newid WHERE assigned_to_id = oldid;
            UPDATE comments SET author_id = newid WHERE author_id = oldid;
            UPDATE changesets SET user_id = newid WHERE user_id = oldid;
            UPDATE queries SET user_id = newid WHERE user_id = oldid;
            UPDATE messages SET author_id = newid WHERE author_id = oldid;
            UPDATE messages SET last_reply_id = newid WHERE last_reply_id = oldid;
            UPDATE issues SET assigned_to_id = newid WHERE assigned_to_id = oldid;
            UPDATE issues SET author_id = newid WHERE author_id = oldid;

            UPDATE journal_details SET old_value = newid WHERE prop_key = 'assigned_to_id' AND old_value = oldid;
            UPDATE journal_details SET value = newid WHERE prop_key = 'assigned_to_id' AND value = oldid;

            /* Delete old user data */
            DELETE FROM member_roles WHERE member_id = oldid;
            DELETE FROM members WHERE user_id = oldid;
            DELETE FROM user_preferences WHERE user_id = oldid;
            DELETE FROM users WHERE id = oldid;

        END IF;
    END LOOP;

    CLOSE oldcur;

END//
DELIMITER ;

Execute procedure (from MySQL Workbench)¶

SET SQL_SAFE_UPDATES = 0;
CALL fixup_oldusers;
SET SQL_SAFE_UPDATES = 1;