Can deleting emails via MySQL screw up Sugar ?

[dstrickler]

I posted this to Sugar, but since they may take a few hours to get back to me, I thought I would post this to the Forum as well:

We have recently moved our Support team to Sugar, and have found a disturbing problem. We are seeing many duplicate emails, and many emails appear and dissapear in Sugar. Possibly the 'pointers' are messed up.

However, because we get rouge emails in the Group Inbox that are easly identified and deleted, I have a cron job that does a "DELETE" in MySQL to clean these out.

Is it OK to "DELETE" these are opposed to "SET delete = 1" in MySQL ?

I have done a 'Database Repair' in Sugar, but its no help.

Using Sugar Pro v4.2d, Linux, PHP 4.3.4, Apache 2.0

[sugarchris]

I think this is your problem:

Contact A (Joe Schmoe) has email "info@schmoe.com".
Contact B (Jane Doe) has email "info@schmoe.com".

When InboundEmail (I-E) links an email, it searches based on email address and links all hits.

This being said, the Email views are joined against the contacts table to fill in the Contacts column - a given email can be related to 0, 1 or more contacts, which means that it *can* appear more than once in a ListView.

If the latter case happens ("or more"), you will see that email as many times as you have contacts with that email address.

So, one of your support engineers thinks, "Oh, dupe. Delete one of em'!" S/he does just that, not knowing the dupe row is the same email.

This is a known issue. It happens anywhere a Contact can be 1-to-many'd in a join (Accounts, Opportunities, etc.).

Rule of thumb, don't delete dupes.

This being said, a logical delete (deleted = 1) can be easily restored; just set it back to 0.

[dstrickler]

Actually, no, that's not the problem. But it's a good answer to another question we figured out a while ago on the dupes.
The problem now focuses on dissapearing emails. Example:

User abc@domain.com emails support@mailwise.com and we know the email came in, as we can see it in our Portal (where it hits before entering the IMAP4 server that Sugar polls). Then Sugar polls the email, and puts it in the Group Inbox. So far so good.

Now multiple Support people say they never saw it in the box. I assume they are nuts, as I see it myself, but as I refresh my screen, it dissapears. I can't find it anywhere in the system. It's as if something deleted it from the system.

We have seen this multiple times, and its getting a little scarry. We have been able to trace it over and over again. Always inbound email, and sometimes we see it in Sugar and sometimes we don't. Most of the time it stays in Sugar's inbox, but sometimes they dissapear.

Of course, this is a huge issue for our Support staff that's trying to be good do-bees, and answer all the email, and now they are fearing lost emails, which we can prove.

The only thing we are doing that's not "standard" is pervious today, I was issuing a SQL "DELETE" to clean out some junk from the mailbox, rather than a "SET deleted=1".

Could the "DELETE" have messed with some pointers?