Applicable to:
- Plesk for Linux
- Plesk for Windows
Symptoms
-
When adding a customer, subscription, mail account, mailing list, IP address, changing domain hosting type or installing a Let's Encrypt SSL certificate in Plesk, the operation fails with one of the following error messages:
PLESK_ERROR: Error: DB query failed: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'X' for key 'PRIMARY', query was: replace <table_name> set ...
PLESK_ERROR: Could not issue a Let's Encrypt SSL/TLS certificate for example.com.
Details
Failed to execute XML-RPC operation 'install/certificate': DB query failed:
"replace certificates set id='0', name='Lets Encrypt example.com', csr='-----BEGIN+CERTIFICATE+REQUEST---- -
When installing a custom instance of WordPress at Domains > example.com > WordPress > Install > Install (Custom), the operation loads continuously and no errors appear.
-
The
AUTO_INCREMENT
value for the table from the error message is equal or less than max PRIMARY KEY value of this table:# plesk db -Ne'SELECT CONCAT("Auto increment value: ", AUTO_INCREMENT) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = "psa" AND TABLE_NAME = "DomainServices";'
Auto increment value: 45
# plesk db -Ne'SELECT CONCAT("Primary key name: ", COLUMN_NAME) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = "psa" AND TABLE_NAME = "DomainServices";'
Primary key name: id
# plesk db -Ne'SELECT CONCAT("Max value: ", MAX(id)) FROM DomainServices'
Max value: 46
Cause
The AUTO_INCREMENT value for the table is lower than the real maximum PRIMARY KEY value.
This issue could be caused when InnoDB and XtraDB use an auto-increment counter stored in the memory. When the server restarts, the counter is re-initialized. This issue is mentioned for a certain MariaDB version.
Resolution
For Plesk Obsidian 18.0.28 Update 3
Error messages related to an incorrect AUTO_INCREMENT value in the Plesk database now show a suggestion to fix the error using the Repair utility (either in the Plesk interface or in the CLI).
-
Repairing the database via Plesk
-
Go to go to Tools & Settings > Diagnose & Repair.
-
Click Repair next to Plesk Database.
-
Repairing the database via a command-line interface
-
Connect to the Plesk server via SSH (Linux) / RDP (Windows Server).
-
Start the repair task:
# plesk repair db
-
For older Plesk versions
Download and use the script to automatically update the improper AUTO_INCREMENTS value:
-
Connect to the Plesk server via SSH.
-
Create a backup of the Plesk database:
# plesk db dump > psa_dump.sql
-
Download the script:
-
Unpack the script:
# tar xf 115003292185_auto_increment_fix.php.tar.gz
-
Run the script to analyze the tables:
# plesk php 115003292185_auto_increment_fix.php -v -y
-
Rerun the script until you see:
MYSQL_LIN: ...
No inconsistencies were detected.
-
Connect to the Plesk server via RDP.
-
Download the script to your Windows Server.
-
Extract the script from the zip file on C:\ disk.
-
Create a backup of the Plesk database:
C:\> plesk db dump > C:\psa_dump.sql
-
Switch to the folder where extracted script on step 2 is located.
C:\> cd C:\
-
Run the script:
C:\> plesk php 115003292185_auto_increment_fix.php -v -y
-
Rerun the script until you see:
MYSQL_WIN: ...
No inconsistencies were detected.
Comments
18 comments
Can a check for this be added to plesk repair db?
We've had this issue on dns_zone table.
Thanks!
As a sidenote, we suspect that an upgrade we did to MariaDB 10.2 (from 5.5 to 10.1 and then to 10.2) might be the cause of this, but this upgrade was done over a week ago.
We asked our customer if they had added any other domains since the upgrade, they did not.
@Stéphan Schamp
Hi! Our developers are aware of such issue and already implementing such functionality in plesk db utility. You may use the ID PPM-2625 to track when it in Plesk Change log here: https://docs.plesk.com/release-notes/onyx/whats-new/
The root cause of AUTO_INCREMENT corruption is still inclear, however, it seems that such upgrade may cause it. The development team is checking it.
We, too, have encountered this issue after upgrading to MariaDB 10.2 from 10.0. In our case it was the certificates table.
Also, your example for the certificates table, step 4 is missing a close quote. It should be this:
# plesk db "ALTER TABLE certificates AUTO_INCREMENT = 82;"
@Websavers fixed, thank you for noticing!
This time i made the migration, i had more than one errors.
I update the AI of tables:
to check (replace DomainServices table_name below to check the other tables too)
if the values are the same, then the table is ok
else set AI the same as MAX(id)+1 (the AI is wrong and should be updated with MAX(id)+1)
to detect
The warning has the error with duplicate id=20 (eg.) so this time the AI is set (possibly false) to 21 for that table and for the next inserted row.
We do not know the table (it does not provided in the error message)
So, executing
we might have the candidates tables to check for non-aligned values.
Please follow the above solution with caution and i am not responsible for any damage it may cause. This worked for me.
PS. I am also curious about why the plesk repair db do not fix errors like these. I think that it would be some conflict issues.
Hello @GIannis,
Thank you for sharing your experience, it may be useful for other Pleskians.
> I am also curious about why the plesk repair db do not fix errors like these. I think that it would be some conflict issues.
Thank you for the idea, I have created a feature request at Plesk User Voice, feel free to vote for this feature as popular suggestions are likely to be implemented.
Hello, same problem here after updating to MariaDB 10.3.X
After having fixed the dns_zone table (as proposed by Giannis) I was able to add new domains in Plesk.
But: when trying to add domains which were registered (in the past) and removed (in the past), following error message is returned:
Error: mailmng-outgoing failed: ERROR:outgoing:column name is not unique
Any idea?
Hello @Sebastian,
Try this article https://support.plesk.com/hc/en-us/articles/213371469-Domain-or-mail-account-creation-error-mailmng-outgoing-failed but, please, make sure that you have created a database backup before applying a solution.
Same error here. domains table.
plesk repair db does not repair the issue.
Provided script works, but leaves errors for sys_user table
Hello @TomBob,
The script has to resolve the issue for the "sys_user" table as well
What is the error message for the "sys_user" table and which actions lead to its appearing?
This error also occurs when resetting a DNS zone (because it shows as empty within the domain > DNS settings). Can you confirm? The solution provided works perfectly.
Hello @Mitchell van Bijleveld,
The error may be related to any table in Plesk database and occur in using different Plesk functionality, including DNS settings.
The solution is universal and fixed the inconsistency in all tables automatically.
MariaDB fixed a bug regarding this problem: https://jira.mariadb.org/browse/MDE-6076
But it seems it is not completely fixed https://jira.mariadb.org/browse/MDEV-20892
Hello,
Thank you for the information.
The mentioned MariaDB jira items are referenced to different versions and confirm that the issue is specific for MariaDB server 10.2.3 and lower.
Hello,
I'm the guy that made https://jira.mariadb.org/browse/MDEV-20892
I updated the issue yesterday since i'm still busy figuring out what this problem causes. In the meantime we've tried multiple mariadb versions: 10.1.38, 10.2.27, 10.2.28 and 10.3.20. This problem happended to me in all these versions. Can someone here confirm that this problem is also happening in their environment? For me this problem caused major data corruption and I'm looking for a way to fix it for almost 2 months now. For now i have contained this problem with a script that fixes the auto_increments based on the max id of the primary key but obviously thats not a long term solution.
I think this problem is caused by wrong evicted cache from innodb where the auto_increment gets flushed to disk. Im currently testing different situations with different innodb_autoinc_lock_mode's and the combination with IODKU statements.
If someone has any information about a possible fix or bug in mariadb. Please let me know.
Just encountered this error on a Windows 2012 R2 running Plesk Obsidian Version 18.0.23 with MariaDB 10.3.17 when trying to clone a service plan. In our case the tables affected were clients, dns_zone, domains, log_rotation and mail_resp. Plesk would err on log_rotation.
I just had this same problem on my CentOS server after adding a new website
Please sign in to leave a comment.