Cannot add mail accounts: ERROR:outgoing:mails.domain_id may not be NULL

Created:

2016-11-16 12:54:11 UTC

Modified:

2017-04-24 12:08:08 UTC

0

Was this article helpful?


Have more questions?

Submit a request

Cannot add mail accounts: ERROR:outgoing:mails.domain_id may not be NULL

Applicable to:

  • Plesk 11.5 for Linux
  • Plesk 12.0 for Windows
  • Plesk 12.0 for Linux

Symptoms

Plesk creates only one domain from the system in the /var/spool/postfix/plesk/passwd.db when viewed with /usr/local/psa/admin/bin/mail_auth_view .

Only users from the example.com domain have access to emails. The rest of users do not have access due to invalid username/password authentication. There is no problem in adding new email accounts to the working domain. When adding the new email accounts to the problematic domains, the following error message appears:

Error: mailmng-outgoing failed: ERROR:outgoing:mails.domain_id may not be NULL

Cause

Database inconsistency: psa.DomainServices in the database contains NULL ipCollectionId fields

mysql> select * from DomainServices where ipCollectionId is NULL;
+----+--------+-----------+--------+---------------+----------------+
| id | dom_id | type | status | parameters_id | ipCollectionId |
+----+--------+-----------+--------+---------------+----------------+
| 3 | 3 | mail | 0 | 3 | NULL |
| 6 | 6 | mail | 0 | 6 | NULL |

Resolution

  1. Get existing domains list with IP addresses currently assigned to the web hosting and put it into a temporary table:

    mysql> CREATE TEMPORARY TABLE dom_ip as (SELECT dom.id as dom_id, dom.name as dom_name, ia.ipAddressId as ip_ipAddressId, iad.ip_address as ip_ip_address FROM domains dom LEFT JOIN DomainServices d ON (dom.id = d.dom_id AND d.type = 'web') LEFT JOIN IpAddressesCollections ia ON ia.ipCollectionId = d.ipCollectionId LEFT JOIN IP_Addresses iad ON iad.id = ia.ipAddressId);

    +--------+------------+----------------+-----------------+
    | dom_id | dom_name | ip_ipAddressId | ip_ip_address |
    +--------+------------+----------------+-----------------+
    | 3 |example.com | 2 | 169.254.120.142 |
    | 6 |example2.com| 9 | 169.254.122.136 |
  2. Merge DomainServices with dom_ip :

    mysql> CREATE TEMPORARY TABLE ds_ip as (select di.*, ds.type as ds_type, ds.ipCollectionId as ds_ipCollectionId, ds.dom_id as ds_dom_id from DomainServices ds, dom_ip di where ds.dom_id=di.dom_id and ds.ipCollectionId is NULL);
  3. For every entry in IpAddressesCollections and IpCollections a new record should be created:

    mysql> select @i:=max(ipCollectionId)+1 from IpAddressesCollections; insert into IpCollections values(@i);  insert into IpAddressesCollections values (@i,2);
    +---------------------------+
    | @i:=max(ipCollectionId)+1 |
    +---------------------------+
    | 72 |
    +---------------------------+
    1 row in set (0.00 sec)
  4. Update table with the relevant data for mail or for web types:

    mysql> begin;
    mysql> update DomainServices, ds_ip set DomainServices.ipCollectionId=ds_ip.ds_ipCollectionId where DomainServices.type='mail' and DomainServices.dom_id=ds_ip.ds_dom_id and DomainServices.ipCollectionId is NULL;
    mysql> commit;
  5. Activate and deactivate Mail Service for the domain to recreate the mail configuration.

Have more questions? Submit a request
Please sign in to leave a comment.