Redmine

h1. 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.

h2. 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 message#6867

<pre><code class="sql">

/* 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;

</code></pre>

h2. User migration procedure

<pre><code class="sql">

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 ;

</code></pre>

h2. Execute procedure (from MySQL Workbench)

<pre><code class="sql">

SET SQL_SAFE_UPDATES = 0;

CALL fixup_oldusers;

SET SQL_SAFE_UPDATES = 1;

</code></pre>