Well FWIW, I did the checks manually (diff'ing the output of 'SELECT' vs 'SELECT DISTINCT') and it turned out that most duplicates had to do with addresses which had uppercase characters it seems older versions of dada allowed this, so that, if I got 'El Baby@gmail com' in the database and then tried to insert 'el baby@gmail com' I'd succeed ('cause now the verification process is always done in lowercase)

I simply did the following:

UPDATE dada_subscribers SET email=lower(email) WHERE BINARY email <> lower(email);

thus, lowercasing all addresses in the database, and then looked for the duplicated ones (with manual selects) and erased the duplicates (directly from MySQL) with something like:

DELETE FROM dada_subscribers WHERE email_id in (nnn, mmm, xxx, yyy, zzz);

(where I previously had gathered the email_ids of the duplicated addresses)

On 9/11/07, Mariano Absatz el.baby@gmail com wrote: >

On 9/11/07, Henry Hughes henry@capitalh net wrote: >

Mariano,

The table that holds the email address can contain more than one list, that the same email address may be subscribed to: I know that (in fact, I do have a couple of lists that have subscribers in common)

> >

CREATE TABLE dada_subscribers ( email_id int4 not null primary key auto_increment, email text(320), list varchar(16), list_type varchar(64), list_status char(1) );

Therefore it is very possible that at first glance you could have duplicates, but check the 'list' field too, as this is the key that differentiates between one list and another Yeah, but this is not the case that is, it happened several times that I searched for an address (via the web mail cgi) and it appeared more than once and (also via the web mail cgi), if you select only one of those and hit "delete", it will erase both (since it does the sql DELETE based on 'email' and not on 'email_id', which is fine for me

>

SELECT DISTINCT(email) FROM dada_subscribers WHERE list='LISTNAME'

The above query should give you a list of unique addresses from your subscriber table Subsitute LISTNAME for your own Yes but that's the easy one I want to see which one of those are repeated well I think maybe a (very) dirty solution would be to issue that query and the same one without the 'DISTINCT' and 'diff' both results

>

Not tested or even ran the query myself though, as no access to a test dada host at present

Don't worry, thanx anyway

-- Mariano Absatz - El Baby el (dot) baby (AT) gmail (dot) com el (punto) baby (ARROBA:@) gmail (punto) com

--

Post: dadadev@PROTECTED

Unsubscribe: http://mojo skazat com/cgi-bin/dada/mail cgi/u/dadadev/

List Information: http://mojo skazat com/cgi-bin/dada/mail cgi/list/dadadev

Archive: http://mojo skazat com/cgi-bin/dada/mail cgi/archive/dadadev

Mailing List Powered by Dada Mail http://mojo skazat com/cgi-bin/dada/mail cgi/what_is_dada_mail/