Bulk email address change for JIRA 5.1.2

Kathleen Buckley August 27, 2012

We have tried using the following script to update (in bulk) the domain names of our email addresses, but it's not working. Anyone know what is wrong with our script? We are running JIRA 5.1.2 and out database is Oracle 11g.

UPDATE CWD_USER
SET email_address = user_name || '@noemail.com',
lower_email_address = lower(user_name) || '@noemail.com',
updated_date = sysdate
WHERE email_address not like '%@currentdomainname.com'

3 answers

1 accepted

1 vote
Answer accepted
Matt
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 28, 2012

It is a dumb method, but worked for me.

Have you tried to dump everything to xml backup file, and then using email regex like this:

="^([0-9a-zA-Z]([-\.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$"

or simple: ="(.*?)@(.*).[a-zA-Z]{2,4}"

Kathleen Buckley August 28, 2012

We were getting ready to try editing the xml backup file (find/replace all) but my co-worker said you're method was even better. We'll try it out tomorrow. Thanks - we'll let you know if it worked.

1 vote
Mauro Badii
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
July 31, 2014
0 votes
IELC Account (closed) August 7, 2013

In the test environment (Jira v5.2.10 + Oracle 11.2.0.3.0) we :

1. stopped Jira
2. did an direct SQL Update:
update cwd_user
set email_address = substr(email_address, 1, instr(email_address, '@')) || 'newmaildomain.xxx'
,lower_email_address = substr(lower_email_address, 1, instr(email_address, '@')) || 'newmaildomain.xxx'
where email_address like '%@oldmaildomain.xxx';
3. started Jira

The email address is correctly shown, even in the opened issues.

Question:

What was not working in your case?

Suggest an answer

Log in or Sign up to answer