Resolved Cannot create subscription - fails on "Configuring mail"

[octet]

Server operating system version

CentOS 7

Plesk version and microupdate number

Plesk Obsidian Web Pro Edition 18.0.48

Hi,

Unable to activate mail on a newly created subscription / domain, it fails / times out on Configuring email.

Panel log below:

/var/log/plesk/panel.log


[2022-12-06 11:47:55.509] 10511:638f2be604667 ERR [panel] DB query failed:
"REPLACE INTO `Parameters` (`parameter`, `value`) VALUES (?, ?)"

Error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '8-nonexist_mail' for key 'PRIMARY', query was: REPLACE INTO `Parameters` (`parameter`, `value`) VALUES (?, ?)
[2022-12-06 11:47:57.669] 10511:638f2be604667 ERR [panel] Task failed: id=4399, pid=10511, type=subscription-create, error=DB query failed: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '8-nonexist_mail' for key 'PRIMARY', query was: REPLACE INTO `Parameters` (`parameter`, `value`) VALUES (?, ?)
[2022-12-06 11:47:57.670] 10511:638f2be604667 ERR [panel] DB query failed: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '8-nonexist_mail' for key 'PRIMARY', query was: REPLACE INTO `Parameters` (`parameter`, `value`) VALUES (?, ?)
[2022-12-06 11:47:57.670] 10511:638f2be604667 ERR [panel] There is already an active transaction:
0: /usr/local/psa/admin/plib/vendor/plesk/zf1/library/Zend/Db/Adapter/Pdo/Abstract.php:307
PDO->beginTransaction()
1: /usr/local/psa/admin/plib/vendor/plesk/zf1/library/Zend/Db/Adapter/Pdo/Abstract.php:307
Zend_Db_Adapter_Pdo_Abstract->_beginTransaction()
2: /usr/local/psa/admin/plib/Db/Adapter/Pdo/Mysql.php:26
Db_Adapter_Pdo_Mysql->_beginTransaction()
3: /usr/local/psa/admin/plib/vendor/plesk/zf1/library/Zend/Db/Adapter/Abstract.php:496
Zend_Db_Adapter_Abstract->beginTransaction()
4: /usr/local/psa/admin/plib/Db/Table/Row/LongTask.php:86
Db_Table_Row_LongTask->save()
5: /usr/local/psa/admin/plib/Task/Control/Abstract.php:289
Task_Control_Abstract->onError(object of type PleskMultipleException)
6: /usr/local/psa/admin/plib/Db/Table/Broker/LongTasks.php:185
Db_Table_Broker_LongTasks->_syncStart(object of type Db_Table_Row_LongTask)
7: /usr/local/psa/admin/plib/Db/Table/Broker/LongTasks.php:113
Db_Table_Broker_LongTasks->runTaskWithinExecutor(object of type Db_Table_Row_LongTask)
8: /usr/local/psa/admin/plib/Task/Async/Executor.php:44
Task_Async_Executor->execute()
9: /usr/local/psa/admin/plib/scripts/task-async-executor.php:6
[2022-12-06 11:47:57.670] 10511:638f2be604667 ERR [panel] There is already an active transaction

Screenshots attached.





Tried this, doesn't work:

https://support.plesk.com/hc/en-us/articles/4404401506706-Recreating-a-subscription-or-enabling-the-mail-service-fails-with-mailmng-outgoing-failed-ERROR-main-column-name-is-not-unique

[root@ufo ~]# plesk repair mail
Repairing the mail server configuration
Reconfigure all domains and mailboxes? [Y/n] y
Reconfiguring all domains and mailboxes ......................... [OK]

Error messages: 0; Warnings: 0; Errors resolved: 0

Any ideas?

 

[Peter Debik]

What happens if you remove the newly created domain an re-add it afterwards?
Here is a thread with a similar issue: Issue - Mail service creation for add-on domains fails on two different servers

 

[octet]

Same error. I even added it with a new owner, new IP address etc. but still getting the same error.

 

[Peter Debik]

In the case referred above the cause was a database upgrade that was done before. However, once a "plesk repair db -y" was run or the domain was re-added, the issue was resolved.

Did you upgrade your database server recently?

 

[Kaspar]

@octet out of curiosity, did you also try to run the command below as suggested in the support article.

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

I am not entirely sure but the Integrity constraint violation: 1062 Duplicate entry indicates a conflict with an duplicate entry for the subscription/domain. Removing the subscription from the mail service with the above command before running Plesk repair mail -y to repair the mail configuration might yield better results.

 

[octet]

Mariadb was updated recently.

[root@ufo ~]# mysql -V


mysql Ver 15.1 Distrib 10.5.18-MariaDB, for Linux (x86_64) using readline 5.1

plesk repair log:

[root@ufo ~]# plesk repair db -y

Checking the Plesk database using the native database server tools .. [OK]

Checking the structure of the Plesk database

Extra table: cards ................................................ [WARNING]
Creating a database dump ........................................ [OK]
A dump of the database was created before an attempt to repair the database: /var/lib/psa/dumps/mysql.preresolve.20221206-160528.dump.gz
Deleting table 'cards' .......................................... [FIXED]

