Need Bouce MySQL command to purge bounced records...

[BGSWebDesign]

Hi Dw and All,

Ok, it seems bouncing is working fine now, BUT, the only thing is there is no way to move the bounced records OUT of the database to a 'purged' or 'deleted' table or remove them completely from the  table to free up the space they are taking up.

Do you have a SQL command DW, or does anyone that would dump the bounced records to a text file (with sql commands similar to mysqldump) so that I can purge these bounced records?

[DW]

Here's a command for removing all bounced users on list 1:

delete from lm_users where cnf = '3' and list = '1'

And for all lists:

delete from lm_users where cnf = '3'

Regards

[BGSWebDesign]

Hi DW,

Just a quick followup on this, so I can keep track of what I did, and it may help others.

If you want to save your Bounced Users, which is probably useful, you can create another table 'lm_bounced' to store them in.  To create that table from the lm_users table use this command (NOTE: this only works for MySQL ver. 4.x and above, if you don't have 4.x or above, see below for creating without indexes):

Code: [Select]

CREATE TABLE lm_bounced LIKE lm_users;

If you don't want the indexes, which I don't care about, but maybe they are needed (DW?), are they needed to pull queries from, for example with the new Rule Based Sending coming in the next release, are indexes needed for that?   Also, how much space do the indexes eat up, andy idea?  Perhaps you can give the commands here needed to index the lm_users table DW?

Anyway, if you don't need indexes you can do this:

Code: [Select]

CREATE TABLE lm_bounced SELECT * FROM lm_users WHERE 1=2;

Since 1 <> 2 you will not get any records, but will get all of the correct fields setup.

Now, to copy the bounced records use this command:

Code: [Select]

INSERT INTO lm_bounced
SELECT *
FROM lm_users
WHERE cnf = '3'

This should tell you how many rows were copied, then you can delete the bounced users using the command:

Code: [Select]

delete from lm_users where cnf = '3'

Ok, that takes ALL of your bounced users and moves them to a new table lm_bounced, but you can also do it by list, if you wanted to, I don't have a need to, and this was good enough.