Unable to send email or create mailbox: Error: mailmng-outgoing failed: ERROR:outgoing:mails.domain_id may not be NULL

Symptoms

• Unable to create a mail account for example.com in Plesk. The following error is shown:

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

• Unable to send emails from example.com and the following is found on /var/log/maillog file:

  Sep 11 18:30:01 hostname py_limit_out[23789]: ERROR Rejecting message: system user uid='UID' is not allowed to send mail
  Sep 11 18:30:01 hostname plesk sendmail[23788]: handlers_stderr: DATA REPLY:554:5.7.0 Your message could not be sent. The user john is not allowed to send email.
  5.7.0 The message could not be sent. You are not allowed to use sendmail utility

• An attempt to repair mail settings for an existing mailbox fails:

  # plesk repair mail [email protected]
  Repairing the mail server configuration
  Reconfiguring the mailboxes ………………………………. [2018-09-11 16:11:34] ERR [util_exec] proc_close() failed [‘/usr/local/psa/admin/bin/mailname_repair’] with exit code [1]
  [FAILED]
  mailname_repair failed: Restoring mailbox settings for
  [email protected]
  DEBUG setMailboxQuota(…, 5368709120)
  mails.domain_id may not be NULL
  ERROR:mailname_repair:This exception happened at:
  IntegrityError: mails.domain_id may not be NULL

• The record for ipAddressId in IpAddressesCollections table is different from the ipAddressId in IP_Addresses table;

  # plesk db “SELECT d.name,ipc.ipAddressId 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'”
  +————-+————-+
  | name | ipAddressId |
  +————-+————-+
  | example.com | 0 |
  +————-+————-+

  # plesk db “SELECT id from IP_Addresses”
  +—-+
  | id |
  +—-+
  | 1 |
  +—-+

Cause

Plesk database inconsistency: wrong entries in psa.IpAddressesCollections table for the corresponding webspace.

Resolution

1. Connect to the server via SSH;
2. Create Plesk database backup;
3. Access Plesk database;
4. Get the ipCollectionId from the DomainServices that has mail service enabled:

   MYSQL_LIN: select ipCollectionId from DomainServices where type =’mail’;
   +—————-+
   | ipCollectionId |
   +—————-+
   | 1 |
   | 3 |
   | 10 |
   | 12 |
   | 14 |
   | 17 |
   +—————-+

5. Check the ID from IP_Addresses table:

   MYSQL_LIN: SELECT id from IP_Addresses;
   +—-+
   | id |
   +—-+
   | 1 |
   +—-+

6. Change the ipAddressId value to the ID shown in the previous step from the ipCollectionId ID’s (step #4) on IpAddressesCollections table:

   MYSQL_LIN: UPDATE IpAddressesCollections SET ipAddressId=’1′ WHERE ipCollectionId in (1,3,10,12,14,17);

7. Exit the database and run the following utility:

   # plesk repair mail -y