Mass-edit email of all users

[Raku]

Long story short: I screwed up an import from phpBB. Well okay, that’s not the full story.

The import itself worked flawlessly!
And at this point let me shout out to the whole team for creating an awesome piece of software (from a users point of view, an admins point of view and a devs point of view)!

But…
… I wanted to be on the safe side and edited every users email in the sql file from phpBB. So Discourse won’t send out any mails after the import, because we needed some time to restructrue posts and categories after the import. Please, don’t ask why and how.
After we finished all refurbishing tasks we wanted to edit the users emails back to their original one, only to find out admins can’t edit emails. We ended up impersonating every user and changing their email address back… only to find out the activation link may only be valid for a few (?) hours.
At least we now have users who click the link they received, but Discourse doesn’t change their address.

Luckily I didn’t screw up THAT bad. When editing the users mail in the first place, before the import, I just appended kinda nonce to the @-sign, so the domain is invalid. This means the original data is still there.

My plan is to replace the nonce with an empty string on the database. And that’s where I need help, as I have zero experience with Ruby/Rails.

I already found

Edit a user preference for everyone or a subset of users

If you need to update the user preference for all of your users or a large subset of users, you can do so via the rails console. From console, run these commands to enter the rails console for Discourse ./launcher enter app rails c Then, run a command that selects the set of users you wish to update. Examples Set the last seen date for users who have never logged in User.where("last_seen_at IS NULL").update_all(last_seen_at: 1.week.ago) Unset mailing list mode for all users who may have se…

And that’s quite a good start, but I need further help here.

What I think I need to to - in pseudocode:

./launcher enter app
rails c
User.where("email contains nonce").update_all(replace nonce with empty string)

Or even

User.update_all(replace nonce with empty string in email field)

As the nonce is really just some gibberish behind the @-sign, but the same gibberish for every user. And the ones I already corrected manually won’t be affected by this.

I don’t have a clue what I’m supposed to write in between the parantheses. Or if this (a replace) is even possible.

[Falco]

User.update_all("email = replace(email, 'nonce', '')")

[Raku]

Worked like a charme! Thank you, Falco!

[codinghorror (Jeff Atwood)]

@Raku

only to find out admins can’t edit emails

Yes, they can-- edit it like a user would, from the profile settings.

[Raku]

[quote=“codinghorror, post:4, topic:51687, full:true”]edit it like a user would, from the profile settings.
[/quote]

Oh my… it seems we really overlooked this!
We impersonated each and every user and changed it there.

Will Discourse send out a mail asking for verification of the adress, if an admin changes it in the profile settings (without impersonating the user)?

[omarfilip]

@Falco

User.update_all(“email = replace(email, ‘nonce’, ‘’)”)

I’m trying something similar, but getting a readonly error. How do I get around it? This is not a production instance, so it’s OK if I screw up.

User.update_all("email = replace(email, '@', '@invalid_email')")
ActiveRecord::StatementInvalid: PG::RaiseException: ERROR:  Discourse: email in users is readonly
: UPDATE "users" SET email = replace(email, '@', '@invalid_email')
from /var/www/discourse/vendor/bundle/ruby/2.4.0/gems/rack-mini-profiler-0.10.5/lib/patches/db/pg.rb:90:in `async_exec'

[fefrei (Felix Freiberger)]

Emails have moved to a separate table called user_emails (behind the scenes, users now can have multiple email addresses), so this command will have to be rewritten.

This may work:

UserEmail.update_all("email = replace(email, 'nonce', '')")

…or it might set fire to the database.
You’ve been warned

[omarfilip]

No fire! Thanks Felix!