Mail option is missing and/or mail service can not be enabled for domain in Plesk: ERROR:outgoing:column name is not unique

Symptoms

• Mail tab is missing for domain and enabling via CLI fails with the following error:

  # plesk bin subscription -u example.com -mail_service true
  ERR [util_exec] proc_close() failed ['/usr/local/psa/admin/bin/mailmng-outgoing' '--add-subscription' '--main-domain-name=example.com' '--out-limit=100'] with exit code [1]
  mailmng-outgoing failed: **ERROR:outgoing:column name is not unique **

• It is not possible to create a new domain with the same error:

  mailmng-outgoing failed: ERROR:__main__:UNIQUE constraint failed: domains.name

Cause

Plesk database inconsistency. The subscription/domain is present in the outgoing mail control database at /var/lib/plesk/mail/outgoing/data.db

# sqlite3 /var/lib/plesk/mail/outgoing/data.db

# sqlite> SELECT id,name,subscription_id,out_limit FROM domains WHERE name='example.com';
13|example.com|11|500'

Resolution

1. Connect to the server via SSH.

2. Create a backup of /var/lib/plesk/mail/outgoing/data.db database:

   # cp /var/lib/plesk/mail/outgoing/data.db{,_bk}

3. Remove the subscription from the outgoing mail control database:

   # /usr/local/psa/admin/sbin/mailmng-outgoing --remove-subscription --main-domain-name=example.com

   Note: In case it's a single domain use the following command instead

   # /usr/local/psa/admin/sbin/mailmng-outgoing --remove-domain --domain-name=example.com

4. Enable mail service for the subscription/domain:

   # plesk bin subscription -u example.com -mail_service true