Extra table: filesharingusers ..................................... [WARNING]
Deleting table 'filesharingusers' ............................... [FIXED]

Column passed_fixed is absent in the table
domainoutgoingmessagesstats ....................................... [ERROR]
Repairing table 'domainoutgoingmessagesstats' ................... [FAILED]
- DB query failed: SQLSTATE[42S01]: Base table or view already
exists: 1050 Table 'DomainOutgoingMessagesStats' already
exists, query was: CREATE TABLE
`DomainOutgoingMessagesStats`(`domainId` int unsigned NOT
NULL,`collectDate` datetime NOT NULL,`limitValue` bigint NOT
NULL DEFAULT '-1',`passed` bigint unsigned NOT NULL DEFAULT
'0',`rejected` bigint unsigned NOT NULL DEFAULT
'0',`passed_fixed` bigint unsigned NOT NULL DEFAULT
'0',`rejected_verified` bigint unsigned NOT NULL DEFAULT
'0',PRIMARY KEY (`domainId`,`collectDate`)) ENGINE=InnoDB
DEFAULT CHARSET=utf8

Column rejected_verified is absent in the table
domainoutgoingmessagesstats ....................................... [ERROR]
Repairing table 'domainoutgoingmessagesstats' ................... [FAILED]
- DB query failed: SQLSTATE[42S02]: Base table or view not found:
1051 Unknown table 'psa.domainoutgoingmessagesstats', query
was: DROP TABLE `domainoutgoingmessagesstats`

Column passed_fixed is absent in the table mailoutgoingmessagesstats[ERROR]
Repairing table 'mailoutgoingmessagesstats' ..................... [FAILED]
- DB query failed: SQLSTATE[42S01]: Base table or view already
exists: 1050 Table 'MailOutgoingMessagesStats' already exists,
query was: CREATE TABLE `MailOutgoingMessagesStats`(`mailId`
int unsigned NOT NULL,`collectDate` datetime NOT
NULL,`limitValue` bigint NOT NULL DEFAULT '-1',`passed` bigint
unsigned NOT NULL DEFAULT '0',`rejected` bigint unsigned NOT
NULL DEFAULT '0',`passed_fixed` bigint unsigned NOT NULL
DEFAULT '0',`rejected_verified` bigint unsigned NOT NULL
DEFAULT '0',PRIMARY KEY (`mailId`,`collectDate`)) ENGINE=InnoDB
DEFAULT CHARSET=utf8

Column rejected_verified is absent in the table
mailoutgoingmessagesstats ......................................... [ERROR]
Repairing table 'mailoutgoingmessagesstats' ..................... [FAILED]
- DB query failed: SQLSTATE[42S02]: Base table or view not found:
1051 Unknown table 'psa.mailoutgoingmessagesstats', query was:
DROP TABLE `mailoutgoingmessagesstats`

Column messageKey is absent in the table panelnotificationmessages [ERROR]
Repairing table 'panelnotificationmessages' ..................... [FAILED]
- DB query failed: SQLSTATE[42S01]: Base table or view already
exists: 1050 Table 'PanelNotificationMessages' already exists,
query was: CREATE TABLE `PanelNotificationMessages`(`id` int
unsigned NOT NULL AUTO_INCREMENT,`clientId` int
unsigned,`adminAliasId` int unsigned,`isRead` int NOT NULL
DEFAULT '0',`isFavorite` int NOT NULL DEFAULT '0',`subject`
varchar(255) NOT NULL,`message` text,`actionText` varchar(255)
NOT NULL,`actionLink` varchar(255) NOT NULL,`category`
varchar(255) NOT NULL,`area` varchar(255) NOT NULL,`context`
varchar(255) NOT NULL,`contextIconUrl` varchar(255) NOT
NULL,`createdAt` timestamp NOT NULL DEFAULT
CURRENT_TIMESTAMP,`isViewed` int NOT NULL DEFAULT '0',`code`
varchar(255) NOT NULL,`openInNewTab` int NOT NULL DEFAULT
'0',`messageKey` varchar(255),`isPromotion` int NOT NULL
DEFAULT '0',PRIMARY KEY (`id`),KEY `clientId` (`clientId`),KEY
`adminAliasId` (`adminAliasId`),KEY `isRead` (`isRead`),KEY
`isFavorite` (`isFavorite`),KEY `category` (`category`))
ENGINE=InnoDB DEFAULT CHARSET=utf8

Column isPromotion is absent in the table panelnotificationmessages [ERROR]
Repairing table 'panelnotificationmessages' ..................... [FAILED]
- DB query failed: SQLSTATE[42S02]: Base table or view not found:
1051 Unknown table 'psa.panelnotificationmessages', query was:
DROP TABLE `panelnotificationmessages`

