Change email adress of all users directly in the database

Lobby
1

Hello,

for a test environment i’ve made a copy of my productive server. To ensure that no real customer gets an email while i’m testing some things, i want to change all email adresses of all users to a domain, where i have a catchall activated.

I have a SQL statement which updated the “email”-field in the users table and the “from”-field in the ticket_articles table. But the test system still shows the old email adresses. Is there anything what i’m missing or is my way of changing all data not possible?

For my test server i need the tickets from the productive server because i want to test some kind of mass updating tickets (~500-1000 tickets) via the api.

2

Hi @richie_77,

not sure if it is within the scope of your testing, but I can suggest to use mailpit as local mailserver instead of your production system.

This enables you to test stuff with a virtual mail server.
More information in this thread:

If it is out of scope, excuse my failed response to your specific request.

Best,
Skip

3

Changing values directly inside the database, without Zammad in between in any way is not supported nor encouraged. You’re skipping important data consistency checks and will run in all sort of issues, e.g. in terms of caching etc.

This is out of scope. You’re on your own.

4

The other option, instead of going through the SQL and change e-mail addresses is to export the users through the API and update them with the new and changed e-mail address again through the API.

5

Or the rails console. :slight_smile:

6

That was my naive idea of ​​an easy way. :face_with_peeking_eye:

I will try some code blocks/for-each-loops in the rails console.

7

The mailserver is already a virtualized postfix instance. Sure, i could make a copy of the mailserver and do some blocking of all outbound mails or some kind of redirecting, but that would be more work. The “changing all mail addresses”-way looks easier to implement.

8

In the Rails console:

zammad run rails r "User.where(\"email LIKE ?\", \"%@old-domain.com\").find_each { |u| u.email = u.email.sub(/@old-domain\.com$/, '@new-domain.com'); u.save! }"

This SHOULD (I haven’t tested it!) update all users whose email ends with @old-domain.com, replacing it with @new-domain.com.

Note:
• Always back up your database before running updates.
• You may need to clear the cache afterwards: zammad run rails r ‘Cache.clear’.

1 Like
9

I’ve done it in some other way, because there are many different"@old-domain". Now every possible email field is filled with a $randomizedsentence@mydomain.com. The mail server at mydomain.com has a catchall, so every mail send from zammad to any of the users will reach the catchall mailbox.