Suitecrm: System outbound email settings can not be saved after 7.10.27 upgrade due to duplicate system records

[evgu]

Issue

After 7.10.27 upgrade the outbound email settings (Admin->Email Settings) can not be saved. The upgrade also cleared off the outbound mail settings which were already set preor the upgrade. This broke down all task/meetings and other system notifications that need to use the default outbound email account.

Expected Behavior

Outbound email settings (server host, username, password) can be saved in Admin->Email Settings

Actual Behavior

Outbound email settings (server host, username, password) can NOT be saved in Admin->Email Settings

Steps to Reproduce

1. Upgrade from 7.10.25 to 7.10.27
2. Go to Admin->Email Settings (Issue Observed: previous outbound mailbox entries have been cleared)
3. Enter outbound mailbox settings (hostname, username, password).
4. Send test email (CRM test email is sent fine).
5. Save the Email Settings page.
6. Visit Admin->Email Settings page and all outbound email entires have been cleared.

Context

This broke down all task/meetings and other system notifications that need to use the default outbound email account. Quite urgent to resolve.

Your Environment

• SuiteCRM Version used: 7.10.27
• Browser name and version: Safari 13.1.1; Chrome 83
• Environment name and version: PHP 7.2.31, MySQL 5.6.49
• Operating System and version: CentOS Linux release 7.8.2003

[evgu]

Further investigation shows that this issue is caused when there are multiple type->'system' entries in table outbound_email. Once only one row of type 'system' is left in the table, everything works fine.

Suggested resolution is for the code to be able to handle multiple type: 'system' and 'system-override' entries.

[pgorod]

I thought system account had to be unique. What is the meaning of having more than one? How do they get used?

[evgu]

system had several entries. Only one row had deleted=0, rest were with deleted=1.
i.e. several system outbound email settings set and consequently deleted over the years.

[evgu]

I havent tested it but I think it is the code in include/OutboundEmail/OutboundEmail.php

public function saveSystem()
    {
        $q = "SELECT id FROM outbound_email WHERE type = 'system'";

query will return IDs for deleted rows. I think query should be

$q = "SELECT id FROM outbound_email WHERE type = 'system' AND deleted = 0";

Anyway, we resolved it by simply cleaning up the table from the obsolete (deleted) rows. Issue is resolved for us, but Im leaving the ticket open in case anyone else has a similar issue.

[Mac-Rae]

Thanks for your investigation work, will leave this hear as i know there definitely needs to be some improvements in this area.

[samus-aran]

If anyone is willing to update that SQL via PR please be our guest sings