Wrong attributes for the column 'rep_id' in the table 'repository': [ERROR]
- Extra attribute: autoincrement.
Repairing table 'repository' .................................... [FAILED]
- DB query failed: SQLSTATE[42S01]: Base table or view already
exists: 1050 Table 'Repository' already exists, query was:
CREATE TABLE `Repository`(`rep_id` int unsigned NOT
NULL,`component_id` int unsigned NOT NULL DEFAULT '0',PRIMARY
KEY (`rep_id`,`component_id`)) ENGINE=InnoDB DEFAULT
CHARSET=utf8

Column passed_fixed is absent in the table
subscriptionoutgoingmessagesstats ................................. [ERROR]
Repairing table 'subscriptionoutgoingmessagesstats' ............. [FAILED]
- DB query failed: SQLSTATE[42S01]: Base table or view already
exists: 1050 Table 'SubscriptionOutgoingMessagesStats' already
exists, query was: CREATE TABLE
`SubscriptionOutgoingMessagesStats`(`subscriptionId` int
unsigned NOT NULL,`collectDate` datetime NOT NULL,`limitValue`
bigint NOT NULL DEFAULT '-1',`passed` bigint unsigned NOT NULL
DEFAULT '0',`rejected` bigint unsigned NOT NULL DEFAULT
'0',`passed_fixed` bigint unsigned NOT NULL DEFAULT
'0',`rejected_verified` bigint unsigned NOT NULL DEFAULT
'0',PRIMARY KEY (`subscriptionId`,`collectDate`)) ENGINE=InnoDB
DEFAULT CHARSET=utf8

Column rejected_verified is absent in the table
subscriptionoutgoingmessagesstats ................................. [ERROR]
Repairing table 'subscriptionoutgoingmessagesstats' ............. [FAILED]
- DB query failed: SQLSTATE[42S02]: Base table or view not found:
1051 Unknown table 'psa.subscriptionoutgoingmessagesstats',
query was: DROP TABLE `subscriptionoutgoingmessagesstats`

More in the next message.

 

[octet]

Checking the consistency of the Plesk database

Inconsistency in the table 'data_bases' for the column dom_id: No
rows in the table 'domains' with id = 80 .......................... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'db_users' for the column db_id: No rows
in the table 'data_bases' with id = 75 ............................ [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Fixing broken references ........................................ [FIXED]

Inconsistency in the table 'db_users' for the column dom_id: No rows
in the table 'domains' with id = 80 ............................... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Wrong AUTO_INCREMENT values in the following tables: Parameters: 9
≠ 48, Parameters: 9 ≠ 48, Parameters: 9 ≠ 48, Parameters: 9
≠ 48 .............................................................. [ERROR]
Updating AUTO_INCREMENT values .................................. [FIXED]

Inconsistency in the table 'apsResources' for the column registryId:
There is no object in the 'apsc' database in the table
'aps_registry_object' with UID =
f46c9d04-a20f-4449-aa5b-ac008243b1b9 .............................. [ERROR]
Removing rows ................................................... [FIXED]

Inconsistency in the table 'apsResourcesParameters' for the column
apsResourceId: No rows in the table 'apsResources' with id = 3 .... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 2 ........................ [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 4 ........................ [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 5 ........................ [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 6 ........................ [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 7 ........................ [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 8 ........................ [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 17 ....................... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 18 ....................... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 19 ....................... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 24 ....................... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 28 ....................... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 29 ....................... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

more in the next message.

 

[octet]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 30 ....................... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 79 ....................... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 87 ....................... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 88 ....................... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 90 ....................... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 91 ....................... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 93 ....................... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 94 ....................... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 95 ....................... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 96 ....................... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 127 ...................... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Inconsistency in the table 'Configurations' for the column objectId:
No rows in the table 'domains' with id = 128 ...................... [WARNING]
To see more details, run the command in the verbose mode: plesk repair db -verbose
Removing unnecessary rows ....................................... [FIXED]

Error messages: 11; Warnings: 30; Errors resolved: 32


exit status 1

Second run shows all fine:

[root@ufo ~]# plesk repair db -y
Checking the Plesk database using the native database server tools .. [OK]
Checking the structure of the Plesk database ........................ [OK]
Checking the consistency of the Plesk database ...................... [OK]
Error messages: 0; Warnings: 0; Errors resolved: 0
[root@ufo ~]#

Domain removed, added again, fails on Configuring mail:

Configuring mail
mailmng-outgoing failed: ERROR:__main__:column name is not unique

 

[octet]

@octet out of curiosity, did you also try to run the command below as suggested in the support article.

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

I am not entirely sure but the Integrity constraint violation: 1062 Duplicate entry indicates a conflict with an duplicate entry for the subscription/domain. Removing the subscription from the mail service with the above command before running Plesk repair mail -y to repair the mail configuration might yield better results.

That worked, thank you! Now resolved.

[root@ufo ~]# /usr/local/psa/admin/bin/mailmng-outgoing --remove-subscription --main-domain-name=domainXXX.com
[root@ufo ~]# plesk repair mail -y
Repairing the mail server configuration
Reconfiguring all domains and mailboxes ......................... [OK]
Error messages: 0; Warnings: 0; Errors resolved: 0

[root@ufo ~]#