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:

  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 johndoe@example.com
  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
  johndoe@example.com
  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:

   select ipCollectionId from DomainServices where type ='mail';
   +----------------+
   | ipCollectionId |
   +----------------+
   | 1 |
   | 3 |
   | 10 |
   | 12 |
   | 14 |
   | 17 |
   +----------------+

5. Check the ID from IP_Addresses table:

   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:

   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

   If you see warning during repairing mail:

   We have found the database inconsistency. We strongly recommend that you run "plesk repair db" after the current command is finished or stopped. Once "plesk repair db" fixes the inconsistency, run "plesk repair mail" once again ............................................. [WARNING]

   It means that system found some inconsistency in Plesk database related to mail functionality, it requires to fix plesk database with command below and repair mail again:

   # plesk repair db