Domain or mail account creation error: mailmng-outgoing failed

Symptoms

An attempt to create a domain or a mail account under a subscription fails with the following errors:

Error: mailmng-outgoing failed: ERROR:outgoing:domains.subscription_id may not be NULL

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

or

The command /usr/local/psa/admin/bin/mail_auth_view does not show all the mail accounts.

Cause

Missing entries in psa.IpAddressesCollections table for the corresponding domain.

Resolution

1. Find the affected domain and missing ipCollectionId :

   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa -e"SELECT d.name,ds.ipCollectionId,ds.type,ipc.* FROM domains d JOIN DomainServices ds on d.id=ds.dom_id LEFT JOIN IpAddressesCollections ipc on (ds.ipCollectionId = ipc.ipCollectionId ) WHERE ds.type='mail' AND ipc.ipCollectionId is NULL"
   

2. Find it's related records:

   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa -e "SELECT * FROM DomainServices WHERE dom_id in (SELECT id FROM domains WHERE name='example.com') AND type='mail'"
   +-----+--------+------+--------+---------------+----------------+
   | id  | dom_id | type | status | parameters_id | ipCollectionId |
   +-----+--------+------+--------+---------------+----------------+
   | 287 |    227 | mail |      0 |           290 |           1061 |
   +-----+--------+------+--------+---------------+----------------+
   

3. Check if the ipCollectionId is present in the psa.IpAddressesCollections table:

   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa -e "SELECT * FROM IpAddressesCollections WHERE ipCollectionId=1061"
   

4. Check the ipAddressId of the domain:

   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin psa -e"SELECT ipAddressId FROM IpAddressesCollections WHERE ipCollectionId  in (SELECT ipCollectionId FROM DomainServices WHERE dom_id in (SELECT id FROM domains WHERE name='example.com') AND type='web')"
   +-------------+
   | ipAddressId |
   +-------------+
   |           2 |
   +-------------+
   

5. Insert the missing ipCollectionId to the psa.IpAddressesCollections table:

   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa -e "insert into IpAddressesCollections values (1061,2)"
   
   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa -e "SELECT * FROM IpAddressesCollections WHERE ipCollectionId=1061"
   +----------------+-------------+
   | ipCollectionId | ipAddressId |
   +----------------+-------------+
   |           1061 |           2 |
   +----------------+-------------+
   

6. Execute the mchk utility to fix mail configuration completely:

   # /usr/local/psa/admin/sbin/mchk --with-spam