Applicable to:
- Plesk for Linux
Symptoms
- One of the following symptoms may apply:
- Unable to create a domain or a mail account under a subscription:
PLESK_ERROR: 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
Error: mailmng-outgoing failed: ERROR:outgoing:NOT NULL constraint failed: mails.domain_id - Mail tab is missing for a domain and mail service cannot be enabled:
# 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 -
Unable to connect with a mail client such as Outlook, thunderbird. The authorization fails with the following error shown in
/var/log/maillog:CONFIG_TEXT: authpsa[260371]: No such user 'johndoe@example.com' in mail authorization database
- Mail restore fails:
# plesk repair mail example.com
....
Restoring outgoing limits for johndoe@example.com
NOT NULL constraint failed: mails.domain_id
# plesk repair mail example.com
....
Restoring outgoing limits for info@example.com
mails.domain_id may not be NULL
# plesk repair mail example.com
...
Fatal error: plesk::SystemError(You should specify at least one
non-empty ip adress: No such file or directory) - Passwords for mailboxes may be changed frequently for unknown reason
ipAddressId is missing in IpAddressesCollections table for one or several domains:# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa -Nse "SELECT d.name 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"
+----------------------------------+-------------+
| name | ipAddressId |
+----------------------------------+-------------+
| example.com | NULL |
| example2.com | NULL |
Cause
Product issue:
-
#PPPM-12375 "Setting “Outgoing mail mode” to “Send from the specified IP addresses” and then to “Send from domain IP addresses” or “Send from domain IP addresses and use domain names in SMTP greeting” in “Tools & Settings” > “Mail Server Settings” no longer results in multiple issues with mail in Plesk due to the Plesk database getting corrupted."
Fixed in:- Plesk Obsidian 27 October 2020 (Linux)
Resolution
Workaround
If update is not possible for some reason you may try the following
Apply the following workaround until the bug is fixed:
Note: if you don't have root-level SSH access to Plesk server, contact your hosting company to resolve the issue.
For Plesk Obsidian 18.0.29 and later:
- Log in to the server via SSH.
- Back up Plesk database
- Execute the following command to automatically repair missing entries:
# plesk repair db
For Plesk Obsidian 18.0.28 and earlier:
- Log in to the server via SSH.
- Back up Plesk database
- Generate the list of domains affected by the issue:
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa -Nse "SELECT d.name 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" > /root/affected_domains.txt
- Download, extract and assign the correct permissions to the script:
# wget https://plesk.zendesk.com/hc/article_attachments/360008434854/kb213371469.zip
# unzip kb213371469.zip
# chmod +x kb213371469.sh
- Execute the script to restore missing entries in
psa.IpAddressesCollectionstable:# ./kb213371469.sh /root/affected_domains.txt
- Check and repair inconsistencies with plesk repair utility if any:
# plesk repair db
- Fix mail configuration:
# plesk repair mail -y
Comments
8 comments
Hi Yulia,
The domains are all set to "No web hosting" in the Plesk panel.
After running the script, I receive the following errors (I have changed the domain names):
root@psk1:/mailpatch# ./kb213371469.sh /affected_domains.txt
========================================================
Domain: domain1.com ipCollectionId: 71 ipAddressId:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
psa.IpAddressesCollections table for domain1.com has been updated
========================================================
Domain: domain2.com ipCollectionId: 86 ipAddressId:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
psa.IpAddressesCollections table for domain2.com has been updated
========================================================
Domain: domain3.com ipCollectionId: 76 ipAddressId:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
psa.IpAddressesCollections table for domain3.com has been updated
========================================================
Domain: domain4.com ipCollectionId: 176 ipAddressId:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
psa.IpAddressesCollections table for domain4.com has been updated
psa.IpAddressesCollections table for all domains has been updated
I would appreciate any assistance.
@Dan Heitbohmer
The script supposes that domains have a web hosting enabled and IP for the web hosting is configured properly. As it updating 'mail' service by using IP address information configured for web hosting. That's why it could not help you. If all domains on your server are using the same IP address, you may explicitly set actual id of IP address from the psa database instead of "$ipaid"
in the string "insert into IpAddressesCollections values ($ipcid,$ipaid)"
To get list of all IP addresses and their id run the following in the terminal:
# plesk db "select * from IP_Addresses"
when i run
./kb213371469.sh /root/affected_domains.txt
this error has be shown:
-bash: ./kb213371469.sh: Permission denied
any suggestion?
Hi @amir b.
Try adding executable permissions to the script:
# chmod +x kb213371469.sh
Did not help me..
# ./kb213371469.sh
./kb213371469.sh: line 34: $FILE: ambiguous redirect
psa.IpAddressesCollections table for all domains has been updated
# plesk repair mail somedomain.com
Repairing the mail server configuration
Reconfiguring the settings of domains: somedomain.com ......... [OK]
Reconfiguring the mailboxes ..................................... [2020-11-23 12:30:26.494] ERR [util_exec] proc_close() failed ['/usr/local/psa/admin/bin/mail
name_repair'] with exit code [1]
[FAILED]
...
ERROR:__main__:This exception happened at:
Traceback (most recent call last):
File
"/usr/local/psa/admin/sbin/mailname_repair/mailname_repair.py",
line 152, in main
File
"/usr/local/psa/admin/sbin/mailname_repair/mailname_repair.py",
line 68, in restore_outgoing_limits
File
"/usr/local/psa/admin/sbin/mailname_repair/plesk_outgoing_mail_db.py",
line 320, in create_mailname
mail_name, domain_name).rowcount
File
"/usr/local/psa/admin/sbin/mailname_repair/plesk_sqlite3_db.py",
line 139, in run
return conn.execute(query, params)
IntegrityError: mails.domain_id may not be NULL
Error messages: 0; Warnings: 0; Errors resolved: 0
exit status 1
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin psa -Nse "SELECT d.name FROM domains d JOIN DomainServices ds on d.id=ds.dom_id LEFT JOIN I
pAddressesCollections ipc on (ds.ipCollectionId = ipc.ipCollectionId ) WHERE ds.type='mail' AND ipc.ipCollectionId is NULL"
[root@jedi turgut]#
Hello Turgut Kalfaoglu
When you're executing the script, you have to specify the file with domains list, generated at step 3:
./kb213371469.sh /root/affected_domains.txt
As the bug is already fixed in Plesk Obsidian, the easiest way to resolve the issue is to update/upgrade Plesk to the most recent Obsidian and execute "plesk repair db".
If the issue will remain even after this, contact Plesk Support.
Many thanks -- for me that file in step 3 was empty. I will bring this issue to the talk forum.
Please sign in to leave a comment.