The mailing list for listmasters using Sympa

[Jérôme Zittoun <address@concealed>]

Chris,

 

first of all, backup your Sympa database :-)

After this, backup your Sympa database :-)))

 

the answer depends on what you really want to do.

1/ You want to change all the email addresses in a precise date and at one time

the SQL syntax is quite yours :

UPDATE subscriber_table SET user_subscriber = REPLACE(user_subscriber, '@wm.edu','@email.wm.edu')

And the whole table will be changed with only one query.

 

 

2/ you want to manage a transition during the rest of the year. Students should post messages with one or the other FROM address (address@concealed or address@concealed), but for the moment should receive messages into only one mailbox.

 

Actually, Sympa knows only one address (address@concealed).

It has to know the other one (address@concealed)

 

a. You have to duplicate all data

CREATE TABLE myDuplicateTable AS SELECT * FROM subscriber_table

 

b. After this, modify the domain name

UPDATE myDuplicateTable SET user_subscriber = REPLACE(user_subscriber, '@wm.edu','@email.wm.edu')

 

c1. If you want they receive messages into the old mailbox, use

UPDATE myDuplicateTable SET reception_subscriber = 'nomail'

 

-- or --

 

c2. If you want they receive messages into the new mailbox, use

UPDATE subscriber_table SET reception_subscriber = 'nomail'

 

d. append the new records into the original table

INSERT INTO subscriber_table SELECT * FROM myDuplicateTable

 

e. delete the myDuplicateTable

DROP TABLE myDuplicateTable

 

After this, students will have two legacy FROM addresses, but messages will be send at only one address.

 

When you are ready to abend the old addresses (after another database backup, of course) :

 

f1. you had selected the c1 option, you'll have to use  :

UPDATE subscriber_table SET user_subscriber = REPLACE(user_subscriber, '@wm.edu','@email.wm.edu.bak')

DELETE FROM subscriber_table WHERE user_subscriber LIKE '%@email.wm.edu'

UPDATE subscriber_table SET user_subscriber = REPLACE(user_subscriber, '@email.wm.edu.bak','@email.wm.edu')

 

-- or --

 

f2. you had selected the c2 option, you'll have to use (after another database backup, of course) :

DELETE FROM subscriber_table WHERE user_subscriber LIKE '%@wm.edu'

 

The difference between f1 and f2 is that you have to preserve the original data (especially the reception_subscriber field)

 

Don't hesitate to simulate this operations with test tables.

 

Hope this help.

Jérôme

--
Jérôme Zittoun
Erigne Informatique / DZ2
http://www.erigne.com

---

De : address@concealed [mailto:address@concealed] De la part de Chris Peck
Envoyé : vendredi 5 mars 2010 17:17
À : address@concealed
Objet : Re: [sympa-users] We are moving all of our students to a new email

Serge,

This may be the way - we can generate a list of students (approx 10000) and create a sql change statement for each one -  I just ran across and old entry from 2004 in the archive https://listes.cru.fr/sympa/arc/sympa-users/2004-12/msg00003.html

So it would be? (bear with me - i'm not a sql coder)

use sympadb;
UPDATE subscriber_table SET email=REPLACE(email, 'address@concealed','address@concealed');
UPDATE subscriber_table SET email=REPLACE(email, 'address@concealed','address@concealed');
ad nauseum...

-chris

On Mar 5, 2010, at 10:51 AM, Serge Aumont wrote:

On 03/05/2010 04:23 PM, Chris Peck wrote:

However, sending email to lists as well as admin'ing sympa list via email commands will most likely be problematical.  Has anyone run into this in the past? Does anyone have an ideas on how I can  circumvent any authorization issues without having to modify every list config that may be effected?

To be honest, I haven't started digging into this yet, I'm thinking that as a lasts resort I can use postfix to change incoming email from @email.wm.edu to be from @wm.edu, but, that's a short term solution and a hack at best.

We are currently running v 5.3.3 and assuming all goes well I hope to upgrade to the most current this summer.

Why not rewrite all related subscription from old email form to the new
one ? This coud be done via sql in subscriber_table . Of course, this
suppose to be able to have a list all student email to avoid change in
staff and teachers emails.

Serge