Resolve invalid email addresses when migrating to the Cloud

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

プラットフォームについて: Data Center のみ - この記事は、Data Center プラットフォームのアトラシアン製品にのみ適用されます。

要約

When migrating from Server/DC to Cloud, the Migration Assistant apps run a set of pre-checks to verify the Source instance does not have users with invalid email addresses. Atlassian cloud generates user accounts based on unique and valid email addresses. Content in Jira and Confluence will be migrated and linked to users depending on their email addresses.

Identify

Verify the Jira Cloud Migration Assistant or Confluence Cloud Migration Assistant app is installed. Create a new migration plan and run the pre-checks to scan the source instance. If there are conflicts of duplicate email addresses you will see the following:

Notice the User section includes a dropdown arrow that can be extended. Selecting that will provide you with the exact users that have a conflict:


Identify via Database

To identify users impacted, you can query the database by running a SQL query similar to the following:

Postgres


SELECT email_address
     , user_name 
  FROM cwd_user
 WHERE email_address !~ '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$'
    OR length(substring(email_address from '(.*)@')) > 64;

Oracle

SELECT email_address
     , user_name
  FROM cwd_user
 WHERE NOT REGEXP_LIKE (email_address, '^[A-Za-z0-9\.\!\#\$\%\&\''\*\+\-\/\=\?\^\_\|\~\(\)]+@([A-Za-z0-9_-]+\.?)+$', 'i')
    OR instr(email_address, '@') > 64;

MySQL Server

SELECT email_address
     , user_name
  FROM cwd_user
 WHERE email_address NOT REGEXP '^[A-Za-z0-9\.\!\#\$\%\&\'\*\+\-\/\=\?\^\_\|\~\(\)]+@([A-Za-z0-9_-]+\.?)+$'
    OR LENGTH(substring_index(email_address, '@', 1)) > 64;

MS SQL Server

SELECT email_address
     , user_name
  FROM cwd_user
 WHERE NOT email_address LIKE '%_@%__%.__%'
    OR NOT PATINDEX('%[^a-z,0-9,@,.,_,\-]%', email_address) = 0
    OR charindex('@', email_address) > 64;
NOTE: The SQL query may need to be adjusted depending on the database. Also, the query makes the assumption that the column email_address and lower_email_address have consistent data.


ソリューション

Manually create a unique, valid email address

The most efficient way to resolve invalid emails is to update the email addresses for all users from the UI. 

  1. Select Administration () User Management.
  2. ページの一番上にあるフィルターを使用してユーザー一覧からユーザーを探します。
  3. [操作] 列の [編集] をクリックします。
  4. ユーザー名、氏名、またはメール アドレスを変更し、[更新] をクリックして完了します。

Update users via Database

Another option is to update users via the database. This method can quickly update the cwd_user table which is used by the Migration Assistant tools to validate email addresses.

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

Postgres

update cwd_user 
set email_address = 'invaliduser-' || id || '@domain.com', 
    lower_email_address = 'invaliduser-' || id || '@domain.com'
    where lower_email_address in (select lower_email_address from cwd_user where lower_email_address !~ '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$');

Oracle

UPDATE cwd_user
SET email_address = 'invalideuser-' || id || '@domain.com',
    lower_email_address = 'invalideuser-' || id || '@domain.com'
    WHERE  lower_email_address in (SELECT lower_email_address
FROM cwd_user
WHERE NOT REGEXP_LIKE (lower_email_address, '^[A-Za-z0-9\.\!\#\$\%\&\''\*\+\-\/\=\?\^\_\|\~\(\)]+@([A-Za-z0-9_-]+\.?)+$', 'i'));

MySQL Server

UPDATE cwd_user
SET email_address = 'invalideuser-' || id || '@domain.com',
    lower_email_address = 'invalideuser-' || id || '@domain.com'
WHERE email_address NOT REGEXP '^[A-Za-z0-9\.\!\#\$\%\&\'\*\+\-\/\=\?\^\_\|\~\(\)]+@([A-Za-z0-9_-]+\.?)+$';

MS SQL Server

UPDATE cwd_user
SET email_address = CONCAT('invaliduser-',id,'@domain.com'),
    lower_email_address = CONCAT('invaliduser-',id,'@domain.com')
    WHERE lower_email_address in (SELECT lower_email_address
FROM cwd_user WHERE not lower_email_address LIKE '%_@__%.__%'or not PATINDEX('%[^a-z,0-9,@,.,_,\-]%', lower_email_address) = 0);


Running this query will update the email address of every user that has an invalid email address. The new email that gets created will be in the following format: invaliduser-<id>@domain.com. Make sure to adjust this accordingly by adding a specific domain or changing the format. After completing this step, you must restart Jira or Confluence in order to clear the cache. If you are unable to restart the instance, you need to clear the user cache so the email address updates are recognized by the Migration Assistant app. The following curl command can help clear the cache. You need to add username/password as well as update the instance URL to run the command.

curl -X DELETE -u admin:admin http://HOST/rest/internal/1.0/cache/app




最終更新日 2022 年 9 月 29 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.