emails and emails_text tables too large

[agcilantro]

I recently added cron jobs to my sugar 5.0.0e installation to run nightly email campaigns. I have not yet sent out any campaigns, but every night the job runs and my emails and emails text tables grow to over 9 megs and 80 megs respectively. I have been using imap on two email accounts for correspondence.
How do i keep these folders from growing?
Is there a way to prune these tables?

Thanks for any help and suggestions.

Running on 1and1 business web accounts.

[jjwdesign]

I have the same issue.

I don't run any email campaigns, but I do have a 20+ sales people using the system with the email module. Our emails_text table has now grown to over 421 MiB; according to phpMyAdmin. Our entire database (MySQL - INNODB) has grown to over 1.3 GiB.

Would love to hear back from developers if we can prune the emails_text table.

Thanks.

[jjwdesign]

This is becoming a serious issue. Our emails_text table keeps growing. Does anyone have any suggestions?

Thanks.

[chad.hutchins]

That table is just going to continue to grow. It contains all of the email content of any and every email sent out through Sugar. You could remove all the records that correspond to an email campaign, but I'm not sure what that would to the linkage between contacts and campaigns (or at least empty the description and description_html fields in the email_text table so there would still be a trail?).

Anyone else?

[jjwdesign]

Thanks for the reply Chad. We are not even using the email campaigns, so most of our emails are from our sales activities. I wish there was a date entered field in the emails_text, or something that could help me filter out the junk that is not needed or old.

Dreaming of the small database days...

[agcilantro]

Every time I have tried to delete some of the email information using phpadmin I've lost the linking information to emails used in campaigns. I had to abandon one of my installations of sugar and move to a different host that didn't have a 100mb limit per database.
There should be a way to archive the email data locally and save a pointer to it in the sugar database. Not that I would have any idea how that would be done.

[sgandhi]

jjwdesign,
Emails table has date_entered column. Emails table and emails_text table has one-one relationship. So if you delete any record from emails_text table, correspoding entry from the emails table should be removed. When you upgrade from 451 to 50 version lots of columns from the old emails where actuall moved to emails_text table like rawsource. description, description_html. You need to remove those column from the emails table. I am not sure that you allowed upgrade wizard to remove those columns or not as when you do the upgrade, wizard will give you complete sql which you can run it manually ot let wizard run that sql which will drop all those columns from emails table.

Let me know if you ahve any more questions.

Thanks
-Samir Gandhi

[kineonicholas]

We are running Sugar CE 5.0.0f. We recently initiated a newsletter campaign which went out to ~6000 targets, however this caused the emails_text table to increase in size by around 90MB.

I subsequently found that there is actually an option to disable storing multiple copies of the same email for newsletters -

Admin / Campaign Email Settings / Keep Copies of Campaign Messages NO

Now our Sugar is storing 1 email per newsletter rather than 6000! Hope this helps.

[wjohnson]

Our emails_text table crashed yesterday and our emails table has crashed previously. The emails table is 1.2 gigs and the emails_text table is 882 MB. Anybody know what causes this? It would also be nice to know if there is a way to have an alert emailed to me when this occurs so that I can fix it immediately.

[sunside]

I am not sure that you allowed upgrade wizard to remove those columns or not as when you do the upgrade, wizard will give you complete sql which you can run it manually ot let wizard run that sql which will drop all those columns from emails table.

Well, I was not asked during upgrading to do these schema changes (seriously!). I already wondered about all these old fields and tables in the database.

Is there any place where I can find a .sql file that will do the trick for me (coming from 4.5.1 to 5.1.0)?

In SugarCE-Upgrade-4.5.1-to-5.1.0/scripts (i.e. in the upgrade .zip file), there is a file called drop_451_schema_after_upgrade_510.php, which contains a function called drop_preUpgardeSchema (!). It seems to contain these statements, but from where can I call it?

Thank you for your help!

Cheers
Sunside